Reduce Query Execution Time

• pani.muni

  SSC Enthusiast

  Points: 181

  More actions

  April 5, 2012 at 3:30 am

  #259489

  Hi All,

  I'm using sql server 2008 r2 for .net application and if i run below query it takes 50 seconds to 3 minutes. So,it cause performance issue while loading grid at form level.

  can any one help me how to optimize this query to speed up the process....

  SELECT DISTINCT

  TOP (100) PERCENT dbo.Job.Id, dbo.Contact.Id AS ContactId, dbo.Contact.Name AS ContactName, dbo.Person.Id AS PersonId,

  Salutation_Ref.Value + dbo.Person.FirstName + ' ' + dbo.Person.Surname AS PersonName, Client_Contact.Id AS ClientId,

  Client_Contact.Name AS ClientName, dbo.CustomerClientLocations.Id AS CustomerClientLocId, dbo.Site.Id AS SiteId, dbo.Site.Name AS SiteName,

  dbo.Site.AddressLn1 + ' ' + ISNULL(dbo.Site.AddressLn2, '')

  + ', ' + dbo.View_Suburb.SuburbName + ', ' + dbo.View_Suburb.CityName + ', ' + dbo.View_Suburb.StateName + ' - ' + CAST(dbo.View_Suburb.PostCode AS

  varchar(20)) + ' ' + dbo.View_Suburb.CountryName AS SiteAddress, dbo.Site.SpecialInstruction AS SiteSpecialInst, dbo.Job.DueDate,

  dbo.Job.AppointmentDate, dbo.Job.ReminderDate, dbo.Locations.Id AS LocationId, dbo.Locations.Description AS LocationDesc,

  dbo.Locations.SpecialInstruction AS LocSpecialInst, dbo.Job.PurchaseOrderNo, dbo.Currency.Id AS CurrencyId, dbo.Currency.Name AS CurrencyName,

  dbo.Currency.Symbol AS CurrencySymbol, dbo.Job.Comment, Prioity_Ref.Id AS PriorityRefId, Prioity_Ref.Value AS JobPriority,

  Origin_Ref.Id AS OriginRefId, Origin_Ref.Value AS JobOrigin, dbo.Job.AssetInfoRequired, WorkOrderOrigin_Ref.Id AS WorkOrderOriginRefId,

  WorkOrderOrigin_Ref.Value AS WorkOrderOrigin, WorkOrderType_Ref.Id AS WorkOrderTypeRefId, WorkOrderType_Ref.Value AS WorkOrderType,

  dbo.Job.CustomerOrderNo, dbo.Asset.Id AS AssetId, dbo.Asset.SerialNo, dbo.Asset.CustomerAssetNo, dbo.Asset.EquipmentNo, dbo.Asset.KeyNo,

  dbo.Job.WorkOrderId, dbo.View_Model.ModelId, dbo.View_Model.ModelNo, dbo.View_Model.ModelDesc, JobAssetModel.Id AS JobModelId,

  JobAssetModel.Number AS JobModelNo, JobAssetModel.Name AS JobModelDesc, dbo.Job.ReceivedDate, dbo.Asset.PurchaseDate,

  dbo.Job.StatusRefId AS JobStatusRefId, dbo.Reference.Value AS JobStatus, dbo.Job.IsDispatch, dbo.Job.IsStored,

  Reference_1.Id AS WorkshopJobTypeRefId, Reference_1.Value AS WorkshopJobType, dbo.Job.IsQuoted, dbo.Job.IsQuoteAuthorised,

  dbo.Job.WarrantyMstId, dbo.Contact.OID,

  MIN(dbo.JobCardActions.StartTime) AS MinActionStartTime, dbo.View_JobActionTotal.ActionTotalExcl,

  dbo.View_JobPartsTotal.PartsTotalExcl, dbo.View_JobChargesTotal.ChargesTotalExcl, dbo.View_JobFreightTotal.AmountExcl,

  ISNULL(dbo.View_JobActionTotal.ActionTotalExcl, 0) + ISNULL(dbo.View_JobPartsTotal.PartsTotalExcl, 0)

  + ISNULL(dbo.View_JobChargesTotal.ChargesTotalExcl, 0) + ISNULL(dbo.View_JobFreightTotal.AmountExcl, 0) AS JobTotalExcl

  FROM dbo.View_JobActionTotal INNER JOIN

  dbo.Reference INNER JOIN

  dbo.Reference AS Salutation_Ref INNER JOIN

  dbo.Person INNER JOIN

  dbo.Job ON dbo.Person.Id = dbo.Job.PersonId ON Salutation_Ref.Id = dbo.Person.GreetingsRefId INNER JOIN

  dbo.ProjectMst ON dbo.Job.PID = dbo.ProjectMst.PID INNER JOIN

  dbo.CustomerClientLocations INNER JOIN

  dbo.Contact INNER JOIN

  dbo.Contact AS Client_Contact INNER JOIN

  dbo.CustomerClient ON Client_Contact.Id = dbo.CustomerClient.ClientId ON dbo.Contact.Id = dbo.CustomerClient.CustomerId ON

  dbo.CustomerClientLocations.CustomerClientId = dbo.CustomerClient.Id INNER JOIN

  dbo.Site INNER JOIN

  dbo.Locations ON dbo.Site.Id = dbo.Locations.SiteId ON dbo.CustomerClientLocations.LocationId = dbo.Locations.Id INNER JOIN

  dbo.View_Suburb ON dbo.Site.SuburbId = dbo.View_Suburb.SuburbId ON dbo.Job.CustClientLocID = dbo.CustomerClientLocations.Id ON

  dbo.Reference.Id = dbo.Job.StatusRefId ON dbo.View_JobActionTotal.JobId = dbo.Job.Id LEFT OUTER JOIN

  dbo.View_JobFreightTotal ON dbo.Job.Id = dbo.View_JobFreightTotal.JobId LEFT OUTER JOIN

  dbo.View_JobChargesTotal ON dbo.Job.Id = dbo.View_JobChargesTotal.JobId LEFT OUTER JOIN

  dbo.View_JobPartsTotal ON dbo.Job.Id = dbo.View_JobPartsTotal.JobId LEFT OUTER JOIN

  dbo.JobCardMst LEFT OUTER JOIN

  dbo.JobCardActions ON dbo.JobCardMst.Id = dbo.JobCardActions.JobCardMstId ON dbo.Job.Id = dbo.JobCardMst.JobId LEFT OUTER JOIN

  dbo.View_Model INNER JOIN

  dbo.Asset ON dbo.View_Model.ModelId = dbo.Asset.ModelId ON dbo.Job.AssetId = dbo.Asset.Id LEFT OUTER JOIN

  dbo.AssetAllocation ON dbo.Job.AllocationId = dbo.AssetAllocation.Id LEFT OUTER JOIN

  dbo.WarrantyMst ON dbo.Job.WarrantyMstId = dbo.WarrantyMst.Id LEFT OUTER JOIN

  dbo.Reference AS Reference_1 ON dbo.Job.WorkshopJobTypeRefId = Reference_1.Id LEFT OUTER JOIN

  dbo.AssetModel AS JobAssetModel ON dbo.Job.ModelId = JobAssetModel.Id LEFT OUTER JOIN

  dbo.Currency ON dbo.Job.CurrencyId = dbo.Currency.Id LEFT OUTER JOIN

  dbo.Reference AS Prioity_Ref ON dbo.Job.PriorityRefId = Prioity_Ref.Id LEFT OUTER JOIN

  dbo.Reference AS WorkOrderType_Ref ON dbo.Job.WorkOrderTypeRefId = WorkOrderType_Ref.Id LEFT OUTER JOIN

  dbo.Reference AS WorkOrderOrigin_Ref ON dbo.Job.WorkOrderOriginRefId = WorkOrderOrigin_Ref.Id LEFT OUTER JOIN

  dbo.Reference AS Origin_Ref ON dbo.Job.OriginRefId = Origin_Ref.Id

  WHERE (dbo.JobCardMst.Id IN

  (SELECT Id

  FROM dbo.JobCardMst AS JobCardMst_1

  WHERE (IsInvoiced = 0) AND (IsAuthorised = 0) AND (StatusRefId IN (113, 104, 105, 532))))

  GROUP BY dbo.Job.Id, dbo.Contact.Id, dbo.Contact.Name, dbo.Person.Id, Salutation_Ref.Value + dbo.Person.FirstName + ' ' + dbo.Person.Surname,

  Client_Contact.Id, Client_Contact.Name, dbo.CustomerClientLocations.Id, dbo.Site.Id, dbo.Site.Name,

  dbo.Site.AddressLn1 + ' ' + ISNULL(dbo.Site.AddressLn2, '')

  + ', ' + dbo.View_Suburb.SuburbName + ', ' + dbo.View_Suburb.CityName + ', ' + dbo.View_Suburb.StateName + ' - ' + CAST(dbo.View_Suburb.PostCode AS

  varchar(20)) + ' ' + dbo.View_Suburb.CountryName, dbo.Site.SpecialInstruction, dbo.Job.DueDate, dbo.Job.AppointmentDate, dbo.Job.ReminderDate,

  dbo.Locations.Id, dbo.Locations.Description, dbo.Locations.SpecialInstruction, dbo.Job.PurchaseOrderNo, dbo.Currency.Id, dbo.Currency.Name,

  dbo.Currency.Symbol, dbo.Job.Comment, Prioity_Ref.Id, Prioity_Ref.Value, Origin_Ref.Id, Origin_Ref.Value, WorkOrderOrigin_Ref.Id,

  WorkOrderOrigin_Ref.Value, WorkOrderType_Ref.Id, WorkOrderType_Ref.Value, dbo.Job.CustomerOrderNo, dbo.Asset.Id, dbo.Asset.SerialNo,

  dbo.Asset.CustomerAssetNo, dbo.Asset.EquipmentNo, dbo.Asset.KeyNo, dbo.Job.WorkOrderId, dbo.View_Model.ModelId, dbo.View_Model.ModelNo,

  dbo.View_Model.ModelDesc, JobAssetModel.Id, JobAssetModel.Number, JobAssetModel.Name, dbo.Job.ReceivedDate, dbo.Asset.PurchaseDate,

  dbo.Job.StatusRefId, dbo.Reference.Value, Reference_1.Id, Reference_1.Value, dbo.Job.WarrantyMstId, dbo.Contact.OID, dbo.Job.AssetInfoRequired,

  dbo.Job.IsDispatch, dbo.Job.IsStored, dbo.Job.IsQuoted, dbo.Job.IsQuoteAuthorised, dbo.View_JobActionTotal.ActionTotalExcl,

  dbo.View_JobPartsTotal.PartsTotalExcl, dbo.View_JobChargesTotal.ChargesTotalExcl, ISNULL(dbo.View_JobActionTotal.ActionTotalExcl, 0)

  + ISNULL(dbo.View_JobPartsTotal.PartsTotalExcl, 0) + ISNULL(dbo.View_JobChargesTotal.ChargesTotalExcl, 0)

  + ISNULL(dbo.View_JobFreightTotal.AmountExcl, 0), dbo.View_JobFreightTotal.AmountExcl

  ORDER BY dbo.Job.ReceivedDate DESC

  Thanks & Best Regards,

  Chakrapani M

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  April 5, 2012 at 4:06 am

  #1469374

  Your query contains all sort of evil. You really need to rewrite it.

  First of all, let's format the code to make it more readable:

  SELECT DISTINCT TOP (100) PERCENT dbo.Job.Id

  ,dbo.Contact.Id AS ContactId

  ,dbo.Contact.NAME AS ContactName

  ,dbo.Person.Id AS PersonId

  ,Salutation_Ref.Value + dbo.Person.FirstName + ' ' + dbo.Person.Surname AS PersonName

  ,Client_Contact.Id AS ClientId

  ,Client_Contact.NAME AS ClientName

  ,dbo.CustomerClientLocations.Id AS CustomerClientLocId

  ,dbo.Site.Id AS SiteId

  ,dbo.Site.NAME AS SiteName

  ,dbo.Site.AddressLn1 + ' ' + ISNULL(dbo.Site.AddressLn2, '')

  + ', ' + dbo.View_Suburb.SuburbName

  + ', ' + dbo.View_Suburb.CityName

  + ', ' + dbo.View_Suburb.StateName

  + ' - ' + CAST(dbo.View_Suburb.PostCode AS VARCHAR(20))

  + ' ' + dbo.View_Suburb.CountryName AS SiteAddress

  ,dbo.Site.SpecialInstruction AS SiteSpecialInst

  ,dbo.Job.DueDate

  ,dbo.Job.AppointmentDate

  ,dbo.Job.ReminderDate

  ,dbo.Locations.Id AS LocationId

  ,dbo.Locations.Description AS LocationDesc

  ,dbo.Locations.SpecialInstruction AS LocSpecialInst

  ,dbo.Job.PurchaseOrderNo

  ,dbo.Currency.Id AS CurrencyId

  ,dbo.Currency.NAME AS CurrencyName

  ,dbo.Currency.Symbol AS CurrencySymbol

  ,dbo.Job.Comment

  ,Prioity_Ref.Id AS PriorityRefId

  ,Prioity_Ref.Value AS JobPriority

  ,Origin_Ref.Id AS OriginRefId

  ,Origin_Ref.Value AS JobOrigin

  ,dbo.Job.AssetInfoRequired

  ,WorkOrderOrigin_Ref.Id AS WorkOrderOriginRefId

  ,WorkOrderOrigin_Ref.Value AS WorkOrderOrigin

  ,WorkOrderType_Ref.Id AS WorkOrderTypeRefId

  ,WorkOrderType_Ref.Value AS WorkOrderType

  ,dbo.Job.CustomerOrderNo

  ,dbo.Asset.Id AS AssetId

  ,dbo.Asset.SerialNo

  ,dbo.Asset.CustomerAssetNo

  ,dbo.Asset.EquipmentNo

  ,dbo.Asset.KeyNo

  ,dbo.Job.WorkOrderId

  ,dbo.View_Model.ModelId

  ,dbo.View_Model.ModelNo

  ,dbo.View_Model.ModelDesc

  ,JobAssetModel.Id AS JobModelId

  ,JobAssetModel.Number AS JobModelNo

  ,JobAssetModel.NAME AS JobModelDesc

  ,dbo.Job.ReceivedDate

  ,dbo.Asset.PurchaseDate

  ,dbo.Job.StatusRefId AS JobStatusRefId

  ,dbo.Reference.Value AS JobStatus

  ,dbo.Job.IsDispatch

  ,dbo.Job.IsStored

  ,Reference_1.Id AS WorkshopJobTypeRefId

  ,Reference_1.Value AS WorkshopJobType

  ,dbo.Job.IsQuoted

  ,dbo.Job.IsQuoteAuthorised

  ,dbo.Job.WarrantyMstId

  ,dbo.Contact.OID

  ,MIN(dbo.JobCardActions.StartTime) AS MinActionStartTime

  ,dbo.View_JobActionTotal.ActionTotalExcl

  ,dbo.View_JobPartsTotal.PartsTotalExcl

  ,dbo.View_JobChargesTotal.ChargesTotalExcl

  ,dbo.View_JobFreightTotal.AmountExcl

  ,ISNULL(dbo.View_JobActionTotal.ActionTotalExcl, 0)

  + ISNULL(dbo.View_JobPartsTotal.PartsTotalExcl, 0)

  + ISNULL(dbo.View_JobChargesTotal.ChargesTotalExcl, 0)

  + ISNULL(dbo.View_JobFreightTotal.AmountExcl, 0) AS JobTotalExcl

  FROM dbo.View_JobActionTotal

  INNER JOIN dbo.Reference

  INNER JOIN dbo.Reference AS Salutation_Ref

  INNER JOIN dbo.Person

  INNER JOIN dbo.Job

  ON dbo.Person.Id = dbo.Job.PersonId

  ON Salutation_Ref.Id = dbo.Person.GreetingsRefId

  INNER JOIN dbo.ProjectMst

  ON dbo.Job.PID = dbo.ProjectMst.PID

  INNER JOIN dbo.CustomerClientLocations

  INNER JOIN dbo.Contact

  INNER JOIN dbo.Contact AS Client_Contact

  INNER JOIN dbo.CustomerClient

  ON Client_Contact.Id = dbo.CustomerClient.ClientId

  ON dbo.Contact.Id = dbo.CustomerClient.CustomerId

  ON dbo.CustomerClientLocations.CustomerClientId = dbo.CustomerClient.Id

  INNER JOIN dbo.Site

  INNER JOIN dbo.Locations

  ON dbo.Site.Id = dbo.Locations.SiteId

  ON dbo.CustomerClientLocations.LocationId = dbo.Locations.Id

  INNER JOIN dbo.View_Suburb

  ON dbo.Site.SuburbId = dbo.View_Suburb.SuburbId

  ON dbo.Job.CustClientLocID = dbo.CustomerClientLocations.Id

  ON dbo.Reference.Id = dbo.Job.StatusRefId

  ON dbo.View_JobActionTotal.JobId = dbo.Job.Id

  LEFT JOIN dbo.View_JobFreightTotal

  ON dbo.Job.Id = dbo.View_JobFreightTotal.JobId

  LEFT JOIN dbo.View_JobChargesTotal

  ON dbo.Job.Id = dbo.View_JobChargesTotal.JobId

  LEFT JOIN dbo.View_JobPartsTotal

  ON dbo.Job.Id = dbo.View_JobPartsTotal.JobId

  LEFT JOIN dbo.JobCardMst

  LEFT JOIN dbo.JobCardActions

  ON dbo.JobCardMst.Id = dbo.JobCardActions.JobCardMstId

  ON dbo.Job.Id = dbo.JobCardMst.JobId

  LEFT JOIN dbo.View_Model

  INNER JOIN dbo.Asset

  ON dbo.View_Model.ModelId = dbo.Asset.ModelId

  ON dbo.Job.AssetId = dbo.Asset.Id

  LEFT JOIN dbo.AssetAllocation

  ON dbo.Job.AllocationId = dbo.AssetAllocation.Id

  LEFT JOIN dbo.WarrantyMst

  ON dbo.Job.WarrantyMstId = dbo.WarrantyMst.Id

  LEFT JOIN dbo.Reference AS Reference_1

  ON dbo.Job.WorkshopJobTypeRefId = Reference_1.Id

  LEFT JOIN dbo.AssetModel AS JobAssetModel

  ON dbo.Job.ModelId = JobAssetModel.Id

  LEFT JOIN dbo.Currency

  ON dbo.Job.CurrencyId = dbo.Currency.Id

  LEFT JOIN dbo.Reference AS Prioity_Ref

  ON dbo.Job.PriorityRefId = Prioity_Ref.Id

  LEFT JOIN dbo.Reference AS WorkOrderType_Ref

  ON dbo.Job.WorkOrderTypeRefId = WorkOrderType_Ref.Id

  LEFT JOIN dbo.Reference AS WorkOrderOrigin_Ref

  ON dbo.Job.WorkOrderOriginRefId = WorkOrderOrigin_Ref.Id

  LEFT JOIN dbo.Reference AS Origin_Ref

  ON dbo.Job.OriginRefId = Origin_Ref.Id

  WHERE (

  dbo.JobCardMst.Id IN (

  SELECT Id

  FROM dbo.JobCardMst AS JobCardMst_1

  WHERE (IsInvoiced = 0)

  AND (IsAuthorised = 0)

  AND (

  StatusRefId IN (

  113

  ,104

  ,105

  ,532

  )

  )

  )

  )

  GROUP BY dbo.Job.Id

  ,dbo.Contact.Id

  ,dbo.Contact.NAME

  ,dbo.Person.Id

  ,Salutation_Ref.Value + dbo.Person.FirstName + ' ' + dbo.Person.Surname

  ,Client_Contact.Id

  ,Client_Contact.NAME

  ,dbo.CustomerClientLocations.Id

  ,dbo.Site.Id

  ,dbo.Site.NAME

  ,dbo.Site.AddressLn1 + ' ' + ISNULL(dbo.Site.AddressLn2, '')

  + ', ' + dbo.View_Suburb.SuburbName

  + ', ' + dbo.View_Suburb.CityName

  + ', ' + dbo.View_Suburb.StateName

  + ' - ' + CAST(dbo.View_Suburb.PostCode AS VARCHAR(20)) + ' ' + dbo.View_Suburb.CountryName

  ,dbo.Site.SpecialInstruction

  ,dbo.Job.DueDate

  ,dbo.Job.AppointmentDate

  ,dbo.Job.ReminderDate

  ,dbo.Locations.Id

  ,dbo.Locations.Description

  ,dbo.Locations.SpecialInstruction

  ,dbo.Job.PurchaseOrderNo

  ,dbo.Currency.Id

  ,dbo.Currency.NAME

  ,dbo.Currency.Symbol

  ,dbo.Job.Comment

  ,Prioity_Ref.Id

  ,Prioity_Ref.Value

  ,Origin_Ref.Id

  ,Origin_Ref.Value

  ,WorkOrderOrigin_Ref.Id

  ,WorkOrderOrigin_Ref.Value

  ,WorkOrderType_Ref.Id

  ,WorkOrderType_Ref.Value

  ,dbo.Job.CustomerOrderNo

  ,dbo.Asset.Id

  ,dbo.Asset.SerialNo

  ,dbo.Asset.CustomerAssetNo

  ,dbo.Asset.EquipmentNo

  ,dbo.Asset.KeyNo

  ,dbo.Job.WorkOrderId

  ,dbo.View_Model.ModelId

  ,dbo.View_Model.ModelNo

  ,dbo.View_Model.ModelDesc

  ,JobAssetModel.Id

  ,JobAssetModel.Number

  ,JobAssetModel.NAME

  ,dbo.Job.ReceivedDate

  ,dbo.Asset.PurchaseDate

  ,dbo.Job.StatusRefId

  ,dbo.Reference.Value

  ,Reference_1.Id

  ,Reference_1.Value

  ,dbo.Job.WarrantyMstId

  ,dbo.Contact.OID

  ,dbo.Job.AssetInfoRequired

  ,dbo.Job.IsDispatch

  ,dbo.Job.IsStored

  ,dbo.Job.IsQuoted

  ,dbo.Job.IsQuoteAuthorised

  ,dbo.View_JobActionTotal.ActionTotalExcl

  ,dbo.View_JobPartsTotal.PartsTotalExcl

  ,dbo.View_JobChargesTotal.ChargesTotalExcl

  ,ISNULL(dbo.View_JobActionTotal.ActionTotalExcl, 0)

  + ISNULL(dbo.View_JobPartsTotal.PartsTotalExcl, 0)

  + ISNULL(dbo.View_JobChargesTotal.ChargesTotalExcl, 0)

  + ISNULL(dbo.View_JobFreightTotal.AmountExcl, 0)

  ,dbo.View_JobFreightTotal.AmountExcl

  ORDER BY dbo.Job.ReceivedDate DESC

  You will notice that many ON predicates have been moved away from the respective JOIN, which is totally useless for INNER JOINs.

  This query has probably been generated by the visual query editor, which is one of the worst tools ever for writing high-performing queries.

  That DISTINCT is a clue that not all the tables in the query are there for selecting data, but for filtering purposes. Consider rewriting those filters as WHERE EXISTS predicates.

  The huge GROUP BY clause on computed fields won't help performance: consider rewriting the aggregate query as a subquery and leave the rest of the statement out of the GROUP BY clause.

  What else can I say... good luck with this!

  -- Gianluca Sartori

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  April 5, 2012 at 4:10 am

  #1469377

  On another note, I would suggest resisting the temptation to throw indexes at this query before fixing it.

  A smart rewrite will probably fix the performance issue without the need for additional indexes.

  -- Gianluca Sartori

• Grant Fritchey

  SSC Guru

  Points: 399363

  More actions

  April 5, 2012 at 5:49 am

  #1469428

  You're doing JOIN to other views. That is one seriously problematic model. I strongly recommend against it. You're shooting the optimizer right in the foot before asking it to run. There's no quick wins in this query. You're going to need to rewrite it from scratch.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning