'SQL Server query performance using constants vs. variables
I'm wondering why this query with constant values takes about 2 minutes:
SELECT LeistungID,
NULL AS EKK,
NULL AS EKH,
SUM(KostenKunde) AS KostenKunde,
SUM(KostenHaendler) AS KostenHaendler,
SUM(Anzahl) AS Anzahl,
MwSt
FROM vauftragkostenporto2
WHERE kundenid = 676523
AND druckam BETWEEN '1.2.2022' AND '28.2.2022'
GROUP BY LeistungID,
Mwst;
Whereas the same query with parametersruns 50 minutes:
DECLARE @Start AS smalldatetime;
DECLARE @End AS smalldatetime;
DECLARE @KundenID AS int;
SET @KundenID = 676523;
SET @Start = CAST(CAST(2022 * 10000 + 2 * 100 + 1 AS varchar(255)) AS smalldatetime);
SET @End = DATEADD(MM, DATEDIFF(MM, -1, @Start), -1);
SELECT LeistungID,
NULL AS EKK,
NULL AS EKH,
SUM(KostenKunde) AS KostenKunde,
SUM(KostenHaendler) AS KostenHaendler,
SUM(Anzahl) AS Anzahl,
MwSt
FROM vAuftragKostenPorto2
WHERE KundenID = @KundenID
AND DruckAm BETWEEN @Start AND @End
GROUP BY LeistungID,
Mwst;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|