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
Return;
DECLARE @totalRecords INT
DECLARE @I INT
Declare @ImportingStartDate DateTime = GetDate(), @DurationInSeconds Int
Update dbo.ImporterQueue Set
ImportingStartDate = @ImportingStartDate,
OverAllStatusID = 2,
StatusReason = Null,
UpdateBy = #results.CreateBy,
UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID
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),
@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
Update dbo.ImporterQueue Set
DurationInSeconds = DATEDIFF(SECOND, @ImportingStartDate, GetDate()),
OverAllStatusID = 3,
StatusReason = Null,
UpdateBy = #results.CreateBy,
UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID
End Try
Begin Catch
Update dbo.ImporterQueue Set
DurationInSeconds = DATEDIFF(SECOND, @ImportingStartDate, GetDate()),
OverAllStatusID = 4,
StatusReason = ERROR_MESSAGE(),
UpdateBy = #results.CreateBy,
UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID
End Catch