How to enhance this query for good performance ?

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2599

    I work on sql server 2012 and i need to enhance or make this query have good performance

    this stored procedure work success but i need to know

    when make drop to temp table and cte

    plus how to write it with best practice for performance

     

    create Proc ImporterQueue_RunModified
    As
    WITH CTE AS
    (
    Select Row_Number() Over (Order By GetDate())as rownumber, StoredProcedureName , ImporterQueue.CreateBy , ImporterQueueID,applicationid, dbo.ImporterTemplate.ImporterTemplateID, InputFilePath, OutputFilePath, StoredProcedureName [ImporterTemplate.StoredProcedureName],
    RN = ROW_NUMBER() OVER (PARTITION BY applicationid ORDER BY ImporterQueueID asc)
    From dbo.ImporterQueue
    Inner Join dbo.ImporterTemplate On dbo.ImporterQueue.ImporterTemplateID = dbo.ImporterTemplate.ImporterTemplateID
    Inner Join Privilages.Module On dbo.ImporterTemplate.ModuleID = Privilages.Module.ModuleID
    Where dbo.ImporterQueue.IsDeleted = 0 And dbo.ImporterQueue.OverAllStatusID = 1
    )
    SELECT rownumber , RN , ImporterQueueID,CreateBy,StoredProcedureName,InputFilePath,OutputFilePath
    into #results FROM CTE
    WHERE RN = 1;
    If (Select OverAllStatusID From dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID) <> 1 -- Pending
    Return;
    --loop through temp table
    DECLARE @totalRecords INT
    DECLARE @I INT


    --Declare @UserID Int = (Select CreateBy From dbo.ImporterQueue Where ImporterQueueID = @ImporterQueueID)
    Declare @ImportingStartDate DateTime = GetDate(), @DurationInSeconds Int

    Update dbo.ImporterQueue Set
    ImportingStartDate = @ImportingStartDate,
    OverAllStatusID = 2, -- In Progress
    StatusReason = Null,
    UpdateBy = #results.CreateBy,
    UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID


    --Begin Transaction Trans
    Begin Try
    SELECT @I = 1
    SELECT @totalRecords = COUNT(ImporterQueueID) FROM #results
    WHILE (@I <= @totalRecords)
    BEGIN
    declare @ProcedureName Nvarchar(200) = (SELECT StoredProcedureName FROM #results WHERE rownumber = @I),
    --@UserIDString Varchar(20) = Convert(Varchar(20), @UserID),
    @ImporterQueueIDString Varchar(20) = (SELECT StoredProcedureName FROM #results WHERE rownumber = @I),
    @InputFilePath Nvarchar(500) = (SELECT InputFilePath FROM #results WHERE rownumber = @I),
    @OutputFilePath Nvarchar(500) = (SELECT OutputFilePath FROM #results WHERE rownumber = @I)

    Declare @SQLvalue Nvarchar(1000) = 'EXECUTE ' + @ProcedureName + ' ' + @ImporterQueueIDString + ' , ' + '''' + @InputFilePath + '''' + ' , ' + '''' + @OutputFilePath + '''' + ''
    Exec(@SQLvalue)
    SELECT @I = @I + 1
    END

    --Commit Transaction Trans
    Update dbo.ImporterQueue Set
    DurationInSeconds = DATEDIFF(SECOND, @ImportingStartDate, GetDate()),
    OverAllStatusID = 3, -- Done
    StatusReason = Null,
    UpdateBy = #results.CreateBy,
    UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID
    End Try

    Begin Catch
    --RollBack Transaction Trans
    Update dbo.ImporterQueue Set
    DurationInSeconds = DATEDIFF(SECOND, @ImportingStartDate, GetDate()),
    OverAllStatusID = 4, -- Failed
    StatusReason = ERROR_MESSAGE(),
    UpdateBy = #results.CreateBy,
    UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID

    End Catch
  • Leo.Miller

    SSChampion

    Points: 12836

    I'm not sure this is the place to get your code rewritten for you.

    You would at least need to tell us where some of the bottlenecks are. The problem could be in the unknown Stored Procs you execute half way down.

    No execution plan, no idea how many rows are being returned in the CTE or into #results (one?)

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2599

    many records returned may be one militon rows from cte to temp table

  • x

    SSC-Insane

    Points: 23485

    looks like you are looping through the temp table and calling a stored procedure for each row.

    You could consider an index on the temp table on column "rownumber" because if you don't, it might result in a scan for each iteration, but honestly the iteration itself is never fast unless you're using memory optimized stuff, which will compile to much faster code but that has a boatload of restrictions in itself.

    PLUS, you might also be doing file imports for each row in the temp table from the looks of it, and well thats going to be a possible source of slow row by row processing too.

     

     

     

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply