November 6, 2019 at 12:48 pm
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
November 6, 2019 at 10:05 pm
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.
November 7, 2019 at 12:14 pm
many records returned may be one militon rows from cte to temp table
November 7, 2019 at 10:12 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply