none
Wie kann man einen Spaltennamen als Parameter in eine SP übergeben? RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen und ein frohes neues Jahr,

    ich möchte eine Funktionalität bauen, mit der ich mit Hilfe einer StoredProcedure Werte in einer Tabelle ersetzen möchte, wobei u.a. die Spalte, in der die Werte ersetzt werden sollen, als Parameter übergeben werden soll, also in etwa so für einen Test:

    DECLARE @spalte NVARCHAR(max)
       ,@searchValue NVARCHAR(max)
       ,@updateValue NVARCHAR(max)
       ,@anz int OUTPUT

SET @spalte = '[Column x]'
SET @searchValue = 'abc'
SET @updateValue = '123'

UPDATE [TestTable]
SET @spalte = @updateValue
WHERE @spalte = @searchValue

    Dabei soll die Anzahl der Ersetzungen in der OUTPUT-Variablen '@anz' zurückgegeben werden.

    Kann mir hier einer "auf die Sprünge" helfen?

    Gruß Jürgen

    Dienstag, 1. Januar 2019 13:48
    |

Alle Antworten

  • Question
    Anmelden
    0
    Anmelden

    Hi Jürgen,
    mit einer einfachen SP sehe ich da keine Möglichkeit. Möglich ist das aber mit einer SP, die mit .Net CLR programmiert wird (Database Projekt). Da kannst Du das gesamte .Net-Instrumentarium nutzen.

    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks

    Dienstag, 1. Januar 2019 13:53
    |
  • Question
    Anmelden
    0
    Anmelden

    Und noch einfacher geht es mit dynamischem SQL und sp_executeSQL.
    https://docs.microsoft.com/de-de/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

    Den Spaltennamen gibst du im SQL dann direkt an, den SpaltenWert dann als Parameter.

    Dienstag, 1. Januar 2019 14:05
    |
  • Question
    Anmelden
    0
    Anmelden

    Hi,
    dann zeig mal ein konkretes Beispiel einer SP, in der über Parameter Spaltennamen ausgewählt bzw. in der Abfrage substituiert werden können.

    Hier mal eine Demo.

    Die CLR-Prozedur:

    using Microsoft.SqlServer.Server;
using System.Data;
using System.Data.SqlClient;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SqlStoredProcedure3 (string Spaltenname)
    {
    // CommandText aufbauen
    string cmdText = $"SELECT [{Spaltenname}] FROM TestTable1";
    // Rückgabe-Recordset
    SqlDataRecord record = new SqlDataRecord(
      new SqlMetaData(Spaltenname, SqlDbType.NVarChar, 50));
    // Ausgabe starten
    SqlContext.Pipe.SendResultsStart(record);
    using (SqlConnection cn = new SqlConnection("context connection=true;"))
    {
      cn.Open();
      using (SqlCommand cmd = new SqlCommand(cmdText,cn))
        using (SqlDataReader reader = cmd.ExecuteReader())
          // alle gefundenen Datensätz einlesen
          while (reader.Read())
          {
            record.SetSqlString(0, reader[0].ToString());
            SqlContext.Pipe.SendResultsRow(record);
          }
    }
    // Ausgabe abschließen
    SqlContext.Pipe.SendResultsEnd();
  }
}

    Dazu das Laden im Code (mit VB.NET):

      Private Function GetData() As DataTable
    Try
      Dim dt As New DataTable
      Using cn As New SqlConnection(My.Settings.cn)
        Using cmd As New SqlCommand("SqlStoredProcedure3", cn)
          cmd.CommandType = CommandType.StoredProcedure
          cmd.Parameters.Add("@Spaltenname", SqlDbType.NVarChar).Value = "Anrufer"
          cn.Open()
          Using dadp As New SqlDataAdapter(cmd)
            dadp.Fill(dt)
          End Using
        End Using
      End Using
      Using cn As New SqlConnection(My.Settings.cn)
        Using cmd As New SqlCommand("SqlStoredProcedure3", cn)
          cmd.CommandType = CommandType.StoredProcedure
          cmd.Parameters.Add("@Spaltenname", SqlDbType.NVarChar).Value = "Angerufener"
          cn.Open()
          Using dadp As New SqlDataAdapter(cmd)
            dadp.Fill(dt)
          End Using
        End Using
      End Using
      Return dt
    Catch ex As Exception
      MsgBox(ex.Message)
    End Try
    Return Nothing
  End Function

    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks


    Dienstag, 1. Januar 2019 14:35
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    zunächst danke für eure Hinweise.

    Wie ich daraus erkenne, kann man Spaltennamen nicht als Parameter übergeben???

    Gruß Jürgen

    Mittwoch, 2. Januar 2019 06:54
    |
  • Question
    Anmelden
    0
    Anmelden
    Hi Jürgen,
    eine Möglichkeit der Übergabe eines Spaltennamens habe ich Dir gezeigt. Dazu ist aber eine CLR Stored Procedure zu programmieren und im SQL Server zu veröffentlichen.

    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks

    Mittwoch, 2. Januar 2019 07:01
    |
  • Question
    Anmelden
    0
    Anmelden

    Also,

    ich habe die Lösung jetzt gefunden und zwar mit einer dynamischen SQL:

    DECLARE @spalte NVARCHAR(max)
       ,@searchValue NVARCHAR(max)
       ,@updateValue NVARCHAR(max)
       ,@query NVARCHAR(max)

SET @spalte = '[Column x]'
SET @searchValue = 'abc'
SET @updateValue = '123'

SET @query = N'
UPDATE [TestTable]
SET ' + @spalte + ' = ' + @updateValue + '
WHERE ' + @spalte + ' = ' + @searchValue + '
'

EXECUTE (@query)

    Danke


    Mittwoch, 2. Januar 2019 07:18
    |
  • Question
    Anmelden
    0
    Anmelden

    Das ist doch schön in der Doku beschrieben wie man mit Parametern umgeht:
    https://docs.microsoft.com/de-de/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017#arguments

    Ich habe ja nicht gesagt, dass der Spaltenname substituiert wird sondern der Variablen-Inhalt.
    Damit geht man der Problematik aus dem Weg:
    a) SQL-Zeichenfolge zu lang
    b) Hochkommata in Variableninhalten zu verdoppeln
    c) Typanpassung je nach Sprachumgebung (Datumformat,

    Mittwoch, 2. Januar 2019 07:34
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo Jürgen,

    bzgl. dynamischem SQL schau dir dann aber bitte auch mal diesen Artikel an:

      Dynamisches SQL : Fluch und Segen

    Der ist zwar schon recht alt, im Kern hat er aber immer seine Berechtigung. Insbesondere die Performance kann erheblich leiden, da u.U. keine oder nur schlechtere Optimierungsmöglichkeiten der SQL Engine genutzt werden.

    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET (2001-2018)
    https://www.asp-solutions.de/ - IT Beratung, Softwareentwicklung, Remotesupport

    Mittwoch, 2. Januar 2019 09:37
    |
    Moderator
  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen nochmal,

    danke für eure Hinweise und Verweise.

    @Stefan: In unserer Applikation werden Berechtigungen/Zugriffe auf Datenbankabfragen (StoredProcedures) über ein separates System geregelt, so dass es keine Berechtigungsprobleme gibt. Die Auswahl der Parameter wird über ein separates System validiert, so dass es nicht zu Fehlabfragen kommen kann.

    Für unsere Bedürfnisse haben wir eine flache Tabelle, in der die Werte abgerufen und ggf. analysiert werden müssen, so dass Joins so gut wie gar nicht vorkommen.

    Einziges Manko ist, dass diese Tabelle u.U. mehrere Milliarden Datensätze beinhalten kann/wird. Bei den Abfragen wird aber meist nur eine kleine Teilmenge abgerufen und analysiert, die in einer CTE in der SP gehalten wird und auf die die weiteren Anweisungen zugreifen.

    Für meine geschilderte Funktionalität sollen in verschiedenen Spalten mit String-Werten bestimmte Werte geändert werden können. Da es sich um ca. 40 Spalten handelt, in denen diese Ersetzungsfunktion greifen soll, will ich statt ca. 40 separate SP's nur eine SP mit dem Spaltennamen als Parameter verwenden. Da durch entsprechende Validierungsfunktionalitäten vor dem Aufruf der SP die von @bfuerchau genannten Probleme abgefangen werden, sehe ich kein Problem bei meiner Lösung.

    @Peter: Da deine Lösungsansätze immer auf einer CLR beruhen, ich deren Verwendung im Zusammenhang mit unserem Gesamtsystem (Client-IIS-DBServer) mit PRISM-Pattern noch nicht verstanden habe und ich unsere Applikation in 2 Monaten fertig haben muss, versuche ich zunächst alles mit SP's zu realisieren, da ich deren Verwendung in unserem Gesamtsystem nachvollziehen kann. Vielleicht sind deine Lösungsansätze ja performanter, aber an die Performance gehe ich erst, wenn alle Funktionalitäten "laufen" und die gewünschten Ergebnisse bringen.

    Gruß Jürgen

    Mittwoch, 2. Januar 2019 12:24
    |
  • Question
    Anmelden
    0
    Anmelden

    Um dem Performanceproblem aus dem Weg zu gehen, sollte man sich ständig wiederholende Statements per Prepare vordefinieren und dann nur noch ausführen.

    https://docs.microsoft.com/de-de/sql/relational-databases/system-stored-procedures/sp-prepare-transact-sql?view=sql-server-2017

    Das Prepare-Ergebnis (Int-Wert) kann ich innerhalb der Sitzung beliebig oft verwenden.

    Der Optimizer schlägt erst bei der Ausführung eines Statements zu und gilt somit auch für dynamische SQL's.
    Nur der Preparevorgang (Syntaxcheck) kommt halt dazu.
    Inzwischen sollte der SQL-Server aber gelernt haben, bereits kompilierte Prepared-Statements wieder zu erkennen um im Wiederholungsfall dann schneller zu werden.

    Allerdings frage ich mich, warum man für alles und jedes Prozeduren generieren muss, da doch die Programmiersprachen, mit denen man auf die DB zugreift, alle SQL's als dynamisch behandelt.
    Bei sinnvoller Verwendung von sog. Command-Objekten erübrigt sich das meiste nämlich.
    Mittwoch, 2. Januar 2019 12:56
    |
  • Question
    Anmelden
    0
    Anmelden

    Allerdings frage ich mich, warum man für alles und jedes Prozeduren generieren muss, da doch die Programmiersprachen, mit denen man auf die DB zugreift, alle SQL's als dynamisch behandelt.

    Dem kann ich nur zustimmen. Man braucht sich nur mal die vom EF generierten SQL Anweisungen anschauen.

    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks

    Mittwoch, 2. Januar 2019 16:17
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    das mit dem Fluch und Segen trifft es schon. Doch der Artikel ist ein wenig betagt. 
    Zunächst ein Beitrag von Christoph Muthmann:
    http://www.insidesql.org/blogs/cmu/sql_server/parameter-sniffing-und-trace-flag-4136

    Noch aktuellere Meinungen:

    https://www.db-berater.de/2018/08/flexible-parameterbergabe-als-filterkriterien-fr-dynamisches-sql-in-stored-procedures/
    oder 
    https://www.db-berater.de/2016/09/parameter-sniffing-loesungsansaetze/

    oder
    https://www.sqlskills.com/blogs/kimberly/high-performance-procedures/


    Jürgen, das mit dem Option(Recompile) hat es in sich. Beachte das Verhalten bei der jeweiligen SQL Server Version.
    HTH

    Grüße Alexander

    Mittwoch, 2. Januar 2019 22:23
    |
  • Question
    Anmelden
    0
    Anmelden

    Jetzt verstehe ich auch, warum der Optimizer des SQL-Servers im Vergleich zu anderen DB's so schlecht abschneidet.
    Eine Optimierung der Abfrage vom Parameterinhalt abhängig zu machen ist tatsächlch grenzwertig.
    Eine Optimierung sollt von der Art der Abfrage und den verfügbaren Indizes ausgehen.

    Warum sollte eine Abfrage anders optimiert werden bei "Where X = 1" als bei "where X = 5000"?
    Wenn es einen Index für X gibt, kann der doch unabhängig vom Inhalt des Parameters genommen werden?

    Auch die Option(Recompile) erklärt natürlich einiges, da ja der Optimierungsvorgang bei jedem erneuten Aufruf wiederholt werden muss, ob wohl die Art einen Statements und der Zugriffspfad schon längst definiert sein sollten.

    Das Parametersniffing sollte da wohl am besten immer abgeschaltet sein und den Recompile müsste man sich dann ja sparen können.

    Von anderen DB's kenne ich es, dass selbst bei einem "Where X = 1" der Optimizer einen "Where X = ?" daraus macht, dann optimiert und zur Ausführungszeit dann das "?" wie bei einem manuellen Parameter mit dem Wert ersetzt.

    Donnerstag, 3. Januar 2019 08:22
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo,

    Die Artikel beschreiben das es nicht grenzwertig, sondern die vernünftigere Alternative ist.

    Besonders Kimberly Tripp beschreibt doch wieso dem so ist. Du solltest dem Glauben schenken. 
    Und welche Datenbanken genau meinst Du?

    Grüße Alexander

    Donnerstag, 3. Januar 2019 09:07
    |
  • Question
    Anmelden
    0
    Anmelden

    DB2, Oracel, Firebird.

    Was ich dem Artikel entnehme ist:
    Alle Prozeduren mit Option(Recomile) auszuführen und das Parametersniffing abzuschalten.

    Nun arbeite ich eher selten mit Prozeduren, sondern mit den Frameworks der Programmiersprachen (wie oben bereits erwähnt).
    So nehme ich ein Commandobjekt mit z.B. "Select * from MyTable where Key=?".
    Dieses wird i.d.R. beim 1. Aufruf prepared, so dass es bei jedem neuen Aufruf nicht neu analysiert und optimiert werden muss.
    Da es hier kein Recompile gibt, müsste ich also analog dem Blog jedes mal ein neues Commandobjekt erstellen um einen Recompile/Prepare und somit eine neue Optimierung zu erzwingen.
    Wobei dies u.U. der längere Teil der eigentlichen Ausführung des Queries bedeutet.

    Dieses Verfahren widerspricht jedweder Erfahrung bzgl. der Performance o.g. Datenbanken, da somit nur wenige 100 Queries/Minute ausgeführt werden können.
    Ich spreche aber auch von Systemen mit mehreren 1000 Queries/Minute ohne deshalb mehrere Cluster einsetzen zu müssen.

    Desweiteren erklärt mir der Blog allerdings nun indirekt die Aussage eines Kunden, dass seine Software nach Umstellung auf Firebird bzgl.der SQL-Zugriffe z.T. mit Faktor 100 schneller ist.

    Donnerstag, 3. Januar 2019 10:10
    |
  • Question
    Anmelden
    0
    Anmelden

    Allerdings frage ich mich, warum man für alles und jedes Prozeduren generieren muss, da doch die Programmiersprachen, mit denen man auf die DB zugreift, alle SQL's als dynamisch behandelt.

    Dem kann ich nur zustimmen. Man braucht sich nur mal die vom EF generierten SQL Anweisungen anschauen.

    --
    Viele Grüsse
    Peter Fleischer (ehem. MVP für Developer Technologies)
    Meine Homepage mit Tipps und Tricks

    Hallo nochmal,

    aber hier scheint einiges aus dem "Ruder" zu laufen. Wir greifen nicht mit dem EF auf die DB zu (wir dürfen nicht!)

    Ich hatte doch geschrieben, dass wir über einen ApplicationServer (IIS) die Anfragen per JSON an die DB durchreichen. Außerdem steht unser DB-Server in einer DMZ, zu der nur ein sehr begrenzter Userkreis Zugriff hat.

    Deshalb verstehe ich diese ganze Diskussion nicht, vielleicht fehlen mir auch noch massive Kenntnisse in SQL und MS-SQL-Server. Ich bin froh, wenn ich eine Funktionalität "am Laufen" habe, denn ich bin bei uns z.Zt. Einzelkämpfer, d.h. ich muss die SQL's, die komplette "Machine" und die Client-Views/ViewModels implementieren. Das bedeutet, dass ich für eine einzige SQL-Abfrage ca. 13 Schritte in der Machine und mind. 1 View mit ViewModel und Controller implementieren muss, damit das Ergebnis endlich in einer View auf dem Client angezeigt werden kann (Mittlerweile habe ich über 120 SP's).

    Insofern bin ich unheimlich dankbar dafür, dass ich hier im Forum qualifizierte Hilfe und Lösungsansätze bekomme. Daher nochmal danke an alle, die sich mit meinen Problemen beschäftigen.

    Gruß Jürgen

    Donnerstag, 3. Januar 2019 11:36
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo 

    DB2 ist lange her, Firebird kenne ich nicht.

    zu Oracle: Hier hast Du den Zustand das erst "vor kurzem"  Rule Based abgeschafft wurde. 
    Bis vor kurzem konnte Rule Based und Cost Based betrieben werden. Damit sind Statistiken, solange Rule Based greift, außen vor und die Statistiken sind mit der Grund für das ganze. 

    Das Problem des Parameter Sniffings existiert aber dennoch auch in ORACLE.

    Siehe: https://nenadnoveljic.com/blog/bind-variable-peeking-parameter-sniffing/

    Der Artikel von Christoph erklärt doch ganz gut was passiert wenn sich die Datenlage ändert.
    Das trifft natürlich in Cost-Based basierten Umgebungen zu.

     

    Grüße Alexander

    Donnerstag, 3. Januar 2019 12:28
    |
  • Question
    Anmelden
    0
    Anmelden

    Was heißt schon "Datenlage ändert sich"?

    Ein Suchverfahren über Index ist unabhängig von der Anzahl Sätze und Schlüssel.

    Wenn also eine Bedingung "X=? and Y=?" vorliegt und ein Index über X+Y vorliegt, so ist dies der performanteste Weg. Über die Art des Index muss man nicht diskutieren, da hat jeder so seine Vorlieben (Binär, Vector, Hash, Map, ...).
    Da helfen auch keine Statistiken über Anzahl Schlüssel u.ä., ins besonders wenn ich sie selber auch noch aktualisieren muss damit sie vielleicht verwendet werden.

    Ich wollte damit ja auch nur aussagen, dass sich somit das Verhalten eines SQL-Servers erklärt.
    An dieses Spezialwissen für die Optimierung kommt man, wie in diesem Fall, nur per Zufall, da dies ja nirgends offiziell dokumentiert wird.
    Wenn das Abschalten des Parametersniffing sich doch als Vorteil erwiesen hat kann der SQL-Server doch dies als Defaultverfahren nutzen. Und schon werden die Probleme geringer.

    Ich wäre nun auch nicht auf die Idee gekommen, überhaupt nach einem Begriff wie "Parametersniffing" zu suchen.

    Donnerstag, 3. Januar 2019 12:46
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo, 
    die Effizienz des gewählten Suchverfahrens hängt sicher von den Inhalten der Daten ab. 
    Wenn eine Bedingung "X=? und Y=?" vorliegt und der Index über X+Y vorliegt, so kann aufgrund der Datenlage ein TableScan effizienter sein.
    Grüße Alexander

    Freitag, 4. Januar 2019 06:57
    |
  • Question
    Anmelden
    0
    Anmelden

    Was heißt schon "Datenlage ändert sich"?

    Ein Suchverfahren über Index ist unabhängig von der Anzahl Sätze und Schlüssel.

    Wenn also eine Bedingung "X=? and Y=?" vorliegt und ein Index über X+Y vorliegt, so ist dies der performanteste Weg.


    Dann lies mal https://www.sqlskills.com/blogs/kimberly/category/the-tipping-point/

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Freitag, 4. Januar 2019 07:33
    |
  • Question
    Anmelden
    0
    Anmelden
    Die Aussage (Wenn also eine Bedingung "X=? and Y=?" vorliegt und ein Index über X+Y vorliegt, so ist dies der performanteste Weg. ) trifft höchstens für einen gruppierten Index (Clustered Index) zu. Bei nicht gruppierten Indizes kann sich eine Verteilung über die Pages der Tabelle ergeben, die einen Tablescan effektiver erscheinen lassen. Ziel ist immer die Minimierung des IO. 

    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    Freitag, 4. Januar 2019 08:00
    |
    Beantworter
  • Question
    Anmelden
    0
    Anmelden

    Da per Definition ja nur ein gruppierter Index (u.U. der PrimaryKey) zulässig ist, erübrigt sich i.W. die Frage nach dem Typ des Index, da alle anderen Indizes nie gruppiert sein können.
    Was den IO angeht, so hängt es wiederum davon ab, welche zusätzlichen Felder in der Where-Klausel stecken, die nicht im Index stecken. Dann ist sicherlich im 2. Schritt ein Zugriff auf die Datenzeilen notwendig.

    Allerdings hat auch der gruppierte Index so seine Tücken.
    Auf Grund der inkludierten Datenzeile sind antürlich in einer DB-Seite weniger Schlüssel enthalten, als in einem nicht gruppierten Index. Somit können im gruppierten Index doch wiederum mehr IO's erforderlich sein als beim nicht gruppierten Index. Was ins besonders dann kritisch ist, wenn die Tabelle weit gestreut ist.
    Bei vielen parallelen Abfragen auf unterschiedliche Tabellen spielt dies dann wiederum keine Rolle.

    Hinzu kommt noch die Möglichkeit einer Index-Only-Abfrage.
    D.h., ich kann dem Index weitere Spalten hinzufügen, die aber nicht Bestandteil des Indexbaumes sind.
    Trotzdem beschränkt sich die Suche dann ausschließlich auf den Index.
    Ein Vorteil wenn ich mit Like, o.ä., Suchfunktionen arbeite.

    Was die IO-Last selber angeht, so hängt es von der Verwendung des DB-Caches ab ob häufig verwendete Indexseiten nicht sowieso bereits im Speicher liegen und kein IO erforderlich ist, während ein häufiger Tablescan eher zu einm Verdrängungswettbewerb der Indexseiten führt.

    I.Ü. kann auch ein Virtualisierungshost mit großem Speicher die Speicherengpässe des eingebetteten SQL-Servers entschärfen, da die virtuelle Disk als File im Filecache des Hosts landet und auch somit weniger physische IO's erforderlich sind.
    Dies habe ich mal mit einm sog. Diskbenchmark in der VM geprüft. Das Ergebnis bescheinigte der virtuellen Disk Übertragungsraten bis 2GByte/Sekunde, während die Festplatte tatsächlich nur 300MByte/Sek. hat.

    Freitag, 4. Januar 2019 08:46
    |