Query performance with date parameters
I have query performance issues when using date parameters in a stored procedure. The proc takes 15 minutes with hard-coded date values but well over an hour with parameters. I was calling it from SSIS and had trouble passing the variables as datetime2. So I was passing as varchar and handling the conversion in the proc. Then I took SSIS out of it as i tried to debug. I've been searching for quite some time and I can't get anything to work. This is basically where I've left off. (SQL2016)
EXEC dbo.GetData @ObsStartDt = '1/1/2017', @ObsEndDt = '12/31/2017'
CREATE PROCEDURE [dbo].[GetData] @ObsStartDt varchar(10) = NULL, @ObsEndDt varchar(10) = NULL
AS
BEGIN TRY
SET NOCOUNT ON;
declare @StartDT datetime2
declare @EndDt datetime2
set @StartDT = cast(@ObsStartDt as datetime2)
set @EndDt = cast(@ObsEndDt as datetime2)
select <stuff)
from HealthPlanDM.MED_CLAIMS_FA
join HealthPlanDM.MEMBER_DEMO_D
on a.MEM_DEMO_KEY = b.MEM_DEMO_KEY
join HealthPlanDM.PRODUCT_DIM c
on a.PROD_KEY = c.PROD_KEY
join HealthPlanDM.DIAGNOSIS_DIM
on a.DIAG_KEY = d.DIAG_KEY
join HealthPlanDM.PROCEDURE_DIM
on a.PROC_KEY = e.PROC_KEY
join HealthPlanDM.SERVICING_PRO
on a.SERV_PROV_KEY = f.SERV_PROV_KEY
join HealthPlanDM.POS_DIM g
on a.POS_KEY = g.POS_KEY
join HealthPlanDM.DRG_DIM h
on a.DRG_KEY = h.DRG_KEY
join HealthPlanDM.TOS_DIMBASE i
on a.TOS_KEY = i.TOS_KEY
join devwork.ACG_PATIENT_EXTRAC
on b.MEM_ID = P.patient_id
left outer join [Provider].NPI_REGISTRYBAS
on f.SERV_PROV_NPI = REG.NPI
left outer join DevWork.TAXONOMY_XWALK TAX
on REG.PrimaryTaxonomyCD = TAX.TAXONOMY_CODE
where c.PLAN_CODE <> 'MHACO' and
a.ALL_AMT IS NOT NULL
and a.DOS between @StartDT and @EndDt
OPTION (RECOMPILE)
end try
EXEC dbo.GetData @ObsStartDt = '1/1/2017', @ObsEndDt = '12/31/2017'
CREATE PROCEDURE [dbo].[GetData] @ObsStartDt varchar(10) = NULL, @ObsEndDt varchar(10) = NULL
AS
BEGIN TRY
SET NOCOUNT ON;
declare @StartDT datetime2
declare @EndDt datetime2
set @StartDT = cast(@ObsStartDt as datetime2)
set @EndDt = cast(@ObsEndDt as datetime2)
select <stuff)
from HealthPlanDM.MED_CLAIMS_FA
join HealthPlanDM.MEMBER_DEMO_D
on a.MEM_DEMO_KEY = b.MEM_DEMO_KEY
join HealthPlanDM.PRODUCT_DIM c
on a.PROD_KEY = c.PROD_KEY
join HealthPlanDM.DIAGNOSIS_DIM
on a.DIAG_KEY = d.DIAG_KEY
join HealthPlanDM.PROCEDURE_DIM
on a.PROC_KEY = e.PROC_KEY
join HealthPlanDM.SERVICING_PRO
on a.SERV_PROV_KEY = f.SERV_PROV_KEY
join HealthPlanDM.POS_DIM g
on a.POS_KEY = g.POS_KEY
join HealthPlanDM.DRG_DIM h
on a.DRG_KEY = h.DRG_KEY
join HealthPlanDM.TOS_DIMBASE i
on a.TOS_KEY = i.TOS_KEY
join devwork.ACG_PATIENT_EXTRAC
on b.MEM_ID = P.patient_id
left outer join [Provider].NPI_REGISTRYBAS
on f.SERV_PROV_NPI = REG.NPI
left outer join DevWork.TAXONOMY_XWALK TAX
on REG.PrimaryTaxonomyCD = TAX.TAXONOMY_CODE
where c.PLAN_CODE <> 'MHACO' and
a.ALL_AMT IS NOT NULL
and a.DOS between @StartDT and @EndDt
OPTION (RECOMPILE)
end try
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
What you've done in the procedure is to disable parameter sniffing, so your query plan is generic rather than taking the parameter values into account.
All that to say this: Figure out how to pass those values as datetime2 into this procedure. One way that comes to mind is to call a wrapper procedure from SSIS that does the conversion and calls the core procedure. Something else to try is to do the conversion in the where clause.
Can I recommend that you NOT use between with datetime date in your where clause. Can I recommend the following:
where
c.PLAN_CODE <> 'MHACO'
and a.ALL_AMT IS NOT NULL
and a.DOS >= @StartDT
and a.DOS < dateadd( day, @EndDt, 1 )
The other things to point out is that the != means that your where clause may be non-sargable - which means that SQL has difficulties using the best query plans and running faster.
HTH
David
What you've done in the procedure is to disable parameter sniffing, so your query plan is generic rather than taking the parameter values into account.
All that to say this: Figure out how to pass those values as datetime2 into this procedure. One way that comes to mind is to call a wrapper procedure from SSIS that does the conversion and calls the core procedure. Something else to try is to do the conversion in the where clause.
Can I recommend that you NOT use between with datetime date in your where clause. Can I recommend the following:
where
c.PLAN_CODE <> 'MHACO'
and a.ALL_AMT IS NOT NULL
and a.DOS >= @StartDT
and a.DOS < dateadd( day, @EndDt, 1 )
The other things to point out is that the != means that your where clause may be non-sargable - which means that SQL has difficulties using the best query plans and running faster.
HTH
David
What data type is a.DOS ?
If it's datetime2, then you're ok with the param being dateetime2.
If it's datetime, you're much better off using datetime as the param type as well.
As to overall performance, if you (almost) always have a WHERE on DOS for table:
HealthPlanDM.MED_CLAIMS_FA
then the best way to gain performance is to cluster that table on DOS. That will result in better query performance overall, as long as the data type of the variable being compared to DOS is at least remotely compatible.
If it's datetime2, then you're ok with the param being dateetime2.
If it's datetime, you're much better off using datetime as the param type as well.
As to overall performance, if you (almost) always have a WHERE on DOS for table:
HealthPlanDM.MED_CLAIMS_FA
then the best way to gain performance is to cluster that table on DOS. That will result in better query performance overall, as long as the data type of the variable being compared to DOS is at least remotely compatible.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial