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_FACT a
join HealthPlanDM.MEMBER_DEMO_DIM b
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 d
on a.DIAG_KEY = d.DIAG_KEY
join HealthPlanDM.PROCEDURE_DIM e
on a.PROC_KEY = e.PROC_KEY
join HealthPlanDM.SERVICING_PROV_DIM f
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_EXTRACT1 P
on b.MEM_ID = P.patient_id
left outer join [Provider].NPI_REGISTRYBASE REG
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
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.