T-SQL (From C#) query taking progressively longer on 1 machine

[ADoozer]

Hi All.

I am having an issue with a T-SQL statement from a C# application on 1 machine. The more times the query executes the longer the time it takes to return. On my test machine the query takes less than 2 seconds (and can finish the batch in about 6 hours), but on the box in question the query takes longer and longer with every pass (currently up to 3mins 30 sec, average 105 sec per record, guestimated query time 27 days and rising).

The SQL is as follows

Pseudo
select [HeaderID] from [Table_Header] Where [myDate] < Some_Date

with each row in returned dataset

BEGIN TRANSACTION

INSERT [New_Table_Data]
SELECT * FROM [Table_Data] WHERE [HeaderID] = row.[HeaderID];

IF @@ERROR !=0 BEGIN ROLLBACK TRANSACTION RETURN END

DELETE FROM [Table_Data] WHERE [HeaderID] = row.[HeaderID];

IF @@ERROR !=0 BEGIN ROLLBACK TRANSACTION RETURN END

COMMIT TRANSACTION

mySQLCommand.CommanadText = <above SQL>
IAsyncResult res = mySQLCommand.BeginExecuteNonQuery()

wait until res.IsComplete

mySQLCommand.EndExecuteNonQuery(res)

wend

[Table_Header].[HeaderID] is a big int primarykey
There are currently 32000 [Table_Header].[HeaderID]'s in the batch
[New_Table_Data] has no constraints
Each [Table_Header] will have up to 210 associated records in [Table_Data]
Currently [Table_Data] has around 8 million rows

The server is MS SQL 2008 R2, the C# application is running on the same machine as the server is installed

Any thoughts grately appreciated.

If somethings hard to do, its not worth doing - Homer Simpson

Jack of all trades, king of none!

 

[jrbarnett]

When you connect locally is your connection information on the line connecting via localhost or the machine's name or IP address?
do the two separate connections have the same settings and use the same versions of the client side connection libraries?

You could possibly speed up (on both sides) by replacing
select *

with
select field1, field2, field3, field4

etc

John

 

[JoeAtWork]

with each row in returned dataset

So basically in your C# code you are looping and running those SQL statements in each iteration? That's very inefficient and is a lot unnecessary communication between your client and server.

You could probably do this with set SQL statements on the server side, or at least do the same work in a stored procedure which the C# code just would need to call once.

 

[ADoozer]

Thanks for the input guys.

John,

Will replacing * with all the fields be quicker (I need to return all the fields)

My laptop (the quick version) is running SQL server express (2008 R2) and the c# app is connecing by machine name
The other box is running SQL full and is also being connected to locally using the machine name

use the same versions of the client side connection libraries?

I think so, I'm not 100% up to speed on the C# one click package deployment but I think it installs the same (correct me if I am wrong) versions, that being said both versions of SQL server were installed from the same CD.

Joe,

Yes, I am returning all [HeaderID]'s older than a specific date and moving all associated records (from another table whose only link is the [HeaderID]) to a new table based on a range of dates

I totally agree with the inefficient comment, the reasoning with using the C# app is that a bunch of other stuff is going on before this point (in a c# app), and although it could probabaly be done as a SP, when I started working on the job I wasn't up to speed with running jobs/SPs etc etc. (call it ignorance on my part. :s)

As a side note, I have managed to speed up the code quite significantly by creating an index on the [HeaderID] field in the data table before starting the process (time reduced to less than 13 hours)

thanks aagain for the input, greatly appreciated.

If somethings hard to do, its not worth doing - Homer Simpson

Jack of all trades, king of none!