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.