How to diagnosed and optimize SQL Query

Question

Hi All I have a query it's inserting around 9 lakh of records into the database in per hit our SQL server exists on the remote location. Due to this query performance client is not happy. So trying to optimize this bellowed mentioned query with the help of query optimizer (Query Execution Plan) During analysis through execution plan we got one warning per this warning I created non-cluster Index on temp table Transection column but after that we have not seen any good result. INSERT INTO FeeAnalysis (FeeAnalysis Transactions Matters MatterID ShortDesc AreaOfLaw MatterType ClientSort Professionals Initials Office ProfClass ProfDept ProfType ProfName TaskCodes CodeId Transactions.MattersProjects Project_Description MattersProjects.ProjectAmount ActivityCodeId  ActivityCodeDesc MTDWorkedUnits MTDWorkedBUnits MTDWorkedAUnits MTDWorkedExtAmt MTDWorkedBExtAmt MTDWorkedAExtAmt MTDWorkedValue MTDWorkedBValue MTDWorkedAValue MTDBilledUnits MTDBilledExtAmt MTDBilledValue MTDBilledDiscount MTDBilledWUD YTDWorkedUnits YTDWorkedBUnits YTDWorkedAUnits YTDWorkedExtAmt YTDWorkedBExtAmt YTDWorkedAExtAmt YTDWorkedValue YTDWorkedBValue YTDWorkedAValue YTDBilledUnits YTDBilledExtAmt YTDBilledValue YTDBilledDiscount YTDBilledWUD FYTDWorkedUnits FYTDWorkedBUnits FYTDWorkedAUnits FYTDWorkedExtAmt FYTDWorkedBExtAmt FYTDWorkedAExtAmt FYTDWorkedValue FYTDWorkedBValue FYTDWorkedAValue FYTDBilledUnits FYTDBilledExtAmt FYTDBilledValue FYTDBilledDiscount FYTDBilledWUD  CurrentProfessionals ReportId ReportDateTime ) SELECT NewID() Transactions.Transactions Matters.Matters Matters.MatterID Matters.ShortDesc Matters.AreaOfLaw Matters.MatterType Matters.ClientSort Professionals.Professionals Professionals.Initials Professionals.Office Professionals.ProfClass Professionals.ProfDept Professionals.ProfType Professionals.ProfName TaskCodes.TaskCodes TaskCodes.CodeId Transactions.MattersProjects MattersProjects.ProjectDesc MattersProjects.ProjectAmount ActivityCodes.ActivityCodeId  ActivityCodes.ActivityCodeDesc dMTDWorkedUnits dMTDWorkedBUnits dMTDWorkedAUnits dMTDWorkedExtAmt dMTDWorkedBExtAmt dMTDWorkedAExtAmt dMTDWorkedValue dMTDWorkedBValue dMTDWorkedAValue dMTDBilledUnits dMTDBilledExtAmt dMTDBilledValue dMTDBilledDiscount dMTDBilledWUD dYTDWorkedUnits dYTDWorkedBUnits dYTDWorkedAUnits dYTDWorkedExtAmt dYTDWorkedBExtAmt dYTDWorkedAExtAmt dYTDWorkedValue dYTDWorkedBValue dYTDWorkedAValue dYTDBilledUnits dYTDBilledExtAmt dYTDBilledValue dYTDBilledDiscount dYTDBilledWUD dFYTDWorkedUnits dFYTDWorkedBUnits dFYTDWorkedAUnits dFYTDWorkedExtAmt dFYTDWorkedBExtAmt dFYTDWorkedAExtAmt dFYTDWorkedValue dFYTDWorkedBValue dFYTDWorkedAValue dFYTDBilledUnits dFYTDBilledExtAmt dFYTDBilledValue dFYTDBilledDiscount dFYTDBilledWUD  '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' '9604d85c-5edc-46e1-95a7-35eca286e41c' GetDate() FROM #FeeAnalysis INNER JOIN Transactions ON Transactions.Transactions = #FeeAnalysis.Transactions LEFT OUTER JOIN MattersProjects ON Transactions.MattersProjects = MattersProjects.MattersProjects LEFT OUTER JOIN ActivityCodes ON Transactions.ActivityCodes = ActivityCodes.ActivityCodes LEFT OUTER JOIN Professionals ON Transactions.Professionals = Professionals.Professionals LEFT OUTER JOIN TaskCodes ON Transactions.TaskCodes = TaskCodes.TaskCodes LEFT OUTER JOIN StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger INNER JOIN Matters ON Transactions.Matters = Matters.Matters INNER JOIN SecurityILSKey ON SecurityILSKey.ILSKey = Matters.ILSKey AND SecurityILSKey.Professionals = '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' LEFT OUTER JOIN MatterTypes ON Matters.MatterType = MatterTypes.MatterTypesDesc INNER JOIN Components ON Transactions.Components = Components.Components (SELECT 1 AS AssignedPercent) AS Thing So I just want to know is there any way to optimize the time of my query or any suggested step so that I found specific area where we will do some updating and then got accepted result with in minimum seconds. Another thing after inserting records through this query when we will go for deleting records then It’s also take too much time with simple delete query. delete FROM AssignedProfs WHERE (ReportId = '9604d85c-5edc-46e1-95a7-35eca286e41c') OR (CurrentProfessionals = '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' AND DateDiff (hh ReportDateTime GetDate()) > 60) Please help me or share your experience with me. Execution plan is attached with post.

Nitin Sontakke · Answer

First I did not understand the last statement at all.  (SELECT 1 AS AssignedPercent) AS Thing Where is this query being executed? In a job? Where exactly? The problem is you are probably inserting 9L records in one go and the entire operation is logged (which also means it is executing within an implicit transaction). Which means SQL Server is logging every record in transaction log because you might issue rollback sometime and SQL Server should be prepared to roll it back. The same is happening at the time of DELETE. There are ways to avoid this scenarios or perform same thing for set of transactions for example say 10K or 20K at a time. Hope you get the point.

Manish Kumar · Answer

May be due to OR condition in your where clause. delete FROM AssignedProfs WHERE (ReportId = '9604d85c-5edc-46e1-95a7-35eca286e41c') OR (CurrentProfessionals = '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' AND DateDiff (hh ReportDateTime GetDate()) > 60) You can try like in below if object_id('tempdb..#temp') is not null drop table #temp create table #temp (use your primary key column here from your deleted table as primary key) insert into #temp select yourPK from AssignedProfs where ReportId = '9604d85c-5edc-46e1-95a7-35eca286e41c' and DateDiff (hh ReportDateTime GetDate()) > 60) UNION ALL select yourPK from AssignedProfs where CurrentProfessionals = '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' AND DateDiff (hh ReportDateTime GetDate()) > 60) declare @rwcnt int set rowcount 10000 ---deleting records in batch while (1=1) begin delete a from AssignedProfs a inner join #temp b on a. AssignedProfs PK column=b.pkcolumn set @rwcnt=@@rowcount if @rwcnt=0 begin break end end

kapil khare · Answer

Thanks Guys I also agree with your solution but I made few changes after that query result is much better compare to previous one. IF OBJECT_ID('tempdb..#tMatters') IS NOT NULL drop table #tMatters; select Matters.Matters Matters.MatterID Matters.ShortDesc Matters.AreaOfLaw Matters.MatterType Matters.ClientSort Into #tMatters from Matters INNER JOIN SecurityILSKey ON SecurityILSKey.ILSKey = Matters.ILSKey AND SecurityILSKey.Professionals =’ currentProfessional’ LEFT JOIN MatterTypes ON Matters.MatterType = MatterTypes.MatterTypesDesc; CREATE CLUSTERED INDEX IDX_tMatters_UserID ON #tMatters(Matters)  ; SELECT NewID () as Id  Transactions . Transactions  #tMatters . Matters  #tMatters . MatterID  #tMatters . ShortDesc  #tMatters . AreaOfLaw  #tMatters . MatterType  #tMatters . ClientSort  Professionals . Professionals  Professionals . Initials  Professionals . Office  Professionals . ProfClass  Professionals . ProfDept  Professionals . ProfType  Professionals . ProfName  TaskCodes . TaskCodes  TaskCodes . CodeId  Transactions . MattersProjects  MattersProjects . ProjectDesc  MattersProjects . ProjectAmount  ActivityCodes . ActivityCodeId  ActivityCodes . ActivityCodeDesc -- dMTDWorkedUnits dMTDWorkedBUnits dMTDWorkedAUnits dMTDWorkedExtAmt dMTDWorkedBExtAmt dMTDWorkedAExtAmt dMTDWorkedValue dMTDWorkedBValue dMTDWorkedAValue dMTDBilledUnits dMTDBilledExtAmt dMTDBilledValue dMTDBilledDiscount dMTDBilledWUD dYTDWorkedUnits dYTDWorkedBUnits dYTDWorkedAUnits dYTDWorkedExtAmt dYTDWorkedBExtAmt dYTDWorkedAExtAmt dYTDWorkedValue dYTDWorkedBValue dYTDWorkedAValue dYTDBilledUnits dYTDBilledExtAmt dYTDBilledValue dYTDBilledDiscount dYTDBilledWUD dFYTDWorkedUnits dFYTDWorkedBUnits dFYTDWorkedAUnits dFYTDWorkedExtAmt dFYTDWorkedBExtAmt dFYTDWorkedAExtAmt dFYTDWorkedValue dFYTDWorkedBValue dFYTDWorkedAValue dFYTDBilledUnits dFYTDBilledExtAmt dFYTDBilledValue dFYTDBilledDiscount dFYTDBilledWUD  '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' '8ef39b18-7e8d-4dac-93b1-bd458bfeb240' GetDate() --into #t FROM FeeAnalysis INNER JOIN Transactions ON Transactions . Transactions = FeeAnalysis . Transactions LEFT OUTER JOIN MattersProjects ON Transactions . MattersProjects = MattersProjects . MattersProjects LEFT OUTER JOIN ActivityCodes ON Transactions . ActivityCodes = ActivityCodes . ActivityCodes LEFT OUTER JOIN Professionals ON Transactions . Professionals = Professionals . Professionals LEFT OUTER JOIN TaskCodes ON Transactions . TaskCodes = TaskCodes . TaskCodes LEFT OUTER JOIN StmnLedger ON Transactions . StmnLedger = StmnLedger . StmnLedger INNER JOIN #tMatters ON Transactions . Matters = #tMatters . Matters --INNER JOIN SecurityILSKey ON SecurityILSKey.ILSKey = Matters.ILSKey AND SecurityILSKey.Professionals = '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' --LEFT OUTER JOIN MatterTypes ON Matters.MatterType = MatterTypes.MatterTypesDesc INNER JOIN Components ON Transactions . Components = Components . Components It's working fine per compared to the previous result before this change Time takes through the Application is around 02:08 after creating the separate table it would now take around one minute. So it's good. is there any way to improve more this query? but my concert is still same with a delete command. we used simply deleted command as follow but it takes too much time I know due too many records deleted in One Sort or same time lots of logs insert entered in the transaction table.so it takes too much time. delete FROM AssignedProfs WHERE (ReportId = '9604d85c-5edc-46e1-95a7-35eca286e41c') OR (CurrentProfessionals = '07138f0d-55d8-4aaf-bf84-a6dbf1251d98' AND DateDiff (hh ReportDateTime GetDate()) > 60) is there any way to optimize this delete query?

Manish Kumar · Answer

Agree with Nitin comment.9 Lakhs records in one shot will take time. You need to insert records in chunks or batch (or try offset and fetch ) Also for deleteing try to capture those records who are going to delete in one temp table(take pk value column) and delete in batch or chunks.