在使用链接服务器 查询时 where条件中带上变量后查询变得很慢
sql declare 赋值查询慢
3种查询执行计划:
1,传值
2,传参
3,使用OPTION(RECOMPILE),执行计划与传值一致。 实际执行时是重新编译了
在语句的最后 附加一句 OPTION(RECOMPILE)
DECLARE @bdat DATETIME ,@edat DATETIME
SET @bdat= CAST ((LEFT ('20230501', 4) + '-' + substring ('20230501', 5, 2) + '-' + substring ('20230501', 7, 2) + ' ' + '00:00:00') AS DATETIME)
SET @edat= CAST ((LEFT ('20230503', 4) + '-' + substring ('20230503', 5, 2) + '-' + substring ('20230503', 7, 2) + ' ' + '23:59:59') AS DATETIME)
SELECT replace(SUBSTRING(stakedeptif,1,CHARINDEX('/',stakedeptif)-1),' ','') AS dptid ,count(product) --b.stakedeptid,bit.product
FROM stake_bill b
LEFT JOIN stake_billitem bit ON b.id = bit.stakebill
WHERE createdate >= @bdat AND createdate <= @edat
GROUP BY replace(SUBSTRING(stakedeptif,1,CHARINDEX('/',stakedeptif)-1),' ','')
OPTION(RECOMPILE)