September 15, 2009 at 10:36 pm
Hi,
I have been given a procedure to tune as its running slow in production when there are more than 50 concurrent users.
We are using SQL SERVER 2005. Are the LEFT JOINs creating the problem or can I re-write this query.
USE [Cash_Mgmt]
GO
/****** Object: StoredProcedure [dbo].[USp_CallEecution_GetRouteExecDetails] Script Date: 09/16/2009 09:57:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USp_CallEecution_GetRouteExecDetails]
@ClActCd varchar(1)=NULL,
@ClCallNo numeric(9)=NULL,
@ClCustCd varchar(6)=NULL,
@ClCustBrCd varchar(6)=NULL,
@ClOffCd varchar(6)=NULL,
@CompCode varchar(6)=NULL,
@SQLDateFormat INT = NULL,
@ClGenDate varchar(15)=NULL,
@ClNature varchar(1)=NULL,
@ClCustCustCd varchar(12)=NULL
AS
Select CL.RTCODE [Route Code],MR.RtName [Route Name],
CASE MR.TMode
WHEN 'V' THEN 'Van' WHEN 'R' THEN 'Bike Rider' WHEN 'B' THEN 'On Body' END [Mode],
MO1.Memp_Fname + ' ' + MO1.Memp_Lname + ' - ' + MO1.Memp_RimNo [Custodian 1],
MO2.Memp_Fname + ' ' + MO2.Memp_Lname + ' - ' + MO2.Memp_RimNo [Custodian 2],
MO3.Memp_Fname + ' ' + MO3.Memp_Lname + ' - ' + MO3.Memp_RimNo [Custodian 3],
MO4.Memp_Fname + ' ' + MO4.Memp_Lname + ' - ' + MO4.Memp_RimNo [Driver],
MO5.Memp_Fname + ' ' + MO5.Memp_Lname + ' - ' + MO5.Memp_RimNo [Loader],
MO6.Memp_Fname + ' ' + MO6.Memp_Lname + ' - ' + MO6.Memp_RimNo [Gun Man 1],
MO7.Memp_Fname + ' ' + MO7.Memp_Lname + ' - ' + MO7.Memp_RimNo [Gun Man 2],
CL.CUST1 [Cust1 Code], CL.CUST2 [Cust2 Code] ,
CL.CUST3 [Cust3 Code], CL.DRIVER [Driver Code],CL.LOADER [Loader Code] ,CL.GUNMAN [GunMan Code],
CL.SUPERVISOR [Supervisor Code],
CL.VANCODE [Van Code]
from CLEXEC CL
LEFT OUTER JOIN MROUTE MR ON MR.RTCODE = CL.RTCODE AND MR.OFFCODE = CL.CLOFFCD AND MR.COMPCODE=CL.COMPCODE
LEFT OUTER JOIN MEMPOFF MO1 ON MO1.Memp_code = CL.CUST1 AND MO1.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO2 ON MO2.Memp_code = CL.CUST2 AND MO2.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO3 ON MO3.Memp_code = CL.CUST3 AND MO3.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO4 ON MO4.Memp_code = CL.DRIVER AND MO4.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO5 ON MO5.Memp_code = CL.Loader AND MO5.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO6 ON MO6.Memp_code = CL.Gunman AND MO6.Memp_CompCd=CL.CompCode
LEFT OUTER JOIN MEMPOFF MO7 ON MO7.Memp_code = CL.Supervisor AND MO7.Memp_CompCd=CL.CompCode
WHERE CL.ClActCd=@ClActCd AND CL.ClCallNo=@ClCallNo AND Cl.ClCustCd=@ClCustCd AND Cl.ClCustBrCd=@ClCustBrCd AND
Cl.ClOffCd=@ClOffCd
AND Cl.CompCode=@CompCode AND Cl.ClGenDate=CONVERT(DATETIME,@ClGenDate,@SQLDateFormat)
AND Cl.ClNature=@ClNature AND (Cl.ClCustCustCd=@ClCustCustCd OR Cl.ClCustCustCd IS NULL)
Please advice on this.
Regards,
Soni
The
September 16, 2009 at 6:59 am
Can you post an actual execution plan? And if you really want help rewriting the query, then sample structure and sample data should be supplied as well. Read the link at the bottom of my signature for a lot more details.
"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
September 16, 2009 at 11:41 pm
Hi,
PLease find the execution plan attached.
Select CL.RTCODE [Route Code],MR.RtName [Route Name], CASE MR.TMode WHEN 'V' THEN 'Van' WHEN 'R' THEN 'Bike Rider' WHEN 'B' THEN 'On Body' END [Mode], MO1.Memp_Fname + ' ' + MO1.Memp_Lname + ' - ' + MO1.Memp_RimNo [Custodian 1], MO2.Memp_Fname + ' ' + MO2.Memp_Lname + ' - ' + MO2.Memp_RimNo [Custodian 2], MO3.Memp_Fname + ' ' + MO3.Memp_Lname + ' - ' + MO3.Memp_RimNo [Custodian 3], MO4.Memp_Fname + ' ' + MO4.Memp_Lname + ' - ' + MO4.Memp_RimNo [Driver], MO5.Memp_Fname + ' ' + MO5.Memp_Lname + ' - ' + MO5.Memp_RimNo [Loader], MO6.Memp_Fname + ' ' + MO6.Memp_Lname + ' - ' + MO6.Memp_RimNo [Gun Man 1], MO7.Memp_Fname + ' ' + MO7.Memp_Lname + ' - ' + MO7.Memp_RimNo [Gun Man 2], CL.CUST1 [Cust1 Code], CL.CUST2 [Cust2 Code] , CL.CUST3 [Cust3 Code], CL.DRIVER [Driver Code],CL.LOADER [Loader Code] ,CL.GUNMAN [GunMan Code], CL.SUPERVISOR [Supervisor Code], CL.VANCODE [Van Code] from CLEXEC CL LEFT OUTER JOIN MROUTE MR ON MR.RTCODE = CL.RTCODE AND MR.OFFCODE = CL.CLOFFCD AND MR.COMPCODE=CL.COMPCODE LEFT OUTER JOIN MEMPOFF MO1 ON MO1.Memp_code = CL.CUST1 AND MO1.Memp_CompCd=CL.CompCode LEFT OUTER JOIN MEMPOFF MO2 ON MO2.Memp_code = CL.CUST2 AND MO2.Memp_CompCd=CL.CompCode LEFT OUTER JOIN MEMPOFF MO3 ON MO3.Memp_code = CL.CUST3 AND MO3.Memp_CompCd=CL.CompCode LEFT OUTER JOIN MEMPOFF MO4 ON MO4.Memp_code = CL.DRIVER AND MO4.Memp_CompCd=CL.CompCode LEFT OUTER JOIN MEMPOFF MO5 ON MO5.Memp_code = CL.Loader AND MO5.Memp_CompCd=CL.CompCode LEFT OUTER JOIN MEMPOFF MO6 ON MO6.Memp_code = CL.Gunman AND MO6.Memp_CompCd=CL.CompCode LEFT OUTER JOIN MEMPOFF MO7 ON MO7.Memp_code = CL.Supervisor AND MO7.Memp_CompCd=CL.CompCode WHERE CL.ClActCd='I' AND CL.ClCallNo='14' AND Cl.ClCustCd='A00003' AND Cl.ClCustBrCd='ABC022' AND Cl.ClOffCd='022' AND Cl.CompCode='CSL' AND Cl.ClGenDate=CONVERT(DATETIME,'04/09/2009',convert(int,103)) AND Cl.ClNature='B' AND (Cl.ClCustCustCd='ABC022000017' OR Cl.ClCustCustCd IS NULL)110NULLNULL1NULL1NULLNULLNULL1.303604NULLNULLSELECT0NULL
|--Compute Scalar(DEFINE:([Expr1026]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO7].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO7].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO7].[Memp_RimNo]))121Compute ScalarCompute ScalarDEFINE:([Expr1026]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO7].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO7].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO7].[Memp_RimNo])[Expr1026]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO7].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO7].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO7].[Memp_RimNo]101E-074581.303604[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1024], [Expr1025], [Expr1026]NULLPLAN_ROW01
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([CL].[Supervisor]))132Nested LoopsLeft Outer JoinOUTER REFERENCES:([CL].[Supervisor])NULL104.18E-064591.303604[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [MO7].[Memp_Fname], [MO7].[Memp_Lname], [MO7].[Memp_RimNo], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1024], [Expr1025]NULLPLAN_ROW01
|--Compute Scalar(DEFINE:([Expr1025]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO6].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO6].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO6].[Memp_RimNo]))143Compute ScalarCompute ScalarDEFINE:([Expr1025]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO6].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO6].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO6].[Memp_RimNo])[Expr1025]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO6].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO6].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO6].[Memp_RimNo]101E-074141.118314[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1024], [Expr1025]NULLPLAN_ROW01
| |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CL].[Gunman]))154Nested LoopsLeft Outer JoinOUTER REFERENCES:([CL].[Gunman])NULL104.18E-064151.118314[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [MO6].[Memp_Fname], [MO6].[Memp_Lname], [MO6].[Memp_RimNo], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1024]NULLPLAN_ROW01
| |--Compute Scalar(DEFINE:([Expr1024]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO5].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO5].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO5].[Memp_RimNo]))165Compute ScalarCompute ScalarDEFINE:([Expr1024]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO5].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO5].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO5].[Memp_RimNo])[Expr1024]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO5].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO5].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO5].[Memp_RimNo]101E-073690.9330235[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1024]NULLPLAN_ROW01
| | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CL].[Loader]))176Nested LoopsLeft Outer JoinOUTER REFERENCES:([CL].[Loader])NULL104.18E-063710.9330234[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [MO5].[Memp_Fname], [MO5].[Memp_Lname], [MO5].[Memp_RimNo], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023]NULLPLAN_ROW01
| | |--Compute Scalar(DEFINE:([Expr1023]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO4].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO4].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO4].[Memp_RimNo]))187Compute ScalarCompute ScalarDEFINE:([Expr1023]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO4].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO4].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO4].[Memp_RimNo])[Expr1023]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO4].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO4].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO4].[Memp_RimNo]101E-073250.7477331[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023]NULLPLAN_ROW01
| | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CL].[Driver]))198Nested LoopsLeft Outer JoinOUTER REFERENCES:([CL].[Driver])NULL104.18E-063270.7477331[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [MO4].[Memp_Fname], [MO4].[Memp_Lname], [MO4].[Memp_RimNo], [Expr1019], [Expr1020], [Expr1021], [Expr1022]NULLPLAN_ROW01
| | | |--Compute Scalar(DEFINE:([Expr1022]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO3].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO3].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO3].[Memp_RimNo]))1109Compute ScalarCompute ScalarDEFINE:([Expr1022]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO3].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO3].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO3].[Memp_RimNo])[Expr1022]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO3].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO3].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO3].[Memp_RimNo]101E-072810.5624428[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [Expr1019], [Expr1020], [Expr1021], [Expr1022]NULLPLAN_ROW01
| | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CL].[Cust3]))11110Nested LoopsLeft Outer JoinOUTER REFERENCES:([CL].[Cust3])NULL104.18E-062820.5624427[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [MO3].[Memp_Fname], [MO3].[Memp_Lname], [MO3].[Memp_RimNo], [Expr1019], [Expr1020], [Expr1021]NULLPLAN_ROW01
| | | | |--Compute Scalar(DEFINE:([Expr1021]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO2].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO2].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO2].[Memp_RimNo]))11211Compute ScalarCompute ScalarDEFINE:([Expr1021]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO2].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO2].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO2].[Memp_RimNo])[Expr1021]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO2].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO2].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO2].[Memp_RimNo]101E-072370.3771524[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [Expr1019], [Expr1020], [Expr1021]NULLPLAN_ROW01
| | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CL].[Cust2]))11312Nested LoopsLeft Outer JoinOUTER REFERENCES:([CL].[Cust2])NULL104.18E-062380.3771524[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [MO2].[Memp_Fname], [MO2].[Memp_Lname], [MO2].[Memp_RimNo], [Expr1019], [Expr1020]NULLPLAN_ROW01
| | | | | |--Compute Scalar(DEFINE:([Expr1020]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO1].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO1].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO1].[Memp_RimNo]))11413Compute ScalarCompute ScalarDEFINE:([Expr1020]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO1].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO1].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO1].[Memp_RimNo])[Expr1020]=((([Cash_Mgmt].[dbo].[MEmpOff].[Memp_Fname] as [MO1].[Memp_Fname]+' ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_Lname] as [MO1].[Memp_Lname])+' - ')+[Cash_Mgmt].[dbo].[MEmpOff].[Memp_RimNo] as [MO1].[Memp_RimNo]101E-071930.1918621[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [Expr1019], [Expr1020]NULLPLAN_ROW01
| | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CL].[Cust1]))11514Nested LoopsLeft Outer JoinOUTER REFERENCES:([CL].[Cust1])NULL104.18E-061940.191862[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [MO1].[Memp_Fname], [MO1].[Memp_Lname], [MO1].[Memp_RimNo], [Expr1019]NULLPLAN_ROW01
| | | | | | |--Compute Scalar(DEFINE:([Expr1019]=CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='V' THEN 'Van' ELSE CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='R' THEN 'Bike Rider' ELSE CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='B' THEN 'On Body' ELSE NULL END END END))11615Compute ScalarCompute ScalarDEFINE:([Expr1019]=CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='V' THEN 'Van' ELSE CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='R' THEN 'Bike Rider' ELSE CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='B' THEN 'On Body' ELSE NULL END END END)[Expr1019]=CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='V' THEN 'Van' ELSE CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='R' THEN 'Bike Rider' ELSE CASE WHEN [Cash_Mgmt].[dbo].[MROUTE].[TMode] as [MR].[TMode]='B' THEN 'On Body' ELSE NULL END END END101E-071490.00657176[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [Expr1019]NULLPLAN_ROW01
| | | | | | | |--Nested Loops(Left Outer Join, OUTER REFERENCES:([CL].[RtCode]))11716Nested LoopsLeft Outer JoinOUTER REFERENCES:([CL].[RtCode])NULL104.18E-061440.00657166[CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [MR].[RtName], [MR].[TMode]NULLPLAN_ROW01
| | | | | | | |--Clustered Index Seek(OBJECT:([Cash_Mgmt].[dbo].[ClExec].[PK_ClExec] AS [CL]), SEEK:([CL].[ClActCd]='I' AND [CL].[ClCallNo]=(14.) AND [CL].[ClCustCd]='A00003' AND [CL].[ClCustBrCd]='ABC022' AND [CL].[ClOffCd]='022' AND [CL].[CompCode]='CSL' AND [CL].[ClGenDate]='2009-09-04 00:00:00.000'), WHERE:([Cash_Mgmt].[dbo].[ClExec].[ClNature] as [CL].[ClNature]='B' AND ([Cash_Mgmt].[dbo].[ClExec].[ClCustCustCd] as [CL].[ClCustCustCd] IS NULL OR [Cash_Mgmt].[dbo].[ClExec].[ClCustCustCd] as [CL].[ClCustCustCd]='ABC022000017')) ORDERED FORWARD PARTITION ID:((1)))11817Clustered Index SeekClustered Index SeekOBJECT:([Cash_Mgmt].[dbo].[ClExec].[PK_ClExec] AS [CL]), SEEK:([CL].[ClActCd]='I' AND [CL].[ClCallNo]=(14.) AND [CL].[ClCustCd]='A00003' AND [CL].[ClCustBrCd]='ABC022' AND [CL].[ClOffCd]='022' AND [CL].[CompCode]='CSL' AND [CL].[ClGenDate]='2009-09-04 00:00:00.000'), WHERE:([Cash_Mgmt].[dbo].[ClExec].[ClNature] as [CL].[ClNature]='B' AND ([Cash_Mgmt].[dbo].[ClExec].[ClCustCustCd] as [CL].[ClCustCustCd] IS NULL OR [Cash_Mgmt].[dbo].[ClExec].[ClCustCustCd] as [CL].[ClCustCustCd]='ABC022000017')) ORDERED FORWARD PARTITION ID:((1))[CL].[ClNature], [CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [CL].[ClCustCustCd]10.0031250.00015811390.0032831[CL].[ClNature], [CL].[Driver], [CL].[Gunman], [CL].[Cust1], [CL].[Cust2], [CL].[Cust3], [CL].[Loader], [CL].[Supervisor], [CL].[RtCode], [CL].[VanCode], [CL].[ClCustCustCd]NULLPLAN_ROW01
| | | | | | | |--Index Seek(OBJECT:([Cash_Mgmt].[dbo].[MROUTE].[inx_nc_mroute] AS [MR]), SEEK:([MR].[OffCode]='022' AND [MR].[CompCode]='CSL' AND [MR].[RtCode]=[Cash_Mgmt].[dbo].[ClExec].[RtCode] as [CL].[RtCode]) ORDERED FORWARD)11917Index SeekIndex SeekOBJECT:([Cash_Mgmt].[dbo].[MROUTE].[inx_nc_mroute] AS [MR]), SEEK:([MR].[OffCode]='022' AND [MR].[CompCode]='CSL' AND [MR].[RtCode]=[Cash_Mgmt].[dbo].[ClExec].[RtCode] as [CL].[RtCode]) ORDERED FORWARD[MR].[RtName], [MR].[TMode]10.0031250.0001581300.0032831[MR].[RtName], [MR].[TMode]NULLPLAN_ROW01
| | | | | | |--Clustered Index Scan(OBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO1]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO1].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO1].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Cust1] as [CL].[Cust1]))12315Clustered Index ScanClustered Index ScanOBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO1]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO1].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO1].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Cust1] as [CL].[Cust1])[MO1].[Memp_Fname], [MO1].[Memp_Lname], [MO1].[Memp_RimNo]10.16831020.0090516720.1773618[MO1].[Memp_Fname], [MO1].[Memp_Lname], [MO1].[Memp_RimNo]NULLPLAN_ROW01
| | | | | |--Clustered Index Scan(OBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO2]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO2].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO2].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Cust2] as [CL].[Cust2]))12713Clustered Index ScanClustered Index ScanOBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO2]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO2].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO2].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Cust2] as [CL].[Cust2])[MO2].[Memp_Fname], [MO2].[Memp_Lname], [MO2].[Memp_RimNo]10.16838870.0089731720.1773618[MO2].[Memp_Fname], [MO2].[Memp_Lname], [MO2].[Memp_RimNo]NULLPLAN_ROW01
| | | | |--Clustered Index Scan(OBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO3]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO3].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO3].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Cust3] as [CL].[Cust3]))13111Clustered Index ScanClustered Index ScanOBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO3]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO3].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO3].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Cust3] as [CL].[Cust3])[MO3].[Memp_Fname], [MO3].[Memp_Lname], [MO3].[Memp_RimNo]10.16838870.0089731720.1773618[MO3].[Memp_Fname], [MO3].[Memp_Lname], [MO3].[Memp_RimNo]NULLPLAN_ROW01
| | | |--Clustered Index Scan(OBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO4]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO4].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO4].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Driver] as [CL].[Driver]))1359Clustered Index ScanClustered Index ScanOBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO4]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO4].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO4].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Driver] as [CL].[Driver])[MO4].[Memp_Fname], [MO4].[Memp_Lname], [MO4].[Memp_RimNo]10.16838870.0089731720.1773618[MO4].[Memp_Fname], [MO4].[Memp_Lname], [MO4].[Memp_RimNo]NULLPLAN_ROW01
| | |--Clustered Index Scan(OBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO5]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO5].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO5].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Loader] as [CL].[Loader]))1397Clustered Index ScanClustered Index ScanOBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO5]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO5].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO5].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Loader] as [CL].[Loader])[MO5].[Memp_Fname], [MO5].[Memp_Lname], [MO5].[Memp_RimNo]10.16838870.0089731720.1773618[MO5].[Memp_Fname], [MO5].[Memp_Lname], [MO5].[Memp_RimNo]NULLPLAN_ROW01
| |--Clustered Index Scan(OBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO6]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO6].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO6].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Gunman] as [CL].[Gunman]))1435Clustered Index ScanClustered Index ScanOBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO6]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO6].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO6].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Gunman] as [CL].[Gunman])[MO6].[Memp_Fname], [MO6].[Memp_Lname], [MO6].[Memp_RimNo]10.16838870.0089731720.1773618[MO6].[Memp_Fname], [MO6].[Memp_Lname], [MO6].[Memp_RimNo]NULLPLAN_ROW01
|--Clustered Index Scan(OBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO7]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO7].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO7].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Supervisor] as [CL].[Supervisor]))1473Clustered Index ScanClustered Index ScanOBJECT:([Cash_Mgmt].[dbo].[MEmpOff].[PK_MEmpOff17] AS [MO7]), WHERE:([Cash_Mgmt].[dbo].[MEmpOff].[Memp_CompCd] as [MO7].[Memp_CompCd]='CSL' AND CONVERT_IMPLICIT(nvarchar(12),[Cash_Mgmt].[dbo].[MEmpOff].[Memp_code] as [MO7].[Memp_code],0)=[Cash_Mgmt].[dbo].[ClExec].[Supervisor] as [CL].[Supervisor])[MO7].[Memp_Fname], [MO7].[Memp_Lname], [MO7].[Memp_RimNo]10.16838870.0089731720.1773618[MO7].[Memp_Fname], [MO7].[Memp_Lname], [MO7].[Memp_RimNo]NULLPLAN_ROW01
September 17, 2009 at 7:51 am
Try using the sentense "with (nolock)" maybe this help the fast execution for the query...
September 17, 2009 at 8:05 am
khade (9/17/2009)
Try using the sentense "with (nolock)" maybe this help the fast execution for the query...
This is not a good idea as it probably wont make the query any faster and you could end with 'dirty' reads
September 17, 2009 at 9:38 am
1) maybe it is slow because there are a kajillion joins? 🙂 This can lead to a) excessive IO (ESPECIALLY if indexes aren't available/used) and b) blocking. Did you check for either?
2) Cl.ClGenDate=CONVERT(DATETIME,@ClGenDate,@SQLDateFormat): why are you formatting a date here? is CL.CLGenDate a char?? bad juju if so
3) AND Cl.ClNature=@ClNature AND (Cl.ClCustCustCd=@ClCustCustCd OR Cl.ClCustCustCd IS NULL)
this could be the killer of the query. How many rows have CLCustCustCd NULL? If there are a bunch you are screwed because you will get table scans all over the place. If there are very few, force an index seek on that column's index (it does have one, right??).
4) LOTS of clustered index scans - i.e. table scans. This may be addressed with indexing - or you could be screwed by 3 above.
5) please attach query plans as an attachment so they can be easily opened in SSMS. 🙂
6) Consider getting some professional help to mentor you on how to effectively tune stuff like this if it is now your responsibility!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 21, 2009 at 11:44 pm
Hi All,
Thanks for your inputs. I am attaching the query execution plan. Please tell me as what is going on wrong.
There is no NULL value in the column 'ClCustCustCd' so I have removed the condition "ClCustCustCd is null".
September 22, 2009 at 1:46 am
I can see some "missing index" elements in the query plan you've posted.
Please see BOL ---> Using Missing Index Information to Write CREATE INDEX Statements or use the "db engine tuning advisor" (---> "index tuning wizard") to add the indexes you need.
September 22, 2009 at 2:23 am
Also take into account how your applications connect and use the sproc !
What's their used Isolation Level ? ( should be "read committed" in 99.9% of the cases)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 26, 2009 at 9:31 pm
Hi
Please evaluate @nalytics Performance Free Data Collector for Microsoft SQL Server &
Windows Server, this tool can help you to solve your performance problems and get
performance archive history information
Regards
@Analytics Peformance - Microsoft SQL Server & Windwos Server Data Collector
November 29, 2009 at 12:56 pm
This is an old post, I wonder how this was (if at all) resolved.
If not, can you post the MEMPOFF table DDL? I just wonder, if creating NC covering index on Memp_code and Memp_CompCd with included Memp_Fname, Memp_LName and Memp_RimNo wouldn't help.
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply