0

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;
Thom A
  • 88,727
  • 11
  • 45
  • 75
affenärschle
  • 123
  • 9
  • Parameter sniffing? Poor typing of columns? What data type is `DruckAm`? – Thom A Mar 15 '22 at 15:35
  • 3
    At a very minimum you should [include the actual Execution Plan](https://stackoverflow.com/a/7359705/1260204), you could use [Paste the Plan](https://www.brentozar.com/pastetheplan/) and share the link in your question. Also try to read it yourself, maybe you can figure out the performance issue(s) with your query. Finally include the [schema DDL](https://en.wikipedia.org/wiki/Data_definition_language) along with the query you are executing. – Igor Mar 15 '22 at 15:36
  • 1
    https://stackoverflow.com/questions/35627172/sql-any-performance-difference-using-constant-values-vs-parameters – J.Salas Mar 15 '22 at 15:38
  • 3
    The short story is the actual literal values are used to optimize the literal version at compile time whereas the query is optimized with average statistics values with variables. If you add `OPTION(RECOMPILE)`, variables will perform similarly due to constant folding. – Dan Guzman Mar 15 '22 at 15:41
  • OPTION(RECOMPILE) did the trick ! Thx. – affenärschle Mar 15 '22 at 16:00
  • What is the data type of `DruckAm`? Side point: you should always use half-open intervals with dates, so `AND DruckAm >= @Start AND DruckAm < @End` where `@End` is the next day – Charlieface Mar 15 '22 at 16:10
  • Try changing `@start` and `@end` to char(10) and I think that you will see a difference! –  Mar 15 '22 at 16:43
  • 1
    When using `@start` and `@end`, the optimizer will assume that a large portion of the date range will be selected, anywhere between 1/3 and 2/3. If the range from `'1.2.2022'` to `'28.2.2022'` is much smaller, then the generic query plan is actually overestimating, which can lead to poorer performance. If this type of query is really really really important to you, then consider a clustered index with `DruckAm` as leading column – Gert-Jan Mar 15 '22 at 18:33

0 Answers0