Click here to Skip to main content
14,383,884 members
Rate this:
Please Sign up or sign in to vote.
See more:
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


What I have tried:

alter 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
Posted
Updated 6-Nov-19 2:22am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Use (or install and use) the SQL Server Management Studio. It has a feature to profile the query and you can see where it's spending its time.

Look for (look-out for!) "Table Scans" of large tables - that means it's looking at every single record. In such cases it may make sense to put one or more indices on those tables. Remember, however, that adding an index cost some performance when adding records to tables with indices.

That, alone, will go a long way to improving your performance.
   
Comments
ahmed_sa 6-Nov-19 7:42am
   
can you please tell me how to make empty temp table #result on query above
W Balboos 6-Nov-19 7:47am
   
I gave you a method to answer your question which you will also to be able to continue to use for improving your work into the future:   speeding up your queries.

Presently I have my own work to do and cannot delve into the logic of your specific query as you keep expanding the scope of your question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100