how to enhance this query for good performance ?

ahmed elbarbary

6y795Databases & DBA

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

ahmed elbarbary6y ago
thank you very much i have some questions about performance
1- using cursor or while loop in case above ?
2-are this block have good performance
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
3- are using variable have statment select return value have good performance
as
@InputFilePath Nvarchar(500) = (SELECT InputFilePath FROM #results WHERE rownumber = @I)

4- are whaile loop executed success when i use rownumber=@I or not
please help me in answer questions above if possible ?
Rupesh Kahane6y ago
1) You can add delete records from temp table before END of store prcedure.
CREATE Procedure ProcedureName(@Parameters)
AS
BEGIN
--- your code of CTE, Temp table, select / update statements

DELETE FROM #TempTable
END
2) Use #TempTable -Always try to create Temp tables using #TempTable

Example
Create table #MyInfoTempTable
(
MyId INT,
My_Name VARCHAR(50),
My_Address VARCHAR(50)
)
3) Use (NOLOCK) -For better result & add flags like Is_deleted, Is_Active etc
SELECT * FROM
FROM Empployee A WITH (NOLOCK)
JOIN Department D WITH (NOLOCK)
ON A.EMP_ID = D.DEPT_EMP_ID AND D.Is_Deleted = 0
4) Use Indexes - For the table so you get faster result while execute the query in procedures / functions
5) User transaction if required, like begin transaction - commit transaction
6) Use distinct - If necessary use the distinct keyword to avoid repeating records
7) Union - Union is also used if necessary
8) If Exists - Avoid use of many variables & compare it by using if else conditions, use If Exists(Your Condition)
9) Case - Use Case statement for multiple condition
10) View - if you are accessing same records from table multiple times then create a view & access from View directly.
Hope so it will help you.