November 12, 2009 at 7:38 am
Hi folks,
I'm usually an ASP.NET developer. That's why I hit a wall while diagnosing a slow query. Here's what I'm trying to do.
We have 2 databases which are created using the same script. They do not contain the same data though because they are for different clients. When executing the query on database 1, it takes 2 seconds to complete. On database 2, the exact same query takes about 40 seconds to complete. Both databases contain about the same number of rows.
I checked the execution plan of this query for both databases and they are completely different. Is this normal? SQL Server seems to make its execution plan based on data contained in the databases rather than the schema.
Here's the query:
SELECT DISTINCT c.IdCall
, IsNull(c.roadorder, 0) as roadorder
, cs.DescriptionFr as statusFR
, cs.DescriptionEn as statusEN
, cr.DescriptionFr as reasonFR
, cr.DescriptionEn as reasonEN
, c.IdCallStatus
, c.IdCancelReason
, IsNull( (SELECT td.textvalue
FROM lookupattributevalue lav
JOIN lookupattributeitemvalue laiv ON laiv.idlookupattributeitemvalue = lav.value
JOIN textdescription td ON td.idtextdescription = laiv.idtextdescription
JOIN cdtablename ctn on ctn.idcdtablename = lav.idtablename
WHERE lav.idattributedefinition = (SELECT idattributedefinition
FROM attributedefinition ad
JOIN textdescription td ON td.idtextdescription = ad.idtextdescription
WHERE td.textvalue = 'Route number'
AND td.idlanguage = 2)
AND ctn.tablename = 'schedule'
AND lav.idreference = s.idschedule
AND lav.windowstartdate <= s.workingdate
AND lav.windowenddate >= s.workingdate
AND td.idlanguage = @IDLANGUAGE), '') as roadname
, d.IdDriver
, d.FirstName + ' ' + d.LastName as drivername
, d.Phone driverPhoneNumber
, COALESCE(dbu.EmployeeNumber COLLATE SQL_Latin1_General_CP1_CI_AI, d.EmployeeNumber) DriverEmployeeNumberBusinessUnit
, TransportationCompany.Name AS TruckCompanyName
, TransportationCompany.ContactName AS TruckContactName
, TransportationCompany.NIR TruckNIR
, Helper.Value HelperName
, c.DoorNumber
, c.Street
, c.AptUnit
, c.City
, c.PostalCode
, c.IdProvince
, c.Message
, c.idclient
, IsNull(c.driverRemark, '') as driverremark
, cl.FirstName + ' ' + cl.LastName as contact
, cl.PrimaryPhone
, cl.SecondaryPhone
, c.PickupDateTime
, c.TimeWindowPickupStartTime
, c.TimeWindowPickupEndTime
, cast(floor(cast(c.pickupdatetime as float))as datetime) pickupdate
, c.RealPickupDateTime
, c.isautomaticcallallowed
, IsNull(c.idcancelreason, 0) as idcancelreason
, IsNull(c.idzone, 0) as idzone
, IsNull(td5.textvalue, '') as zonename
, t.IdTask
, t.InvoiceNumber
, IsNull(td1.textvalue, '') as TaskStatus
, IsNull(td2.textvalue, '') as TaskSubStatus
, t.IdTaskStatus
, t.IdTaskSubStatus
, IsNull(t.storenumber, '') as storenumber
, i.IdItem
, i.ItemNumber
, i.BarCode
, i.SerialNumber
, i.Quantity
, i.Weight
, i.Volume
, i.Mark AS Brand
, i.Model
, COALESCE(i.Description, tprod.TextValue) Description
, i.Value
, i.ManufacturingYear As GiftCard
, il.DescriptionFr As ItemLocationFr
, il.DescriptionEn As ItemLocationEn
, i.ImportTrackingNumber
, i.TrackingNumber
, IsNull(td3.textvalue, '') as ItemStatus
, IsNull(td4.textvalue, '') as ItemSubStatus
, i.IdItemStatus
, i.IdItemSubStatus
, c.idcompany
, (SELECT COUNT(IdTask) AS Expr1
FROM dbo.TaskStatusHistory
WHERE (IdTask = t.IdTask) AND (IdActor = 1)) AS PDACount
, p.SigleFr AS ProvFr
, p.SigleEn AS ProvEn
, IsNull(td6.textvalue, '') as TaskType
, IsNull(td_tst.textvalue, '') as TaskSubType
, IsNull(td7.textvalue, '') as Company
, oc.IdOutboundCallStatus
, oc.IdOutboundCallAttemptResult
, (Select max(CallStartDateTime)
From dbo.CallAttemptHistory
Where (IdOutboundCall = oc.IdOutboundCall)) AS CallTime
FROM dbo.Call c
JOIN client cl on cl.idclient = c.idclient
JOIN task t on t.idcall = c.idcall
JOIN callstatus cs on c.idcallstatus = cs.idcallstatus
LEFT JOIN TaskStatus ts on ts.IdTaskStatus = t.IdTaskStatus
LEFT JOIN TaskSubStatus tss on tss.IdTaskSubStatus = t.IdTaskSubStatus
LEFT JOIN Item i on i.IdItem = t.IdItem
LEFT JOIN ItemStatus istat on istat.IdItemStatus = i.IdItemStatus
LEFT JOIN ItemSubStatus isubstat on isubstat.IdItemSubStatus = i.IdItemSubStatus
LEFT JOIN driver d on d.iddriver = c.iddriver
LEFT JOIN Schedule sch ON d.IdDriver = sch.IdDriver and sch.WorkingDate = cast(floor(cast(c.pickupdatetime as float))as datetime)
LEFT JOIN TransportationCompany ON sch.IdTransportationCompany = TransportationCompany.IdTransportationCompany
LEFT JOIN DriverBusinessUnit dbu on dbu.IdDriver = c.iddriver and c.IdCompany = dbu.IdBusinessUnit and cast(floor(cast(c.pickupdatetime as float))as datetime) between dbu.EffectiveStartDate and dbu.EffectiveEndDate
LEFT JOIN ItemLocation il on il.IdItemLocation = i.IdItemLocation
LEFT JOIN CancelReason cr on cr.IdCancelReason = c.IdCancelReason
LEFT JOIN zone z ON z.idzone = c.idzone
LEFT JOIN Province p on p.IdProvince = c.IdProvince
LEFT JOIN TaskType tt on tt.IdTaskType = t.IdTaskType
LEFT JOIN TaskSubType tst on tst.IdTaskSubType = t.IdTaskSubType
LEFT JOIN TextAttributeValue Helper ON sch.IdSchedule = Helper.IdReference AND Helper.IdTableName = 7 /* Schedule */ AND (Helper.IdAttributeDefinition = 4000000000000005 /* Helper */) AND cast(floor(cast(c.pickupdatetime as float))as datetime) BETWEEN WindowStartDate and WindowEndDate
LEFT JOIN Company co on co.IdCompany = c.IdCompany
LEFT JOIN OutboundCall oc on oc.IdStop = c.idcall
LEFT JOIN Product prod on prod.IdProduct = t.IdProduct
LEFT JOIN textdescription2 tprod on tprod.IdTextDescription2 = prod.IdTextDescription2 and tprod.IdLanguage = @IDLANGUAGE
LEFT JOIN textdescription td1
ON td1.idtextdescription = ts.idtextdescription
AND td1.idlanguage = @IDLANGUAGE
LEFT JOIN textdescription td2
ON td2.idtextdescription = tss.idtextdescription
AND td2.idlanguage = @IDLANGUAGE
LEFT JOIN textdescription td3
ON td3.idtextdescription = istat.idtextdescription
AND td3.idlanguage = @IDLANGUAGE
LEFT JOIN textdescription td4
ON td4.idtextdescription = isubstat.idtextdescription
AND td4.idlanguage = @IDLANGUAGE
LEFT JOIN textdescription td5
ON td5.idtextdescription = z.idtextdescription
AND td5.idlanguage = @IDLANGUAGE
LEFT JOIN textdescription td6
ON td6.idtextdescription = tt.idtextdescription
AND td6.idlanguage = @IDLANGUAGE
LEFT JOIN textdescription td7
ON td7.idtextdescription = co.idtextdescription
AND td7.idlanguage = @IDLANGUAGE
LEFT JOIN textdescription td_tst
ON td_tst.idtextdescription = tst.idtextdescription
AND td_tst.idlanguage = @IDLANGUAGE
LEFT JOIN schedule s
ON s.iddriver = c.iddriver
AND datediff(day, s.workingdate, c.pickupdatetime) = 0
INNER JOIN CompanyDomain dom2 on dom2.IdCompany = t.IdBusinessUnit
inner join CompanyDomain dom on dom.IdCompany = c.IdCompany WHERE pickupdatetime BETWEEN @PICKUPDATE AND @PICKUPDATEMAXIMUM
Here's the slow executiong plan:
|--Parallelism(Gather Streams)
|--Sort(DISTINCT ORDER BY:([Expr1072] ASC, [cs].[DescriptionFr] ASC, [cs].[DescriptionEn] ASC, [cr].[DescriptionFr] ASC, [cr].[DescriptionEn] ASC, [Expr1087] ASC, [d].[IdDriver] ASC, [Expr1088] ASC, [Expr1089] ASC, [TestTEC_Fred].[dbo].[Transportati
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1072], [cs].[DescriptionFr], [cs].[DescriptionEn], [cr].[DescriptionFr], [cr].[DescriptionEn], [Expr1087], [d].[IdDriver], [Expr1088], [Expr1089], [TestTEC_Fred].[db
|--Compute Scalar(DEFINE:([Expr1087]=isnull([Expr1118],''), [Expr1089]=CASE WHEN [Expr1127] IS NOT NULL THEN CONVERT(varchar(30),[TestTEC_Fred].[dbo].[DriverBusinessUnit].[EmployeeNumber] as [dbu].[EmployeeNumber],0) ELSE [TestTEC_Fred].[d
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([oc].[IdOutboundCall]))
|--Hash Match(Right Outer Join, HASH:([TestTEC_Fred].[dbo].[TaskStatusHistory].[IdTask])=([t].[IdTask]), RESIDUAL:([TestTEC_Fred].[dbo].[TaskStatusHistory].[IdTask]=[TestTEC_Fred].[dbo].[Task].[IdTask] as [t].[IdTask]))
| |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | |--Compute Scalar(DEFINE:([Expr1105]=CONVERT_IMPLICIT(int,[Expr1138],0)))
| | |--Stream Aggregate(GROUP BY:([TestTEC_Fred].[dbo].[TaskStatusHistory].[IdTask]) DEFINE:([Expr1138]=Count(*)))
| | |--Sort(ORDER BY:([TestTEC_Fred].[dbo].[TaskStatusHistory].[IdTask] ASC))
| | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TaskStatusHistory].[PK_TaskStatusHistory]), WHERE:([TestTEC_Fred].[dbo].[TaskStatusHistory].[IdActor]=(1)))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:(.[IdSchedule], .[WorkingDate]))
| |--Hash Match(Right Outer Join, HASH:(.[IdDriver])=([c].[IdDriver]), RESIDUAL:([TestTEC_Fred].[dbo].[Schedule].[IdDriver] as .[IdDriver]=[TestTEC_Fred].[dbo].[Call].[IdDriver] as [c].[IdDriver] AND datediff(day,[T
| | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | |--Index Scan(OBJECT:([TestTEC_Fred].[dbo].[Schedule].[IXU_IdDriver_WorkingDate_Schedule] AS ))
| | |--Compute Scalar(DEFINE:([Expr1109]=isnull([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as [td_tst].[TextValue],'')))
| | |--Hash Match(Right Outer Join, HASH:([td_tst].[IdTextDescription])=([tst].[IdTextDescription]), RESIDUAL:([TestTEC_Fred].[dbo].[TextDescription].[IdTextDescription] as [td_tst].[IdTextDescription]=[TestTEC_Fr
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([td_tst].[IdTextDescription]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS [td_tst]), WHERE:([TestTEC_Fred].[dbo].[TextDescription].[IdLanguage] as [td_tst].[IdLanguage]=[@IDLANGU
| | |--Compute Scalar(DEFINE:([Expr1110]=isnull([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as [td7].[TextValue],'')))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tst].[IdTextDescription]))
| | |--Hash Match(Right Outer Join, HASH:([td7].[IdTextDescription])=([TestTEC_Fred].[dbo].[BusinessUnit].[IdTextDescription]), RESIDUAL:([TestTEC_Fred].[dbo].[TextDescription].[IdTextDescription] a
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([td7].[IdTextDescription]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS [td7]), WHERE:([TestTEC_Fred].[dbo].[TextDescription].[IdLanguage] as [td7].[IdLanguage]=
| | |--Compute Scalar(DEFINE:([Expr1108]=isnull([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as [td6].[TextValue],'')))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([TestTEC_Fred].[dbo].[BusinessUnit].[IdTextDescription]))
| | |--Hash Match(Right Outer Join, HASH:([td6].[IdTextDescription])=([tt].[IdTextDescription]), RESIDUAL:([TestTEC_Fred].[dbo].[TextDescription].[IdTextDescription] as [td6].[IdTextD
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([td6].[IdTextDescription]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS [td6]), WHERE:([TestTEC_Fred].[dbo].[TextDescription].[IdLanguage] as [td6
| | |--Compute Scalar(DEFINE:([Expr1095]=isnull([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as [td5].[TextValue],'')))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tt].[IdTextDescription]))
| | |--Hash Match(Right Outer Join, HASH:([td5].[IdTextDescription])=([z].[IdTextDescription]), RESIDUAL:([TestTEC_Fred].[dbo].[TextDescription].[IdTextDescription] as
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([td5].[IdTextDescription]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS [td5]), WHERE:([TestTEC_Fred].[dbo].[TextDescription].[IdLa
| | |--Compute Scalar(DEFINE:([Expr1101]=isnull([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as [td4].[TextValue],'')))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([z].[IdTextDescription]))
| | |--Hash Match(Right Outer Join, HASH:([td4].[IdTextDescription])=([isubstat].[IdTextDescription]), RESIDUAL:([TestTEC_Fred].[dbo].[TextDescription].[
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([td4].[IdTextDescription]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS [td4]), WHERE:([TestTEC_Fred].[dbo].[TextDes
| | |--Compute Scalar(DEFINE:([Expr1100]=isnull([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as [td3].[TextValue],'')))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([isubstat].[IdTextDescription]))
| | |--Hash Match(Right Outer Join, HASH:([td3].[IdTextDescription])=([istat].[IdTextDescription]), RESIDUAL:([TestTEC_Fred].[dbo].[TextDe
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([td3].[IdTextDescription]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS [td3]), WHERE:([TestTEC_Fred]
| | |--Compute Scalar(DEFINE:([Expr1097]=isnull([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as [td2].[TextValue],'')))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([istat].[IdTextDescription]))
| | |--Hash Match(Right Outer Join, HASH:([td2].[IdTextDescription])=([tss].[IdTextDescription]), RESIDUAL:([TestTEC_Fred].
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([td2].[IdTextDescription]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS [td2]), WHERE:
| | |--Compute Scalar(DEFINE:([Expr1096]=isnull([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as [td1].[TextValue
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([tss].[IdTextDescription]))
| | |--Hash Match(Right Outer Join, HASH:([td1].[IdTextDescription])=([ts].[IdTextDescription]), RESIDUAL:([
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([td1].[IdTextDescription]
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([ts].[IdTextDescription])
| | |--Hash Match(Right Outer Join, HASH:([tprod].[IdTextDescription2])=([prod].[IdTextDescription
| | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextDescription2].[PK__TextDes
| | |--Hash Match(Right Outer Join, HASH:([prod].[IdProduct])=([t].[IdProduct]), RESIDUAL:([T
| | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[Product].[PK__Product__05
| | |--Nested Loops(Left Outer Join, WHERE:([TestTEC_Fred].[dbo].[OutboundCall].[IdStop]
| | |--Hash Match(Right Outer Join, HASH:([TestTEC_Fred].[dbo].[BusinessUnit].[IdBu
| | | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[BusinessUnit].[
| | | |--Hash Match(Right Outer Join, HASH:([Helper].[IdReference])=([sch].[IdSc
| | | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TextAttrib
| | | |--Hash Match(Right Outer Join, HASH:([tst].[IdTaskSubType])=([t].[Id
| | | |--Parallelism(Distribute Streams, Broadcast Partitioning)
| | | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[TaskS
| | | |--Hash Match(Right Outer Join, HASH:([tt].[IdTaskType])=([t]..[dbo].[
| | | |--Hash Match(Right Outer Join, HASH:([p].[IdProvince])=([c
| | | |--Parallelism(Distribute Streams, Broadcast Partition
| | | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[d
| | | |--Hash Match(Right Outer Join, HASH:([z].[IdZone])=([
| | | |--Parallelism(Distribute Streams, Broadcast Part
| | | | |--Clustered Index Scan(OBJECT:([TestTEC_Fre
| | | |--Hash Match(Right Outer Join, HASH:([cr].[IdCan
| | | |--Parallelism(Distribute Streams, Broadcast
| | | | |--Clustered Index Scan(OBJECT:([TestTE
| | | |--Hash Match(Right Outer Join, HASH:([il].[
| | | |--Parallelism(Distribute Streams, Broa
| | | | |--Clustered Index Scan(OBJECT:([T
| | | |--Compute Scalar(DEFINE:([Expr1127]=CO
| | | |--Nested Loops(Left Outer Join, W
| | | |--Hash Match(Right Outer Joi
| | | | |--Parallelism(Distribut
| | | | | |--Nested Loops(Lef
| | | | | |--Clustered I
| | | | | |--Clustered I
| | | | |--Compute Scalar(DEFINE
| | | | |--Hash Match(Right
| | | | |--Parallelism
| | | | | |--Cluste
| | | | |--Hash Match(
| | | | |--Parall
| | | | | |--N
| | | | |
| | | | |
| | | | |
| | | | |
| | | | |--Parall
| | | | |--H
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |
| | | |--Clustered Index Scan(OBJEC
| | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[OutboundCall].[PK_Outboun
| |--Assert(WHERE:(CASE WHEN [Expr1117]>(1) THEN (0) ELSE NULL END))
| |--Stream Aggregate(DEFINE:([Expr1117]=Count(*), [Expr1118]=ANY([TestTEC_Fred].[dbo].[TextDescription].[TextValue] as .[TextValue])))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([laiv].[IdTextDescription]))
| |--Nested Loops(Inner Join, WHERE:([TestTEC_Fred].[dbo].[LookupAttributeValue].[IdAttributeDefinition] as [lav].[IdAttributeDefinition]=[Expr1120]))
| | |--Assert(WHERE:(CASE WHEN [Expr1119]>(1) THEN (0) ELSE NULL END))
| | | |--Stream Aggregate(DEFINE:([Expr1119]=Count(*), [Expr1120]=ANY([TestTEC_Fred].[dbo].[AttributeDefinition].[IdAttributeDefinition] as [ad].[IdAttributeDefinition])))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ad].[IdTextDescription]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[AttributeDefinition].[PK_AttributeDefinition] AS [ad]))
| | | |--Clustered Index Seek(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS ), SEEK:(.[IdTextDescription]=[TestTEC_Fred].[dbo].[AttributeDefinition].[IdTextD
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([lav].[IdTableName]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([lav].[Value]))
| | | |--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[LookupAttributeValue].[PK_LookupAttributeValue] AS [lav]), WHERE:([TestTEC_Fred].[dbo].[LookupAttributeValue].[IdReference] as [lav]..[dbo].[LookupAttributeItemValue].[PK_LookupAttributeItemValue] AS [laiv]), SEEK:([laiv].[IdLookupAttributeItemValue]=[TestTEC_Fred].[dbo].[Loo
| | |--Clustered Index Seek(OBJECT:([TestTEC_Fred].[dbo].[CDTableName].[PK_CDTableName] AS [ctn]), SEEK:([ctn].[IdCdTableName]=[TestTEC_Fred].[dbo].[LookupAttributeValue].[IdTableName] as [lav].[IdT
| |--Clustered Index Seek(OBJECT:([TestTEC_Fred].[dbo].[TextDescription].[PK_TextDescription] AS ), SEEK:(.[IdTextDescription]=[TestTEC_Fred].[dbo].[LookupAttributeItemValue].[IdTextDescription] as
|--Stream Aggregate(DEFINE:([Expr1114]=MAX([TestTEC_Fred].[dbo].[CallAttemptHistory].[CallStartDateTime])))
|--Clustered Index Scan(OBJECT:([TestTEC_Fred].[dbo].[CallAttemptHistory].[PK_CallAttemptHistory]), WHERE:([TestTEC_Fred].[dbo].[CallAttemptHistory].[IdOutboundCall]=[TestTEC_Fred].[dbo].[OutboundCall].[IdOutboundCall] as [o
Here's the fast execution plan:
|--Sort(DISTINCT ORDER BY:([Expr1072] ASC, [cs].[DescriptionFr] ASC, [cs].[DescriptionEn] ASC, [cr].[DescriptionFr] ASC, [cr].[DescriptionEn] ASC, [Expr1087] ASC, [d].[IdDriver] ASC, [Expr1088] ASC, [Expr1089] ASC, [TestSGL].[dbo].[TransportationCompany]
|--Compute Scalar(DEFINE:([Expr1087]=isnull([Expr1118],''), [Expr1089]=CASE WHEN [Expr1127] IS NOT NULL THEN CONVERT(varchar(30),[TestSGL].[dbo].[DriverBusinessUnit].[EmployeeNumber] as [dbu].[EmployeeNumber],0) ELSE [TestSGL].[dbo].[Driver].[Employ
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([oc].[IdOutboundCall]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([t].[IdTask]))
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:(.[IdSchedule], .[WorkingDate]))
| | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([c].[PickupDateTime], [c].[IdDriver]))
| | | |--Compute Scalar(DEFINE:([Expr1109]=isnull([TestSGL].[dbo].[TextDescription].[TextValue] as [td_tst].[TextValue],'')))
| | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([tst].[IdTextDescription]))
| | | | |--Compute Scalar(DEFINE:([Expr1110]=isnull([TestSGL].[dbo].[TextDescription].[TextValue] as [td7].[TextValue],'')))
| | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TestSGL].[dbo].[BusinessUnit].[IdTextDescription]))
| | | | | |--Compute Scalar(DEFINE:([Expr1108]=isnull([TestSGL].[dbo].[TextDescription].[TextValue] as [td6].[TextValue],'')))
| | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([tt].[IdTextDescription]))
| | | | | | |--Compute Scalar(DEFINE:([Expr1095]=isnull([TestSGL].[dbo].[TextDescription].[TextValue] as [td5].[TextValue],'')))
| | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([z].[IdTextDescription]))
| | | | | | | |--Compute Scalar(DEFINE:([Expr1101]=isnull([TestSGL].[dbo].[TextDescription].[TextValue] as [td4].[TextValue],'')))
| | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([isubstat].[IdTextDescription]))
| | | | | | | | |--Compute Scalar(DEFINE:([Expr1100]=isnull([TestSGL].[dbo].[TextDescription].[TextValue] as [td3].[TextValue],'')))
| | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([istat].[IdTextDescription]))
| | | | | | | | | |--Compute Scalar(DEFINE:([Expr1097]=isnull([TestSGL].[dbo].[TextDescription].[TextValue] as [td2].[TextValue],'')))
| | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([tss].[IdTextDescription]))
| | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1096]=isnull([TestSGL].[dbo].[TextDescription].[TextValue] as [td1].[TextValue],'')))
| | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([ts].[IdTextDescription]))
| | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([prod].[IdTextDescription2]))
| | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[Product].[IdProduct] as [prod].[IdProduct]=[TestSGL].[dbo].[Task].[IdProduct] as [
| | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[OutboundCall].[IdStop] as [oc].[IdStop]=[TestSGL].[dbo].[Call].[IdCall] as [c
| | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[BusinessUnit].[IdBusinessUnit]=[TestSGL].[dbo].[Call].[IdCompany] as [c]
| | | | | | | | | | | | | | | |--Nested Loops(Inner Join, PASSTHRU:([IsBaseRow1040] IS NULL), OUTER REFERENCES:([Helper].[IdTextAttributeValue]))
| | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([sch].[IdSchedule], [Expr1092], [Expr1134]) WITH UNORDERED PREFETCH)
| | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[TaskSubType].[IdTaskSubType] as [tst].[IdTaskSubType]=[Te
| | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[TaskType].[IdTaskType] as [tt].[IdTaskType]=[TestSGL
| | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[Province].[IdProvince] as [p].[IdProvince]=[Tes
| | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[Zone].[IdZone] as [z].[IdZone]=[TestSGL].[
| | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[CancelReason].[IdCancelReason] as [cr
| | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[ItemLocation].[IdItemLocation] a
| | | | | | | | | | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1127]=CONVERT(varchar(30),[TestSGL].[dbo].[DriverBusi
| | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[DriverBusinessUnit]..[dbo].[Schedule].[IdTran
| | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[Driver].[IdD
| | | | | | | | | | | | | | | | | | | | | | | | | | |--Compute Scalar(DEFINE:([Expr1088]=([TestSGL].[dbo].[Driver].[
| | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo].[Dr
| | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL].[dbo
| | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:([TestSGL]
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, OUTER REFER
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, WHERE:
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Left Outer Join, W
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Inner Join, O
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Inner Jo
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested Loops(Inn
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Compute Sca
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Nested
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--H
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--C
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered I
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Seek(
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJEC
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([T
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([TestSG
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[d
| | | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[
| | | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[Drive
| | | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[Schedule].[PK_S
| | | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[TransportationCompan
| | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[DriverBusinessUnit].[PK__
| | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[ItemLocation].[PK_ItemLocation] AS
| | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[CancelReason].[PK_CancelReason] AS [cr])
| | | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[Zone].[PK_Zone] AS [z]))
| | | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[Province].[PK_Province] AS [p]))
| | | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[TaskType].[PK_TaskType] AS [tt]))
| | | | | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[TaskSubType].[PK_TaskSubType] AS [tst]))
| | | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([TestSGL].[dbo].[TextAttributeValue].[UK_TextAttributeValue_Identity] AS [Helper]), SEEK:([H
| | | | | | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextAttributeValue].[PK_TextAttributeValue] AS [Helper]), SEEK:([Helpe
| | | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[BusinessUnit].[PK__BusinessUnit__650E807E]))
| | | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[OutboundCall].[PK_OutboundCall] AS [oc]))
| | | | | | | | | | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[Product].[PK__Product__057B5010] AS [prod]))
| | | | | | | | | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription2].[PK__TextDescription2__58A8A999] AS [tprod]), SEEK:([tprod].[IdTextDescr
| | | | | | | | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS [td1]), SEEK:([td1].[IdTextDescription]=[TestSGL].[dbo
| | | | | | | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS [td2]), SEEK:([td2].[IdTextDescription]=[TestSGL].[dbo].[TaskSub
| | | | | | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS [td3]), SEEK:([td3].[IdTextDescription]=[TestSGL].[dbo].[ItemStatus].[IdTe
| | | | | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS [td4]), SEEK:([td4].[IdTextDescription]=[TestSGL].[dbo].[ItemSubStatus].[IdTextDescr
| | | | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS [td5]), SEEK:([td5].[IdTextDescription]=[TestSGL].[dbo].[Zone].[IdTextDescription] as [z].[IdT
| | | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS [td6]), SEEK:([td6].[IdTextDescription]=[TestSGL].[dbo].[TaskType].[IdTextDescription] as [tt].[IdTextDe
| | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS [td7]), SEEK:([td7].[IdTextDescription]=[TestSGL].[dbo].[BusinessUnit].[IdTextDescription] AND [td7].[IdLanguage]=
| | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS [td_tst]), SEEK:([td_tst].[IdTextDescription]=[TestSGL].[dbo].[TaskSubType].[IdTextDescription] as [tst].[IdTextDescription]
| | | |--Index Seek(OBJECT:([TestSGL].[dbo].[Schedule].[IXU_IdDriver_WorkingDate_Schedule] AS ), SEEK:(.[IdDriver]=[TestSGL].[dbo].[Call].[IdDriver] as [c].[IdDriver]), WHERE:(datediff(day,[TestSGL].[dbo].[Schedule].[Workin
| | |--Assert(WHERE:(CASE WHEN [Expr1117]>(1) THEN (0) ELSE NULL END))
| | |--Stream Aggregate(DEFINE:([Expr1117]=Count(*), [Expr1118]=ANY([TestSGL].[dbo].[TextDescription].[TextValue] as .[TextValue])))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([laiv].[IdTextDescription]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([lav].[Value]))
| | | |--Nested Loops(Inner Join, WHERE:([TestSGL].[dbo].[LookupAttributeValue].[IdAttributeDefinition] as [lav].[IdAttributeDefinition]=[Expr1120]))
| | | | |--Assert(WHERE:(CASE WHEN [Expr1119]>(1) THEN (0) ELSE NULL END))
| | | | | |--Stream Aggregate(DEFINE:([Expr1119]=Count(*), [Expr1120]=ANY([TestSGL].[dbo].[AttributeDefinition].[IdAttributeDefinition] as [ad].[IdAttributeDefinition])))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([ad].[IdTextDescription]))
| | | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[AttributeDefinition].[PK_AttributeDefinition] AS [ad]))
| | | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS ), SEEK:(.[IdTextDescription]=[TestSGL].[dbo].[AttributeDefinition].[IdTextDescription] as
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([lav].[IdTableName]))
| | | | |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[LookupAttributeValue].[PK_LookupAttributeValue] AS [lav]), WHERE:([TestSGL].[dbo].[LookupAttributeValue].[IdReference] as [lav].[IdReference]=[TestSGL
| | | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[CDTableName].[PK_CDTableName] AS [ctn]), SEEK:([ctn].[IdCdTableName]=[TestSGL].[dbo].[LookupAttributeValue].[IdTableName] as [lav].[IdTableName]), WH
| | | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[LookupAttributeItemValue].[PK_LookupAttributeItemValue] AS [laiv]), SEEK:([laiv].[IdLookupAttributeItemValue]=[TestSGL].[dbo].[LookupAttributeValue].[Value] as
| | |--Clustered Index Seek(OBJECT:([TestSGL].[dbo].[TextDescription].[PK_TextDescription] AS ), SEEK:(.[IdTextDescription]=[TestSGL].[dbo].[LookupAttributeItemValue].[IdTextDescription] as [laiv].[IdTextDescri
| |--Compute Scalar(DEFINE:([Expr1105]=CONVERT_IMPLICIT(int,[Expr1135],0)))
| |--Stream Aggregate(DEFINE:([Expr1135]=Count(*)))
| |--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[TaskStatusHistory].[PK_TaskStatusHistory]), WHERE:([TestSGL].[dbo].[TaskStatusHistory].[IdTask]=[TestSGL].[dbo].[Task].[IdTask] as [t].[IdTask] AND [TestSGL].[dbo].[TaskStatus
|--Stream Aggregate(DEFINE:([Expr1114]=MAX([TestSGL].[dbo].[CallAttemptHistory].[CallStartDateTime])))
|--Clustered Index Scan(OBJECT:([TestSGL].[dbo].[CallAttemptHistory].[PK_CallAttemptHistory]), WHERE:([TestSGL].[dbo].[CallAttemptHistory].[IdOutboundCall]=[TestSGL].[dbo].[OutboundCall].[IdOutboundCall] as [oc].[IdOutboundCall]))
If you have an idea let me know!
Thank you for your time.
November 12, 2009 at 8:22 am
Yes, SQL Server does base it's execution plans on the data, not the schema. SQL Server keeps statistics for the distribution of data in the tables and uses the statistics to determine what would be the best path to access the data. If the statistics are not up to date then the optimizer can't pick the best plan.
I suggest updating the statistics on the slower database and make sure that autoupdate statistics is enabled.
To be honest I find the graphical execution plans easier to read, especially in a complex query like the one you have posted. If you could attach the graphical execution plans to the thread I think you would get better answers. See this video on how to attach the graphical execution plans.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 12, 2009 at 9:00 am
Hi Jack,
thank you for you reply. I attached the two execution plans in graphical format.
I updated the statistics before getting the plan of the slow database but it changed nothing.
November 12, 2009 at 9:23 am
I should have noticed this at first look. The slow plan has created a plan using parallelism which actually could be the source of the problem. In a DEV environment, hopefully you have one, try setting OPTION (MAXDOP 1) which will keep the optimizer from using a plan using parallelism.
There are also a few other things you can do to potentially improve the performance of the query. Each of these is meant to be suggested in a constructive way.
1. Convert your correlated subqueries to derived tables. Those would be the selects within the outer SELECT clause. correlated subqueries are usually performance killers as they have to be processed for each row returned by the outer query. Derived tables are run once.
2. Find a way to get rid of this cast(floor(cast(c.pickupdatetime as float)) as datetime). Anytime you use functions in a JOIN or WHERE clause you are forcing an scan. Is that column a datetime already? If so why are you converting to float and then back to datetime? There has to be a better way.
3. Are you sure you need the DISTINCT? Do you get duplicate results when you remove? This causes a sort and sorts are expensive.
4. Just for readability purposes, I'd suggest moving the 2 INNER JOIN's at the end of your FROM up with the other INNER JOIN's.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 12, 2009 at 9:38 am
Hi Jack,
thanks for your suggestions. I am currently implementing them.
This is a query that someone else designed some months ago and I was assigned the task to make it run faster.
I'll let you know on how things go.
November 12, 2009 at 6:20 pm
Once you eliminate the date cast(floor(... stuff and the subqueries, as a data integrity note - you should verify that the dates being picked up in your between clauses is precisely what your are looking to get.
Here is an Article outlining some of the gotchas.
Jack has made some good suggestions. If you can, try to get those subqueries out of the query and see if you can do a straight join. If not, use the derived tables as Jack suggested.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 13, 2009 at 7:42 am
Thank you CirquedeSQLeil,
The article you gave me is exactly what I was looking for. The guy who wrote the query with the 'cast(...' was trying to strip the time part of the date. It works but it seems not optimal at all. I'll need to learn more about derived tables before getting this query optimized as I am no SQL guru [yet].
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply