Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    6

    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!

    I pasted the whole stored procedure here:


    drop PROCEDURE spUpdateASW
    go

    create PROCEDURE spUpdateASW
    AS

    DECLARE @DataBatchID int
    DECLARE @DataHeaderID int
    DECLARE @ASWTableID int
    DECLARE @ASWTableName varchar(25)
    DECLARE @ASWFieldName varchar(25)
    DECLARE @AllowASWUpdate tinyint
    DECLARE @IsPrimaryKey tinyint
    DECLARE @DataTypeIsNumeric tinyint
    DECLARE @Data varchar(100)

    DECLARE @SQL_Where as varchar(400)
    DECLARE @SQL_Insert as varchar(1000)
    DECLARE @SQL_InsertValues as varchar(400)
    DECLARE @SQL_Update as varchar(1000)
    DECLARE @updateCounter int
    DECLARE @whereCounter int
    DECLARE @SQL_CheckIfAlreadyExist as varchar(1000)

    DECLARE @ErrorMessage varchar(500)

    DECLARE @RuleWhen as varchar(50)
    DECLARE @RuleWhenToExec as varchar(500)
    DECLARE @tempStr as varchar(700)

    DECLARE @server varchar(50)
    DECLARE @shortServer varchar(50)
    SET @server = 'GIBSON_A3MFGF_T1.S44E5797.A3MFGFT1'
    SET @shortServer = 'GIBSON_A3MFGF_T1'
    DECLARE @SQL varchar(5000)

    select getdate(), 'testposition 1'


    CREATE Table #tmptblUpdateASW(
    ASWRowAlreadyExists int,
    RuleWhenIsValid int
    )
    INSERT INTO #tmptblUpdateASW(ASWRowAlreadyExists, RuleWhenIsValid) Values(-1, -1)

    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

    FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    Update tblDataBatch set ASWUpdateStarted = 1 where DataBatchID = @DataBatchID

    DECLARE Header_Cursor CURSOR LOCAL FOR
    SELECT DataHeaderID
    from tblDataHeader
    inner join tblAgileFieldType on tblDataHeader.fkAgileFieldTypeID = tblAgileFieldType.AgileFieldTypeID
    where fkDataBatchID = @DataBatchID and isSentToASW = 0 order by tblAgileFieldType.InsertOrder
    OPEN Header_Cursor
    FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE ASWTable_Cursor CURSOR LOCAL FOR
    SELECT ASWTableID, ASWTableName, RuleWhen
    from tblASWTable
    inner join tblASWField on tblASWTable.ASWTableID = tblASWField.fkASWTableID
    inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
    where fkDataHeaderID = @DataHeaderID
    group by ASWTableID, ASWTableName, RuleWhen, InsertOrder
    order by InsertOrder
    OPEN ASWTable_Cursor
    FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
    WHILE @@FETCH_STATUS = 0
    BEGIN
    exec spBuildRuleString @DataHeaderID, @RuleWhen, @RuleWhenToExec output, 0

    SET @tempStr = 'IF ' + @RuleWhenToExec + ' UPDATE #tmptblUpdateASW SET RuleWhenIsValid=1 ELSE UPDATE #tmptblUpdateASW SET RuleWhenIsValid=0'
    EXEC (@tempStr)
    IF (SELECT RuleWhenIsValid FROM #tmptblUpdateASW) = 1
    BEGIN

    set @ErrorMessage = null
    exec spASWDataCheck_hardCoded @DataHeaderID, @ErrorMessage output

    SET @SQL_Insert = 'INSERT INTO ' + @server + '.' + @ASWTableName + '('
    SET @SQL_InsertValues = 'VALUES('
    SET @SQL_Update = 'UPDATE ' + @server + '.' + @ASWTableName + ' set '
    SET @updateCounter = 0
    SET @SQL_Where = ' WHERE '
    SET @whereCounter = 0

    DECLARE ASWField_Cursor CURSOR LOCAL FOR
    SELECT ASWFieldName, AllowASWUpdate, IsPrimaryKey, DataTypeIsNumeric, Data
    from tblASWField
    inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
    where fkASWTableID = @ASWTableID and fkDataHeaderID = @DataHeaderID
    OPEN ASWField_Cursor
    FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
    select getdate(), 'testposition 2'
    WHILE @@FETCH_STATUS = 0
    BEGIN
    select getdate(), @ASWFieldName, 'testposition 3'
    set @Data = replace(@Data, char(39), char(39) + char(39))
    if @DataTypeIsNumeric = 0
    set @Data = char(39) + @Data + char(39)

    set @SQL_Insert = @SQL_Insert + @ASWFieldName + ', '
    set @SQL_InsertValues = @SQL_InsertValues + @Data + ', '
    IF @AllowASWUpdate = 1
    BEGIN
    set @SQL_Update = @SQL_Update + @ASWFieldName + ' = ' + @Data + ', '
    set @updateCounter = @updateCounter + 1
    END
    IF @IsPrimaryKey = 1
    BEGIN
    set @SQL_Where = @SQL_Where + @ASWFieldName + ' = ' + @Data + ' and '
    SET @whereCounter = @whereCounter + 1
    END

    FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
    END
    select getdate(), 'testposition 4'
    CLOSE ASWField_Cursor
    DEALLOCATE ASWField_Cursor

    SET @SQL_Where = LEFT(@SQL_Where, LEN(@SQL_Where) - 4)
    SET @SQL_Insert = LEFT(@SQL_Insert, LEN(@SQL_Insert) - 1) + ') ' + LEFT(@SQL_InsertValues, LEN(@SQL_InsertValues) - 1) + ')'
    SET @SQL_Update = LEFT(@SQL_Update, LEN(@SQL_Update) - 1) + @SQL_Where
    SET @SQL_CheckIfAlreadyExist = 'Update #tmptblUpdateASW set ASWRowAlreadyExists = ' +
    '(SELECT * from OPENQUERY(' + @shortServer + ','' SELECT count(*) FROM ' + @ASWTableName + ' ' + replace(@SQL_Where,char(39), char(39) + char(39)) + ' ''))'
    Exec(@SQL_CheckIfAlreadyExist)
    select getdate(), 'testposition 4'
    select getdate(), 'testposition 5'
    select getdate(), 'testposition 6'

    IF @whereCounter = 0
    begin
    insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
    values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Inga primary keys var valda för denna tabellen!')
    end
    ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) > 1
    begin
    insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
    values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Kombinationen av primary keys genererade följande where-sats: ' + @SQL_Where)
    end
    ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 1 and @updateCounter > 0
    begin
    EXEC(@SQL_Update)
    insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
    values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Update, @ErrorMessage)
    update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
    end
    ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 0
    begin
    EXEC(@SQL_Insert)
    insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
    values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Insert, @ErrorMessage)
    update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
    end

    END

    FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
    END
    CLOSE ASWTable_Cursor
    DEALLOCATE ASWTable_Cursor

    FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
    END
    CLOSE Header_Cursor
    DEALLOCATE Header_Cursor

    UPDATE tblDataBatch set DateToASW = getDate() where DataBatchID = @DataBatchID

    FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
    END
    CLOSE Batch_Cursor
    DEALLOCATE Batch_Cursor


    DROP Table #tmptblUpdateASW

    GO

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Can take help from PROFILER and monitor the activity.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Three cursors, and nested.

    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.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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>)

  5. #5
    Join Date
    Feb 2003
    Posts
    6
    Originally posted by Satya
    Can take help from PROFILER and monitor the activity.
    I came a lot closer to the problem by using sql profiler. I turned every event on. Now i can see what statement which stops the query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •