How to diagnosed and optimize SQL Query

Sign in to answer this question.

• MK

  Manish Kumar9y agoPosted Nov 15, 2016, 9:55 AM

  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 
• KK

  Kapil Khare9y agoPosted Nov 15, 2016, 8:26 AM

  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?
• MK

  Manish Kumar9y agoPosted Nov 15, 2016, 4:14 AM

  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.
• 

  Nitin Sontakke9y agoPosted Nov 14, 2016, 10:35 PM

  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.