ahmed elbarbary

ahmed elbarbary

  • 1.4k
  • 431
  • 16.9k

how to enhance this query for good performance ?

Nov 7 2019 12:49 AM

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
  1. alter Proc ImporterQueue_RunModified  
  2. As  
  3. WITH CTE AS  
  4.     (  
  5.     Select Row_Number() Over (Order By GetDate())as rownumber, StoredProcedureName , ImporterQueue.CreateBy , ImporterQueueID,applicationid, dbo.ImporterTemplate.ImporterTemplateID, InputFilePath, OutputFilePath, StoredProcedureName [ImporterTemplate.StoredProcedureName],  
  6.                 RN = ROW_NUMBER() OVER (PARTITION BY applicationid ORDER BY ImporterQueueID asc)  
  7. From dbo.ImporterQueue  
  8. Inner Join dbo.ImporterTemplate On dbo.ImporterQueue.ImporterTemplateID = dbo.ImporterTemplate.ImporterTemplateID  
  9. Inner Join Privilages.Module On dbo.ImporterTemplate.ModuleID = Privilages.Module.ModuleID  
  10. Where dbo.ImporterQueue.IsDeleted = 0 And dbo.ImporterQueue.OverAllStatusID = 1   
  11.     )  
  12.       SELECT rownumber , RN , ImporterQueueID,CreateBy,StoredProcedureName,InputFilePath,OutputFilePath  
  13.  into #results   FROM    CTE  
  14.     WHERE   RN = 1;  
  15. If (Select OverAllStatusID From dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID) <> 1 -- Pending  
  16. Return;  
  17. --loop through temp table  
  18. DECLARE @totalRecords INT  
  19. DECLARE @I INT  
  20. --Declare @UserID Int = (Select CreateBy From dbo.ImporterQueue Where ImporterQueueID = @ImporterQueueID)  
  21. Declare @ImportingStartDate DateTime = GetDate(), @DurationInSeconds Int  
  22.   
  23. Update dbo.ImporterQueue Set  
  24. ImportingStartDate = @ImportingStartDate,  
  25. OverAllStatusID = 2, -- In Progress  
  26. StatusReason = Null,  
  27. UpdateBy = #results.CreateBy,  
  28. UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID  
  29. --Begin Transaction Trans  
  30. Begin Try  
  31. SELECT @I = 1  
  32. SELECT @totalRecords = COUNT(ImporterQueueID) FROM #results  
  33. WHILE (@I <= @totalRecords)  
  34. BEGIN  
  35. declare @ProcedureName Nvarchar(200) = (SELECT StoredProcedureName FROM #results WHERE rownumber = @I),  
  36. --@UserIDString Varchar(20) = Convert(Varchar(20), @UserID),  
  37. @ImporterQueueIDString Varchar(20) = (SELECT StoredProcedureName FROM #results WHERE rownumber = @I),  
  38. @InputFilePath Nvarchar(500) = (SELECT InputFilePath FROM #results WHERE rownumber = @I),  
  39. @OutputFilePath Nvarchar(500) = (SELECT OutputFilePath FROM #results WHERE rownumber = @I)  
  40.   
  41. Declare @SQLvalue Nvarchar(1000) = 'EXECUTE ' + @ProcedureName + ' ' + @ImporterQueueIDString + ' , ' + '''' + @InputFilePath + '''' + ' , ' + '''' + @OutputFilePath + '''' + ''  
  42. Exec(@SQLvalue)  
  43.     SELECT @I = @I + 1   
  44. END
  45. --Commit Transaction Trans  
  46. Update dbo.ImporterQueue Set  
  47. DurationInSeconds = DATEDIFF(SECOND, @ImportingStartDate, GetDate()),  
  48. OverAllStatusID = 3, -- Done  
  49. StatusReason = Null,  
  50. UpdateBy = #results.CreateBy,  
  51. UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID  
  52. End Try  
  53.   
  54. Begin Catch  
  55. --RollBack Transaction Trans  
  56. Update dbo.ImporterQueue Set  
  57. DurationInSeconds = DATEDIFF(SECOND, @ImportingStartDate, GetDate()),  
  58. OverAllStatusID = 4, -- Failed  
  59. StatusReason = ERROR_MESSAGE(),  
  60. UpdateBy = #results.CreateBy,  
  61. UpdateDate = GetDate() from dbo.ImporterQueue inner join #results on ImporterQueue.ImporterQueueID=#results.ImporterQueueID  
  62.   
  63. End Catch

Answers (2)