|
AceWare
Starting Member
Australia
2 Posts |
 Posted - 09/24/2012 : 20:30:25
|
Multiposted on social.technet.microsoft.com/Forums/en-US/sqlnetfx
The code below is very preliminary test code to see if we can use clr sql to update grain handler records.
It is a simple test at this stage to see if I can loop thru approx 5000 records updating an arbitary field for each record.
Currently the batch pricing process via a front end is taking 45 minutes.
It has a lot of fairly convoluted logic that does not suit being written in T-SQL.
I will use stored procedures with T-SQL at the beginning and end of the process where it suits.
Once in production, the batch process is intended to be run stand alone by a single user - if it experiences locking issues from other users of the system - I just intend to abort the process and issue a message to the user that other user must not be using the system.
The scale of the operation allows this to be feasible - ie I do not intend to cope with record locks beyond warning other users to get out of the system.
The problem I am experiencing is infinite time out on updates for the same specific set of records.
I am the only user of the test database being used.
The same set of records experiences a time out in the update each time.
(We do not experience any record lock problems when using the existing front end batch code.)
At the end of the reader loop I can specifically update the problem records - the timeout problem only occurs when attempting to update within the loop. You will see lines of code where I successfully update the problem records one by one.
I am guessing that I must be locking multiple records within a page block (don't really know what I am talking about here)
Here are the counter numbers and id fields of the problem records:
(this is what comes back from the SqlContext.Pipe.Send(strError); line of code, slightly tweaked up for readability)
Recd# Id
----- ------------
706 002-21078503
718 002-21259566
1133 003-00452040
1149 002-19605577
1263 00000433
2117 002-21193694
2133 002-21236494
2507 002-21190216
2540 002-21281006
3634 002-21203655
3913 002-21140890
and here is the code:
I admit I am being naughty using SQL login to sa - will fix soon.
Timeout is set to 1 just to get a quick turn around everytime I try a new idea - I have set it to 300 with no improvement.
I have tried using a separate connection for the ExecuteNonQuery command but this interestingly enough gave me more than twice as many problem records.
I have tried creating and disposing cmdUpdate every time around the reader loop - with no improvement.
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using Microsoft.CSharp;
using System.Data.SqlClient;
using System.Configuration;
public class TransformLoopProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TransLoop(DateTime dteProcDate, String strSQLServer, String strInitialCatalog)
{
int intRecordCount;
IDataRecord Record;
string strIDNumber;
string strConnection;
string strError = "";
strConnection = "Data Source=" + strSQLServer + ";Initial Catalog=" + strInitialCatalog + ";Persist Security Info=True;User ID=sa;Password=xxxx;MultipleActiveResultSets=True ";
SqlConnection connection = new SqlConnection(strConnection);
using (connection)
{
connection.Open();
SqlCommand CmdReadLoop = new SqlCommand("ace_ToBeTransformed", connection);
CmdReadLoop.CommandTimeout = 0;
SqlDataReader reader = CmdReadLoop.ExecuteReader();
SqlCommand cmdUpdate = new SqlCommand();
cmdUpdate.CommandTimeout = 1;
cmdUpdate.Connection = connection;
cmdUpdate.CommandText = "UPDATE tblAQImport SET Paddock = @Paddock WHERE IDNumber = @IDNumber;";
using (reader)
{
intRecordCount = 0;
while (reader.Read())
{
intRecordCount ++;
Record = (IDataRecord)reader;
strIDNumber = Convert.ToString(Record["IDNumber"]);
cmdUpdate.Parameters.AddWithValue("@Paddock", "test9");
cmdUpdate.Parameters.Add("@IDNumber", SqlDbType.VarChar);
cmdUpdate.Parameters["@IDNumber"].Value = strIDNumber;
try
{
cmdUpdate.ExecuteNonQuery();
}
catch (Exception e)
{
strError = strError + intRecordCount.ToString() + " " + strIDNumber + "\n";
}
cmdUpdate.Parameters.Clear();
}
}
reader.Close();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21078503'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21259566'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '003-00452040'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-19605577'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '00000433'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21193694'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21236494'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21190216'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21281006'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21203655'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.CommandText = @"update tblAQImport set Paddock = 'test9' where IDNumber = '002-21140890'";
cmdUpdate.ExecuteNonQuery();
cmdUpdate.Dispose();
}
connection.Close();
SqlContext.Pipe.Send(strError);
}
}
Any suggestions very gratefully received.
Tony Epton
rosie |
|
Record blocking when attempting to update approx 5
