Testing: Execution Time Increases During While Loop

[MissyEd]

Hi folks

I'm curious why this is happening and haven't spotted anything similar on the boards yet. Every time I test my trigger the execution time bloats from 0ms to up to 2 seconds.

I've written and tuned the trigger, which is down to about 16ms when I update the table with a single row - (we only ever update / insert a single row at a time as thats how the data is coming in from a legacy system)

I've siphoned off 1000 records from the production server to simulate updates using real data. I've written a while loop to loop through the test dataset and execute a stored procedure. Part of this stored procedure will update / insert into the table.

So far, so good, when there is only a single row in the dataset. As soon as I attempt to loop through 1000, the execution time grows. Im not sure why, and wonder if anyone can suggest causes for this.

I'm only interested in and recording the time within the trigger which I've captured at the start and just before the end of the trigger then inserted into a table, therefore, my test while loop shouldnt affect the performance results theoretically.....

Any ideas welcome

Cheers

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter

 

[druer]

I wonder if locking/blocking becomes an issue while you are in the loop due to records being in the same data page or something. Just guessing. Are the 1000 records you've selected for your test all in order or are they randomly spread out through the entire table? Can you change the order so that they would be scattered randomly throughout to see if that affects things.

Just throwing a guess into the ring,
Dalton

 

[MissyEd]

Hmm, didnt think of that! Will try it out and see if it makes a difference. The records arent in the clustered index order, but since the most recent records get the most updates, maybe a ROWLOCK hint will help in this instance.

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter