webentwicklung-frage-antwort-db.com.de

Dieselbe Abfrage wird schneller ausgeführt, wenn sie nicht in proc ist

Wir haben eine spezifische Abfrage, die in einem Prozess viel langsamer ausgeführt wird. Ich muss hier hinzufügen, dass es von einem Zwei-Ebenen-Cursor umgeben ist. Beide Cursor haben jedoch eine Iterationsergebnismenge von einer Zeile.

Lassen Sie mich zunächst die Dinge angeben, die wir versucht und versagt haben:

  • Vermeiden von Parameter-Sniffing mit Option (Neukompilieren) und Option (Optimierung für (@var UNKNOWN)
  • Dieser Thread . Die Variablen, die das Problem zu sein scheinen, sind tatsächlich lokale und keine proc-Parameter.

Hier ist die Abfrage aus den proc/cursors.

 select @tpdim1 = dim1, @tpdim2 = dim2, @typecalc = typecalc
    from loyalty_policy where [email protected]_loyalty_policy

Hinweis: @ loop2_loyalty_policy ist die Varia aus dem Ergebnis des inneren Cursors und hat einen Wert. code ist PK in der loyalty_policy-Tabelle. @ Tpdim1 und @ tpdim2 haben also jeweils einen einzigen Wert.

SET STATISTICS PROFILE ON 
SET STATISTICS    xml on           
                  insert into @tbl_loyal_loop2 (cnt, store, map, pda, insdate, line, item, loyalty_policy_data, loyal_calc, loyalty_policy)
                  select @cnt, t.store, t.map, t.pda, t.insdate, t.line, t.item, ld.tab_id,  
                  case @typecalc
                        when 1 then convert(bigint,round(isnull(t.valueFromTran,0.00) * ld.value , 0 ) )
                        when 2 then convert(bigint,round(isnull(t.netvalue,0.00) * ld.value , 0 ) )
                        when 3 then convert(bigint,isnull(t.qty,0) * ld.value )
                        when 4 then convert(bigint,round(isnull(t.valueFromPrice2,0.00) * ld.value , 0 ) )
                        when 5 then convert(bigint,round(isnull(t.valueFromPrice3,0.00) * ld.value , 0 ) )
                        when 6 then convert(bigint,round(isnull(t.valueFromPrice4,0.00) * ld.value , 0 ) )
                  else 0 end
                  ,@loop2_loyalty_policy
                  from loyalty_policy_data ld-- with (index=ind_loyalty_policy_02)
                              inner join #tbl_data t on t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
                  where ld.loyalty_policy = @loop2_loyalty_policy 
                  and ld.tdateactive >= @from_rundate and ld.fdateactive <= @to_rundate
                  and t.dbupddate > @loop1_dbupddate  
                  and
                        case when @tpdim1 is null then '' 
                        else  
                              case  @tpdim1 
                                    when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                                    when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                                    when 'CUSTOMER'         then @customer
                              else '' end
                        end
                        = case when @tpdim1 is null then '' else ld.dim1 end
                  and 
                        case when @tpdim2 is null then '' 
                        else  
                              case  @tpdim2 
                                    when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                                    when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                                    when 'CUSTOMER'         then @customer                     
                              else '' end
                        end
                        = case when @tpdim2 is null then '' else ld.dim2 end
SET STATISTICS    xml off

Der SET STATISTICS XML für das oben genannte gibt diesen Plan zurück.

Beim Debuggen haben wir die Abfrage in der folgenden Form isoliert (hier können Sie auch sehen, wie die Tabelle #a erstellt wird, die genau dieselben Daten wie die vorherigen #tbl_data hat):

drop table #a;
select dt.dbupddate, dt.insdate, dt.map, dt.pda, pt.line, pt.item, 
( pt.exp_qty - pt.imp_qty)  as qty,  
( pt.exp_value + pt.imp_value )  as netvalue, 
( (document.exp_val - document.imp_val) * (pt.netvalue - pt.vat_value) )  as valueFromTran,  
( (document.exp_val - document.imp_val) * ( ( (pt.qty - pt.qty_gift) * isnull(pt.price2,0.00) ) * (1.00-( pt.disc_perc / 100)) ) ) as valueFromPrice2, 
( (document.exp_val - document.imp_val) * ( ( (pt.qty - pt.qty_gift) * isnull(pt.price3,0.00) ) * (1.00-( pt.disc_perc / 100)) ) ) as valueFromPrice3, 
( (document.exp_val - document.imp_val) * ( ( (pt.qty - pt.qty_gift) * isnull(pt.price4,0.00) ) * (1.00-( pt.disc_perc / 100)) ) ) as valueFromPrice4, 
dt.store, item.brand, item.cat1, item.cat2, item.cat3, customer.custgroup, customer.custgroup2, customer.custgroup3 
into #a
from document with (nolock) 
      inner join dt with (nolock) on dt.doccode = document.code 
      inner join store with (nolock) on store.code = dt.store and store.calc_loyal = 1 
      inner join customer with (nolock) on customer.code = dt.customer  
      inner join pt with (nolock) on dt.map = pt.map and dt.pda=pt.pda 
      inner join item with (nolock) on item.code = pt.item and item.itemtype in (select code from itemtype with (nolock) where vsales = 1)
where dt.canceled = 0 and document.is_opposite = 0 and document.type = 3 and dt.customer=N'EL4444444'
and dt.insdate >= '20180109' and dt.insdate <= '20190108' ;



SET STATISTICS PROFILE ON 
                  select t.store, t.map, t.pda, t.insdate, t.line, t.item, ld.tab_id,  
                  case 4
                        when 1 then convert(bigint,round(isnull(t.valueFromTran,0.00) * ld.value , 0 ) )
                        when 2 then convert(bigint,round(isnull(t.netvalue,0.00) * ld.value , 0 ) )
                        when 3 then convert(bigint,isnull(t.qty,0) * ld.value )
                        when 4 then convert(bigint,round(isnull(t.valueFromPrice2,0.00) * ld.value , 0 ) )
                        when 5 then convert(bigint,round(isnull(t.valueFromPrice3,0.00) * ld.value , 0 ) )
                        when 6 then convert(bigint,round(isnull(t.valueFromPrice4,0.00) * ld.value , 0 ) )
                  else 0 end
                  ,'003'
                  --select count(*)
                  from loyalty_policy_data ld with (index=ind_loyalty_policy_02)
                              inner join #a t on t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
                  where ld.loyalty_policy = '003' 
                  --and ld.tdateactive >= '20180109' and ld.fdateactive <= '20190108'
                  and t.dbupddate > '20000101'
      and 
                        case when 'CUSTOMER' is null then '' 
                        else  
                              case  'CUSTOMER' 
                                    when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                                    when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                                    when 'CUSTOMER'         then 'EL0134366'
                              else '' end
                        end
                        = case when 'CUSTOMER' is null then '' else ld.dim1 end
                  and 
                        case when 'BRAND' is null then '' 
                        else  
                              case  'BRAND' 
                                    when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                                    when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                                    when 'CUSTOMER'         then 'EL0134366'

                              else '' end
                        end
                        = case when 'BRAND' is null then '' else ld.dim2 end
SET STATISTICS PROFILE off

Und hier ist der Ausführungsplan. Dies läuft viel schneller.

Warum dieser riesige Unterschied? Aus meinem begrenzten Wissen über die Ausführungsanalyse habe ich festgestellt 

  1. Die erste (langsame) Abfrage der index spool-Operation hat geschätzte Zeilen von ~ 9700, aber tatsächliche Zeilen von 3 Millionen. 
  2. Die zweite Abfrage hat viele Operationen mit Parallelität verwendet
  3. Der einzige "echte" Unterschied, den ich in der zweiten Abfrage sehen kann, sind die von Hand ersetzten Werte der Werte @ tpdim1 und @ tpdim2. Sicher genug, als wir in den Proc-Code der ersten Abfrage gegangen sind und @ tpdim1 & @ tpdim2 durch die einzelnen Werte ersetzt haben, die sie erhalten sollen, lief so schnell wie die zweite Abfrage.

Könnten Sie diesen Unterschied bitte erläutern und einen Rat vorschlagen, um das Verfahren festzulegen?

Edit: Wie Laughing Vergil empfahl, habe ich die Literale in der zweiten Abfrage durch zuvor deklarierte Variablen ersetzt, und wieder läuft es langsam! 

Edit 2: Ich habe einige zusätzliche Informationen, wenn ich weiter recherchiere.

Zuerst habe ich das Problem auf diese Zeile isoliert:

case when @tpdim1 is null then '' <- Verwendet den langsamen Plan

case when 'CUSTOMER' is null then '' <- Verwendet den schnellen Plan

Dies trifft in der Ad-hoc-Abfrage zu, es ist nicht nötig, sich mit SPCs und/oder Cursorn zu beschäftigen.

Dies geschieht auch dann, wenn ich den Code in die empfohlene dynamische where-Struktur ändere.

Ich habe noch keine Sampla-Daten erstellt, aber die wichtige Information (wie aus den Plänen ersichtlich) ist, dass loyalty_policy_data etwa 720.000 Zeilen hat, wenn wir nur nach loyalty_policy = @loop2_loyalty_policy filtern. Wenn wir jedoch die @ tpdim1-Bedingung auswerten, die im Wesentlichen dim1 = N'EL0134366 'ist, sind die zurückgegebenen Zeilen nur 4.

Der Unterschied im Plan besteht dann, wenn diese Bedingung hinsichtlich der Datumsüberprüfungsbedingungen bewertet wird.

Im Schnellplan wird es zuerst ausgewertet. Bei der Suche nach dem Index für den Wert der Loyalitätsrichtlinie wird ein Prädikat (Non-Search) hinzugefügt. Während dieses Prädikat nicht im Index enthalten ist, sind die zurückgegebenen Zeilen 4 und alle anderen Operatoren haben "logische" Größen.

Im Gegensatz dazu ignoriert der langsame Plan dieses Prädikat schmerzhaft bis zu spät. Wenn ich richtig gedacht habe, macht es eine geschachtelte Schleife auf loyalty_policy_data als der äußeren Tabelle (was verrückt ist). Die übergebenen Spalten werden als äußere Referenzen übergeben. Für jeden Tupel scannt der Index-Spool die #Tabelle (~ 1k Zeilen), findet etwa 250 Ergebnisse und gibt diese an den Filter weiter, der finally die Filterung von tpdim1 durchführt. Somit werden 250 * 700.000 Zeilen an den Filteroperator übergeben.

Jetzt glaube ich zu wissen, was passiert. Aber ich kann nicht verstehen warum. 

sqlsql-servertsqlstored-proceduressql-execution-plan
6
George Menoutis

Zur Beantwortung Ihrer Frage:

Eine klare und reproduzierbare Erklärung, wie und warum der Abfrage-Analysator verhält sich in diesen Fällen anders

Das Abfrageoptimierungsprogramm verhält sich in diesen Fällen unterschiedlich, da der Plan mit Variablen für any möglichen zukünftigen Wert von Parametern gültig sein muss. Das Optimierungsprogramm generiert daher einen komplizierten generischen Plan, der auch dann korrekte Ergebnisse liefert, wenn die Parameter NULL sind.

Der Plan mit Literalen (nicht Variablen) ist normalerweise effizienter, da der Optimierer Ihre CASE-Logik während der Planerstellungsphase erheblich vereinfachen kann. Das Optimierungsprogramm hat eine bessere Chance, die optimale Planform zu wählen, da das Optimierungsprogramm leichter verfügbare Informationen zu Indizes und Kardinalitätsschätzungen berücksichtigen kann, wenn die Abfrage einfacher ist und die Filter bekannte Werte haben.

Martin Smith hat in dem Kommentar darauf hingewiesen, dass Sie die Server-Version 10.0.2531.0 verwenden, die 2008 SP1 ist und bei der die Optimierung der Parameter-Einbettung nicht aktiviert ist. Sie müssten bei minimum SP1 CU5 in diesem Zweig funktionieren, damit OPTION (RECOMPILE) ordnungsgemäß funktioniert (wie ich es in der nachstehenden Erklärung erwartet hatte).

Erland Sommarskog spricht darüber in seinem Artikel . Er sagt, dass Sie mindestens auf SP2 sein müssen.

Wenn Sie den Server nicht aktualisieren können, lesen Sie die ältere Version des Erland-Artikels Dynamische Suchbedingungen in T-SQL-Version für SQL 2005 und frühere Versionen , um zu erfahren, wie Sie mit dieser Situation umgehen können, wenn OPTION (RECOMPILE) nicht verfügbar ist.

Hier ist meine ursprüngliche Antwort.

Ich weiß, dass Sie gesagt haben, dass Sie es versucht haben, aber ich würde Sie trotzdem bitten, es noch einmal zu überprüfen. Wenn Sie sich Ihre Symptome ansehen, sollte OPTION (RECOMPILE) helfen. 

Sie müssen diese Option zur Hauptabfrage hinzufügen. Nicht für die gesamte gespeicherte Prozedur. So was:

insert into @tbl_loyal_loop2 (cnt, store, map, pda, insdate, line, item, loyalty_policy_data, loyal_calc, loyalty_policy)
select @cnt, t.store, t.map, t.pda, t.insdate, t.line, t.item, ld.tab_id,  
case @typecalc
    when 1 then convert(bigint,round(isnull(t.valueFromTran,0.00) * ld.value , 0 ) )
    when 2 then convert(bigint,round(isnull(t.netvalue,0.00) * ld.value , 0 ) )
    when 3 then convert(bigint,isnull(t.qty,0) * ld.value )
    when 4 then convert(bigint,round(isnull(t.valueFromPrice2,0.00) * ld.value , 0 ) )
    when 5 then convert(bigint,round(isnull(t.valueFromPrice3,0.00) * ld.value , 0 ) )
    when 6 then convert(bigint,round(isnull(t.valueFromPrice4,0.00) * ld.value , 0 ) )
else 0 end
,@loop2_loyalty_policy
from loyalty_policy_data ld -- with (index=ind_loyalty_policy_02)
            inner join #tbl_data t on t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
where ld.loyalty_policy = @loop2_loyalty_policy 
and ld.tdateactive >= @from_rundate and ld.fdateactive <= @to_rundate
and t.dbupddate > @loop1_dbupddate  
and
    case when @tpdim1 is null then '' 
    else  
            case  @tpdim1 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer
            else '' end
    end
    = case when @tpdim1 is null then '' else ld.dim1 end
and 
    case when @tpdim2 is null then '' 
    else  
            case  @tpdim2 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer                     
            else '' end
    end
    = case when @tpdim2 is null then '' else ld.dim2 end
OPTION(RECOMPILE);

Mit OPTION (RECOMPILE) soll das Parameter-Sniffing nicht gemildert werden, sondern es dem Optimierer ermöglicht werden, die tatsächlichen Parameterwerte in die Abfrage zu integrieren. Dies gibt dem Optimierer die Freiheit, die Abfragelogik zu vereinfachen.

Der Typ Ihrer Abfrage sieht aus wie Dynamic Search Conditions und ich empfehle Ihnen dringend, diesen Artikel von Erland Sommarskog zu lesen.

Auch statt 

and
    case when @tpdim1 is null then '' 
    else  
            case  @tpdim1 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer
            else '' end
    end
    = case when @tpdim1 is null then '' else ld.dim1 end

Ich würde es etwas anders schreiben:

and
(
    @tpdim1 is null
    OR
    (
            ld.dim1 =
            case @tpdim1
                when 'STORE'      then t.store 
                when 'BRAND'      then t.brand  
                when 'CAT1'       then t.cat1   
                when 'CAT2'       then t.cat2   
                when 'CAT3'       then t.cat3   
                when 'ITEM'       then t.item    
                when 'CUSTGROUP'  then t.custgroup 
                when 'CUSTGROUP2' then t.custgroup2 
                when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'   then @customer
                else ''
            end
    )
)

Bei OPTION (RECOMPILE), wenn @tpdim1 einen Wert von CUSTOMER hat und @customer einen Wert von EL0134366 hat, sollte der Optimierer diese Anweisung in eine einfache Form umwandeln

and
(
    ld.dim1 = `EL0134366`
)

dann könnte er einen geeigneten Index verwenden oder die Anzahl der Zeilen genauer schätzen und eine bessere Entscheidung für die Planform treffen. Bei dieser Option ist der Plan nur für diesen bestimmten Wert des Parameters gültig.

Beachten Sie, dass option (optimize for UNKNOWN) hier nicht helfen kann. optimize for UNKNOWN müsste einen generischen Plan generieren, der für jeden möglichen Wert von Parametern gültig ist.

1
Vladimir Baranov

nachdem ich die Abfrage aus Gründen der Lesbarkeit aufgeräumt habe, habe ich Folgendes.

insert into @tbl_loyal_loop2 
( cnt, 
  store, 
  map, 
  pda, 
  insdate, 
  line, 
  item, 
  loyalty_policy_data, 
  loyal_calc, 
  loyalty_policy
)
select 
      @cnt, 
      t.store, 
      t.map, 
      t.pda, 
      t.insdate, 
      t.line, 
      t.item, 
      ld.tab_id,
      convert(bigint, round( coalesce(
         case @typecalc
               when 1 then t.valueFromTran
               when 2 then t.netvalue
               when 3 then t.qty
               when 4 then t.valueFromPrice2
               when 5 then t.valueFromPrice3
               when 6 then t.valueFromPrice4
               else 0 
            END,   0.00) * ld.value , 0 ) ),
      @loop2_loyalty_policy
   from 
      loyalty_policy_data ld  -- with (index=ind_loyalty_policy_02)
         inner join #tbl_data t 
            on t.insdate >= ld.fdateactive 
            and t.insdate <= ld.tdateactive
   where 
          ld.loyalty_policy = @loop2_loyalty_policy 
      and ld.tdateactive >= @from_rundate 
      and ld.fdateactive <= @to_rundate
      and t.dbupddate > @loop1_dbupddate  
      and (   @tpdim1 is null
           OR ld.dim1 = case @tpdim1
                           when 'STORE' then t.store 
                           when 'BRAND' then t.brand  
                           when 'CAT1' then t.cat1   
                           when 'CAT2' then t.cat2   
                           when 'CAT3' then t.cat3   
                           when 'ITEM' then t.item    
                           when 'CUSTGROUP' then t.custgroup 
                           when 'CUSTGROUP2' then t.custgroup2 
                           when 'CUSTGROUP3' then t.custgroup3
                           when 'CUSTOMER' then @customer
                           else ''
                          END )
      and (   @tpdim2 is null
           OR ld.dim2 = case when @tpdim1
                         when 'STORE' then t.store 
                         when 'BRAND' then t.brand  
                         when 'CAT1' then t.cat1
                         when 'CAT2' then t.cat2
                         when 'CAT3' then t.cat3
                         when 'ITEM' then t.item    
                         when 'CUSTGROUP' then t.custgroup 
                         when 'CUSTGROUP2' then t.custgroup2 
                         when 'CUSTGROUP3' then t.custgroup3
                         when 'CUSTOMER' then @customer
                         else '' 
                      END )

Darüber hinaus würde ich sicherstellen, dass Sie einen zusammengesetzten Index für Ihre Tabelle loyalty_policy_data ... haben. Index on (loyalty_policy, tdateactive, fdateactive, dbupddate, dim1, dim2)

Auf diese Weise qualifizieren Sie alle Felder, die in Ihren WHERE-Filterkriterien verwendet werden. Verlassen Sie sich nicht auf einen Index nur des Schlüssels ... aber der Schlüssel PLUS, die Datumsangaben helfen dabei, den spezifischen Datumsbereich zu optimieren, ohne zu den Rohdatenseiten zurückkehren zu müssen. Sie können jedoch die Abfrage JOIN-Bedingungen basierend auf den Werten im INDEX optimieren .

Stellen Sie für Ihre temporäre Tabelle #tbl_data sicher, dass Sie einen Index für (insdate) haben, da dies das einzige JOIN-Basiskriterium ist (falls Sie noch keinen Index für diese Tabelle hatten).

KOMMENTAR --

Aus Ihrem Kommentar zur langsamen vs. schnellen Abfrage basierend auf der Null 

@ tpdim1 = NULL vs 'CUSTOMER' = NULL

eine feste Zeichenfolge 'CUSTOMER' ist NIE NULL und muss daher nicht als Nullpfad betrachtet werden. Die Zeichenfolge "CUSTOMER" gegenüber der @customer -Variable ist null oder wird verglichen mit dem Fall/when von ld.dim1 und ld.dim2, die jeweils mit null verglichen werden ... Die Frage, für die getestet werden muss, wurde geändert

  and (   @tpdim1 is null
               OR ld.dim1 = case @tpdim1
                               when 'STORE' then t.store 
                               when 'BRAND' then t.brand  ... end
     )

zu

  and ld.dim1 = case @tpdim1
                when NULL then ''
                when 'STORE' then t.store 
                when 'BRAND' then t.brand  ... end

Gleiches mit dem Fall ld.dim2/wann. Fügen Sie "NULL" als ersten getesteten Wert für die Tests @ tpdim1 (und @ tpdim2) ein.

4
DRapp

Im Allgemeinen ist die Abfrage mit literal value schneller als die Abfrage mit proc parameter oder local variable.

Wenn der Literalwert verwendet wird, erstellt Optimizer den Spezialplan nur für diesen Wert, wenn Forced Parameterization nicht aktiviert ist

Das Optimierungsprogramm kann auch Trivial Plan oder Simple Parameterize Plan erstellen, in Ihrem Fall ist dies jedoch nicht der Fall.

Wenn Sie einen Parameter verwenden, erstellt das Optimierungsprogramm einen Plan für diesen Parameter Dieser heißt Parameter Sniffing, Und verwendet diesen Plan anschließend erneut. 

Option Recompile ist eine Möglichkeit, dieses Problem zu überwinden: Erstellen Sie einen Plan für jeden verschiedenen Variablenwert, um die Kardinalitätsschätzung beizubehalten. Das ist sehr kurz

Die Abfrage mit Literalwert ist daher immer schneller.

Lassen Sie mich zunächst die Dinge angeben, die wir versucht und versagt haben:

• Vermeiden Sie das Parameter-Sniffing mit der Option (Neukompilieren) und der Option (Optimieren für (@var UNKOWN)

• Dieser Thread. Die Variablen, die das Problem zu sein scheinen, sind tatsächlich lokale und keine proc-Parameter.

Sie schlagen fehl, weil Ihre Abfrage sehr schlecht geschrieben wurde (mit gebührendem Respekt).

Verwenden Sie keinen Cursor. Es scheint, dass der Cursor in Ihrem Fall vermieden werden kann

Post abgeschlossene proc-Abfrage mit variablem Parameter, da die Logik zum Abrufen des Werts in @ loop2_loyalty_policy usw. nicht eindeutig ist. Dies wird dazu beitragen, korrekte Vorschläge zu geben, um den Cursor zu vermeiden.

case when @tpdim1 is null: Diese vollständige Logik kann erstellt und in die Temp-Tabelle eingefügt werden, sodass die neue Spalte sofort in join verwendet wird. Ich hoffe, Sie können meine Idee und Sprache verstehen.

1. Die erste (langsame) Abfrage der Index-Spool-Operation hat geschätzte Zeilen von ~ 9700, aber tatsächliche Zeilen von 3 Millionen.

Wegen hoher Kardinalitätsschätzung durch Optmizer bei falschem Join 

Ich bin mir nicht sicher, ob dies Ihre Anfrage und Ihre Kardinalitätsschätzung sicherlich verbessern wird, da ich Ihre Anfrage nicht zu 100% verstanden habe.

Aber das Ändern der Join-Bedingung hilft oft,

Lesen Sie hier sorgfältig durch, ich bin nicht sicher, welche Daten in der Spalte loyalty_policy und t.insdate vorhanden sind. Es scheint nicht so, dass Sie einen so komplizierten Join wie unten benötigen.

Wenn Sie wirklich brauchen, können Sie alter join condition einmal unten eingeben.

from loyalty_policy_data ld with (nolock)
 inner join #tbl_data t on ld.loyalty_policy = @loop2_loyalty_policy
 and ld.tdateactive >= @from_rundate and ld.fdateactive <= @to_rundate 
and t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
                  where t.dbupddate > @loop1_dbupddate

Hauptziel ist es, den Cursor zu vermeiden.

0
KumarHarsh