|
panimuni
Starting Member
1 Post |
 Posted - 2012-04-05 : 02:08:30
|
| 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 DESCThanks & Best Regards,Chakrapani M |
|
Reduce Query Execution Time