how to enhance this query for good performance ?

[ahmedsa2018]

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

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

 

[SamBones]

Wow, it seems like you're trying to farm your job out to an online forum. Good luck with that.

If you'd like to learn how to do performance tuning yourself, look up "SQL Server Execution Plan". That will show you how the SQL engine actually decides how to get the results you're looking for. In that plan you'll see where the query is spending most of its time. Look at the spots it spends a lot of time. You can't tune it if you don't know where it's spending its time. A lot of times just adding an index on a specific column or two can really boost performance. I have seen queries that took hours end up taking seconds.

Also, doing a little data analysis can sometimes show you a better way to approach the data. Maybe structuring the queries differently can boost the performance. That's something "we" can't really decipher without you posting a schema and some data, which might get you fired.

So, fire up Google and learn about execution plans, indexes, and also "hints". You can alter the way the query optimizer handles your query with hints.

https://docs.microsoft.com/en-us/sq...n-actual-execution-plan?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql?view=sql-server-ver15

I really do recommend spending some time learning how to tune database queries yourself. That's a very valuable, and marketable skill to have.

Hope this helps.

 

[johnherman]

One way to increase performance is to have indexes (indices) on the columns on which you are doing joins. So, create indexes on these columns:
dbo.ImporterQueue.ImporterTemplateID
dbo.ImporterTemplate.ModuleID

You might also benefit by creating indexes on the temporary table before reading it and inner joining to the other tables.

I would set things up in a test environment with a representative set of data (number of rows) and test adding an index for each join. Some might not be worthwhile while others might provide strong benefits.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity