0

I Have a question regarding the performance of a SELECT-statement with and without variables. The following statement runs in about 3 secs:

SELECT TOP 10 CAST(a.PLZ05 AS NVARCHAR(7)) AS Postleitzahl,
              SUM(a.ANZ)                   AS Anzahl,
              a.[HER]                      AS HSN,
              a.HERTX                      AS Hersteller,
              a.TYP                        AS TSN,
              CASE
                WHEN b.[Fabrikmarke] IS NULL THEN 'k.A.'
                ELSE b.[Fabrikmarke]
              END                          AS Fabrikmarke,
              CASE
                WHEN b.[Handelsname] IS NULL THEN 'k.A.'
                ELSE b.[Handelsname]
              END                          AS Handelsname,
              CASE
                WHEN AVG(b.[Nennleistung_KW]) IS NULL THEN 'k.A.'
                ELSE CAST(AVG(b.[Nennleistung_KW]) AS NVARCHAR(10))
              END                          AS Nennleistung_KW,
              CASE
                WHEN AVG(b.[Nennleistung_PS]) IS NULL THEN 'k.A.'
                ELSE CAST(AVG(b.[Nennleistung_PS]) AS NVARCHAR(10))
              END                          AS Nennleistung_PS,
              CASE
                WHEN AVG(b.[MAX_Hubraum_cm3]) IS NULL THEN 'k.A.'
                ELSE CAST(AVG(b.[MAX_Hubraum_cm3]) AS NVARCHAR(10))
              END                          AS MAX_Hubraum_cm3
FROM   STAMM_KBA a
       LEFT OUTER JOIN STAMM_FZG b
         ON a.[HER] = b.HSN
            AND a.TYP = b.TSN
       LEFT OUTER JOIN STAMM_BRD_GeoDaten c
         ON a.PLZ05 = c.StadtGemeinde_Plz
WHERE  b.[Code_Kraftstoffart_Energiequelle] LIKE '%%'
       AND [Nennleistung_KW] BETWEEN 10 AND 110
       AND [Nennleistung_PS] BETWEEN 0 AND 427
       AND [MAX_Hubraum_cm3] BETWEEN 100 AND 1000
       AND c.Bundesland = 'Niedersachsen'
       AND PLZ05 != 0
GROUP  BY a.PLZ05,
          a.[HER],
          a.HERTX,
          a.TYP,
          b.[Fabrikmarke],
          b.[Handelsname] 

If I use variables the speed of the select statement decreases dramatically down to over 3 mins. Here´s the statement with variables:

DECLARE @kraftstoffart NVARCHAR(1)
DECLARE @kw_von INT
DECLARE @kw_bis INT
DECLARE @ps_von INT
DECLARE @ps_bis INT
DECLARE @ccm_von INT
DECLARE @ccm_bis INT
DECLARE @value1 NVARCHAR(255)

SET @kraftstoffart = ''
SET @kw_von = 10
SET @kw_bis = 110
SET @ps_von = 0
SET @ps_bis = 427
SET @ccm_von = 100
SET @ccm_bis = 1000
SET @value1 = 'Niedersachsen'

SELECT TOP 10 CAST(a.PLZ05 AS NVARCHAR(7)) AS Postleitzahl,
              SUM(a.ANZ)                   AS Anzahl,
              a.[HER]                      AS HSN,
              a.HERTX                      AS Hersteller,
              a.TYP                        AS TSN,
              CASE
                WHEN b.[Fabrikmarke] IS NULL THEN 'k.A.'
                ELSE b.[Fabrikmarke]
              END                          AS Fabrikmarke,
              CASE
                WHEN b.[Handelsname] IS NULL THEN 'k.A.'
                ELSE b.[Handelsname]
              END                          AS Handelsname,
              CASE
                WHEN AVG(b.[Nennleistung_KW]) IS NULL THEN 'k.A.'
                ELSE CAST(AVG(b.[Nennleistung_KW]) AS NVARCHAR(10))
              END                          AS Nennleistung_KW,
              CASE
                WHEN AVG(b.[Nennleistung_PS]) IS NULL THEN 'k.A.'
                ELSE CAST(AVG(b.[Nennleistung_PS]) AS NVARCHAR(10))
              END                          AS Nennleistung_PS,
              CASE
                WHEN AVG(b.[MAX_Hubraum_cm3]) IS NULL THEN 'k.A.'
                ELSE CAST(AVG(b.[MAX_Hubraum_cm3]) AS NVARCHAR(10))
              END                          AS MAX_Hubraum_cm3
FROM   STAMM_KBA a
       LEFT OUTER JOIN STAMM_FZG b
         ON a.[HER] = b.HSN
            AND a.TYP = b.TSN
       LEFT OUTER JOIN STAMM_BRD_GeoDaten c
         ON a.PLZ05 = c.StadtGemeinde_Plz
WHERE  b.[Code_Kraftstoffart_Energiequelle] LIKE '%' + @kraftstoffart + '%'
       AND [Nennleistung_KW] BETWEEN @kw_von AND @kw_bis
       AND [Nennleistung_PS] BETWEEN @ps_von AND @ps_bis
       AND [MAX_Hubraum_cm3] BETWEEN @ccm_von AND @ccm_bis
       AND c.Bundesland = @value1
       AND PLZ05 != 0
GROUP  BY a.PLZ05,
          a.[HER],
          a.HERTX,
          a.TYP,
          b.[Fabrikmarke],
          b.[Handelsname] 

Why is this happening and how can I speed up the performance again?

I´m using SQL-Server 2008 R2.

Thanks a lot for your help...

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Chris
  • 835
  • 12
  • 27

0 Answers0