If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: The Query just stops - at different places

I have created a stored procedure in SQL Server. I found it very slow, so i putted "select getDate(), 'testposition 1'" at different places, so I could see what part of the code that takes time.

The problem is: Depending on where I put the select statements, the execution of the stored procedure seems to just stop. And depending on where i put the select statements, it stops at different places.

This is how I do (example):
1. I re-create the stored procedure with some "select getDate()"-statements
2. I run the stored procedure 15:00:00
3. I cancel the stored procedure after 20 seconds and look at the resultsets. All getDate-functions show a time between 15:00:00 and 15:00:02
4. I run the stored procedure 15:01:00
5. I cancel the stored procedure after 5 seconds and look at the resultsets. The same amount of resultsets are showed, so I can make the conclusion that the execution stopped at the same place as last time. All getDate-functions show a time between 15:01:00 and 15:01:02 this time too.
6. I re-create the stored procedure with some new "select getDate()"-statements
7. Now the execution stops at an other position. Somtimes even between two "select getDate()"-statements!

DECLARE Batch_Cursor CURSOR LOCAL FOR
SELECT DataBatchID from tblDataBatch
where DateConverted is not null and ASWUpdateStarted = 0
and DataBatchID not IN(
select fkDataBatchID from tblDataHeader where DataHeaderID IN(
select fkDataHeaderID from tblASWData where ConversionErrorMessage is not null
)
)
OPEN Batch_Cursor

I assume you have tried putting the "select getDate()"-statements inside the while loop? What happens then? The time element in batches is not always reliable (or at least was not in sql 6.5), but I forget what the conditions were on that tidbit of information. I think they have since fixed it.

Whie it appears to have stopped go to another QA window, select sql_handle from sysprocesses where spid = <your_spid_from_connection_with_query>, and do select * from ::fn_get_sql(<selected_sql_handle_from_sysprocesse s>)