TSQL-Variable Mit Liste von Werten für die IN-Klausel

Möchte ich die Verwendung einer Klausel, die entlang der Linien von "CASE WHEN ... then 1 ELSE 0 END" in einer select-Anweisung. Der schwierige Teil ist, dass ich es brauche, mit zu arbeiten "- Wert IM @List".

Wenn ich hart code in der Liste funktioniert es - und es funktioniert gut:

SELECT
       CASE WHEN t.column_a IN ( 'value a', 'value b' ) THEN 1 ELSE 0 END AS priority
      , t.column_b
      , t.column_c
  FROM
       table AS t
 ORDER BY
       priority DESC

Was ich tun möchte ist:

-- @AvailableValues would be a list (array) of strings.
DECLARE
        @AvailableValues ???

 SELECT
        @AvailableValues = ???
   FROM
        lookup_table

 SELECT
        CASE WHEN t.column_a IN @AvailableValues THEN 1 ELSE 0 END AS priority
      , t.column_b
      , t.column_c
   FROM
        table AS t
  ORDER BY
        priority DESC

Leider, es scheint, dass der SQL Server dies nicht tut, können Sie nicht verwenden Sie eine variable mit einer IN-Klausel. So lässt mich das mit einigen anderen Optionen:

  1. Machen '@AvailableValues' eine durch Komma getrennte Zeichenfolge und verwenden Sie eine LIKE-Anweisung. Dies funktioniert nicht besonders gut.
  2. Die Verwendung einer inline-SELECT-Anweisung gegen das 'lookup_table' an die Stelle der variable. Wieder, nicht gut (denke ich), weil es um die lookup-Tabelle in jeder Zeile.
  3. Eine Funktion schreiben, die Verpackung um die SELECT-Anweisung anstelle der variable. Ich habe dies noch nicht ausprobiert (werde es jetzt versuchen), aber es scheint, dass es das gleiche problem, wie eine direct SELECT-Anweisung.
  4. ???

Gibt es andere Optionen? Leistung ist sehr wichtig für die Abfrage - es hat wirklich schnell sein, wie es ernährt einen Echtzeit-such-Ergebnis-Seite (also kein caching) für eine web-site.

Gibt es andere Optionen hier? Gibt es eine Möglichkeit zur Verbesserung der Leistung von einer der oben genannten Optionen, um auch gute Leistung?

Vielen Dank im Voraus für jede Hilfe!

UPDATE: ich sollte erwähnt haben, dass die "lookup_table' im obigen Beispiel ist bereits eine table-variable. Ich habe auch aktualisiert, die Probe Abfragen, um besser zu zeigen wie ich bin, mit der Klausel.

UPDATE II: Es fiel mir ein, dass die IN-Klausel ist in Betrieb off eine NVARCHAR-NCHAR Feld (wegen der historischen Tabelle design-Gründen). Wenn ich änderungen an, die sich mit integer-Felder (ich.e durch PK/FK-Beziehungen Einschränkungen), könnte dies große Auswirkungen auf die performance?

  • Werfen Sie einen Blick auf Erland Sommarskog den Artikel hier: Arrays und Listen im SQL Server 2005 und darüber Hinaus Für die Jahre 2000 und 2008-Versionen, schau mal hier: sommarskog.se/arrays-in-sql.html
  • Musst mehr Los hier als SELECT count(*) from MyTable where MyColumn in (select LookupValue from LookupTable). Poste die vollständige Abfrage und jemand wird Ihnen helfen zu optimieren.
  • bereits mit Hilfe der Tabelle Variablen in der SP, hatte performance Probleme hier.
  • Kelly, ich habe Beispiele wurden aktualisiert, um widerzuspiegeln, was ich versuche zu tun, besser.
InformationsquelleAutor Zac Seth | 2011-03-21
  1. 7

    Können Sie eine variable in einer IN - Klausel, nicht aber in der Art und Weise Sie zu tun versuchen. Zum Beispiel, Sie könnten dies tun:

    declare @i int
declare @j int

select @i = 10, @j = 20

select * from YourTable where SomeColumn IN (@i, @j)

    Der Schlüssel ist, dass die Variablen nicht darstellen kann, mehr als einen Wert.

    Ihre Frage zu beantworten, verwenden Sie die inline-wählen. Solange Sie nicht einen äußeren Wert in der Abfrage (was könnte sich ändern, die Ergebnisse auf einer pro-Zeilen-basis), der Motor wird nicht wiederholt die gleichen Daten selektiert aus der Tabelle.

    • Ja, gut, versuchte ich mit einem inline-wählen Sie " basierend auf einer Tabelle einer Variablen, aber die Leistung war schrecklich. In der Vergangenheit habe ich immer verwendet, die table-Variablen, um eine gute Wirkung (im Sinne der performance), aber es funktioniert nicht in dieser Zeit. Jubel für die Antwort obwohl.
    • Die Lösung, die wir kamen schließlich zu der Aufnahme der Beispiel-code, den Sie zur Verfügung gestellt (neben ein paar anderen änderungen, die wir vorgenommen Spalte Datentypen). Es ist nicht die flexibelste/verwaltbare Lösung aber, weil wir nur mit einer kleinen Anzahl von Variablen in der IN-Klausel und diese wird wahrscheinlich nicht zu ändern, wir haben es geschafft, die Leistung, die wir gesucht haben mit diesem Ansatz.
    InformationsquelleAutor Adam Robinson
  2. 2

    Basierend auf Ihrem update-und vorausgesetzt, die lookup-Tabelle klein ist, schlage ich vor, versuchen so etwas wie die folgenden:

    DECLARE @MyLookup table
 (SomeValue nvarchar(100) not null)

SELECT
   case when ml.SomeValue is not null then 1 else 0 end AS Priority   
  ,t.column_b
  ,t.column_c
 from MyTable t
  left outer join @MyLookup ml
   on ml.SomeValue = t.column_a
 order by case when ml.SomeValue is not null then 1 else 0 end desc

    (Man kann nicht auf die Spalte verweisen, alias "Priorität" in der ORDER BY-Klausel. Alternativ können Sie die Ordinalposition etwa so:

     order by 1 desc

    aber das ist in der Regel nicht empfohlen.)

    Solange die lookup-Tabelle ist klein , das wirklich laufen sollte ziemlich schnell-aber dein Kommentar impliziert, dass es eine ziemlich große Tabelle, und das könnte sich verlangsamen Leistung.

    Als für n[Var]char gegen int, ja, ganze zahlen wäre schneller, wenn auch nur, weil die CPU hat weniger bytes zu jonglieren, um... das sollte nur ein problem bei der Verarbeitung einer viel von Zeilen, so könnte es einen Versuch Wert.

    • Tatsächlich, mit einem LEFT join war die erste Technik, die ich versucht, allerdings hatten wir performance-Probleme, auch hier. Nach einiger Zeit auf die Lösung es hat sich gezeigt, dass wir tun müssen, um einige Optimierungen, die auf unseren Indizes - wenn die Tabellen indiziert wurden, besser ich denke diese Lösung wäre perfekt gewesen. Als Seite beachten, können Sie auf 'Priorität' in der ORDER BY - (ich konnte). Gruß, Zac
    • Ihr Recht. Es ist wahr für die GRUPPE DURCH, was die immer verwirrt mich.
    InformationsquelleAutor Philip Kelley
  3. 1

    Diese könnte entlang der Linien von, was Sie brauchen.

    Beachten Sie, dass hier davon ausgegangen, dass Sie über die Berechtigungen und die input-Daten wurden bereinigt.

    Vom Ausführen Gespeicherte Prozeduren Dynamische 

    CREATE PROCEDURE MyProc (@WHEREClause varchar(255))
AS

    -- Create a variable @SQLStatement
    DECLARE @SQLStatement varchar(255)

    -- Enter the dynamic SQL statement into the
    -- variable @SQLStatement
    SELECT @SQLStatement = "SELECT * FROM TableName WHERE " + @WHEREClause

    -- Execute the SQL statement
    EXEC(@SQLStatement)
    • Mein Verständnis ist, dass dieser verlieren würde, die Leistung, da wäre es kein Ausführungsplan. Kann das jemand bestätigen/verneinen/verbessern nach meinem Verständnis hier? Cheers.
    • Im nicht sicher, dass um ehrlich zu sein. [Anekdotische Evidenz 🙂 - ich habe es vor, unter bestimmten Bedingungen, die von buchstäblich Millionen von Zeilen und habe nicht bemerkt, jede parictular performance-Probleme.] Es sollte Recht einfach zu testen, obwohl die von Ihr beschriebene Szenario.
    • 4guysfromrolla.com/webtech/sqlguru/q120899-2.shtml - "Beginnend mit SQL 7.0, es ist ein handliches, kleines proc namens sp_executeSQL wird akzeptieren, dass parametrisierte Abfragen. Es wird auch versucht, die Wiederverwendung von Abfrageplänen, so dass, wenn Sie ausführen, die gleiche Anweisung mehrfach, Chancen sind Sie erhalten einen Abfrageplan wiederverwendet." offenbar werden Sie nur verlieren analysieren und compile-Zeit-performance.
    • Häufige Neukompilierungen würde sonst die performance leiden, aber die zusätzliche Zeit, die beteiligt sein könnten, minute (+12 ms, oder ähnliche) -- test und sehen, es hängt davon ab, wie Komplex die Abfrage ist. Und versuchen sp_executeSQL, wieder abhängig von der Komplexität der Abfrage kann es ermöglichen, für eine bessere Leistung.
    • Wir landeten mit einer anderen Lösung, aber deine Antworten sind gut zu wissen.
    InformationsquelleAutor bic
  4. 1

    Löste ich dieses problem durch die Verwendung eines CHARINDEX-Funktion. Ich wollte den string als einzigen parameter. Ich erstellt einen string mit führenden und nachgestellten Kommas für jeden Wert, den ich für den test wollte. Dann habe ich verkettet einer der führenden und nachgestellten Kommas, um die Zeichenfolge, die ich wollte sehen, ob war "in" - parameter. Am Ende habe ich überprüft, die CHARINDEX - > 0

    DECLARE @CTSPST_Profit_Centers VARCHAR (256) 
SELECT @CTSPST_Profit_Centers = ',CS5000U37Y,CS5000U48B,CS5000V68A,CS5000V69A,CS500IV69A,CS5000V70S,CS5000V79B,CS500IV79B,'

SELECT 
   CASE
       WHEN CHARINDEX(','+ISMAT.PROFIT_CENTER+',' ,@CTSPST_Profit_Centers) > 0 THEN 'CTSPST'  
       ELSE ISMAT.DESIGN_ID  + ' 1 CPG' 
      END AS DESIGN_ID

    Kann man es auch in der where-Klausel 

    WHERE CHARINDEX(','+ISMAT.PROFIT_CENTER+',',@CTSPST_Profit_Centers) > 0

    Wenn Sie versuchen zu vergleichen, die zahlen, die Sie bräuchte, um die Zahl zu konvertieren, um eine Zeichenfolge für die CHARINDEX-Funktion zu arbeiten.

    InformationsquelleAutor Gayle
Schreibe einen Kommentar