Glengamoi (Forum) · AspHeute · .NET Heute (RSS-Suche) · AspxFiles (Wiki) · .NET Blogs

Highspeed-Abfragen einer SQL Server Datenbank

Geschrieben von: Christian Koller
Kategorie: Datenbank

This printed page brought to you by AlphaSierraPapa

Heute geht es ans Eingemachte: High-Speed Abfragen aus einer SQL Server 7.0 Datenbank steht am Programm. Das ist nichts für Leute, die kleine ASP Sites erstellen, sonderen mehr etwas für den Hardcore-Datenbankfreak. Aber auch für ASP-Programmierer, die in Performance-Tuning von Datenbankabfragen Einblick gewinnen möchten, oder Leute deren tägliches Brot das Erstellen von ASP-Websites mit Datenbankunterstützung für tausende Zugriffen pro Tag ist, werden in diesem Artikel einige interessante Anregungen finden.

Ganz nebenbei möchte ich eine kurze Einführung in die wirklich spezielle Stored Procedure des SQL Server Namens sp_executesql geben, die das extrem schnelle Ausführen von parametrisierten Abfragen erlaubt, ohne für jede Abfrage eine neue Stored Procedure erstellen zu müssen. Das Verwenden dieser Stored Procedure ist noch wesentlich schneller als das Verwenden sogenannter "Prepared Statements" in ADO alleine. Daneben möchte ich betonen, daß in diesem Artikel die wahrscheinlich weltweit erste Verwendung der sp_executesql Stored Procedure beschrieben ist, die parametrisierte SQL Statements mit Rückgabewerten (Output Parameters) verbindet!

Das einzig effektive Mittel um zu erkennen, ob eine bestimmte Art der Datenbankabfrage schneller ist als eine andere, ist die Stoppuhr. Datenbankabfragen unter ASP und SQL Server 7.0 benötigen jedoch nur wenige Millisekunden, daher ist unter ASP der Einsatz einer Komponente zur Zeitmessung angebracht. Ich habe für alle Messungen dieses Artikels wieder die ASP Profiling Component meines geschätzten Kollegen Christoph Wille benutzt um die Ausführungsgeschwindigkeit der einzelnen Skripts und Abfragen zu ermitteln. Außerdem war das unbestechliche "Ticken" dieser elektronischen Stoppuhr eine wertvolle Hilfe beim Tuning einzelner Abfrageteile.

Alle gemessenen Abfragen in diesem Artikel sind hochoptimiert. So verschieden auch die Ansätze der Abfragen sein mögen, jede einzelne Abfrage wurde so programmiert, daß sie so schnell als möglich ausgeführt wird. Stets wurden die schnellsten Recordset Cursortypes oder die schnellsten Abfragen von Feld- und Parameterwerten benutzt.

Alle Messungen erfolgten auf einem Webserver der folgenden Konfiguration:

Prozessor: AMD Athlon 750 Mhz
Motherboard: Asus K7M/A
Ram: 256 MB (Cycle 322, 7ns)
RAID 0 (Promise Fasttrak 66 mit 2x IBM 20.5 GB 7200 U/min)
Microsoft Windows 2000 Server
Microsoft SQL Server 7.0
Microsoft Internet Explorer 5.5

Genug der vielen Worte, zuerst eine kleine Einführung in Performance Tuning für ADO.

Recordset und Connection Performance Tuning

Um die Geschwindigkeit einer Datenbankabfrage in einem ASP Skripts zu erhöhen, gilt es das verwendete Recordset optimal zu konfigurieren und einzusetzen.

Server.CreateObject statt CreateObject

Führen wir doch einmal eine Datenbankabfrage in ASP aus, die Daten aus der Orders Tabelle der SQL Server 7.0 Northwind Datenbank liest.

Hier die einfache Version, bei der ein Recordset geöffnet und die Werte gelesen werden (Datei 20001013_RecordsetEinfach.asp im Download):

<%
   strServer = "Thunder"
   strDatabase = "Northwind"
   strUserName = "sa"
   strPassword = ""
   
   strConnection = "Provider=SQLOLEDB;Data Source=" & strServer & _
   ";" & "Initial Catalog=" & strDatabase & ";User ID=" & _
   strUserName & ";" & "Password=" & strPassword & ";"

   ' Definiere SQL Statement:
   strSQL = "SELECT CustomerID, EmployeeID, OrderDate, " & _
            "ShipName, ShipCountry FROM Orders " & _ 
          "WHERE OrderID=10555"

   Set rs = CreateObject("ADODB.Recordset")
   rs.open strSQL, strConnection
   Response.Write("CustomerID: "  & rs("CustomerID") & "<BR>")
   Response.Write("EmployeeID: "  & rs("EmployeeID") & "<BR>")
   Response.Write("OrderDate: "   & rs("OrderDate") & "<BR>")
   Response.Write("ShipName: "    & rs("ShipName") & "<BR>")
   Response.Write("ShipCountry: " & rs("ShipCountry") & "<BR>")
   rs.close   
   Set rs = Nothing
%>

Dieses Listing benötigt auf meinem Server im Mittel etwa 2.6 ms zum Ausführen.

Hier die optimierte Version (Datei 20001013_RecordsetOptimiert.asp):

<%
   strServer = "Thunder"
   strDatabase = "Northwind"
   strUserName = "sa"
   strPassword = ""
   
   strConnection = "Provider=SQLOLEDB;Data Source=" & strServer & _
      ";" & "Initial Catalog=" & strDatabase & ";User ID=" & _
      strUserName & ";" & "Password=" & strPassword & ";"

   ' Definiere SQL Statement:
   strSQL = "SELECT CustomerID, EmployeeID, OrderDate, " & _
      "ShipName, ShipCountry FROM Orders " & _ 
      "WHERE OrderID=10555"

   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.open strSQL, strConnection
   Response.Write("CustomerID: "  & rs(0) & "<BR>")
   Response.Write("EmployeeID: "  & rs(1) & "<BR>")
   Response.Write("OrderDate: "   & rs(2) & "<BR>")
   Response.Write("ShipName: "    & rs(3) & "<BR>")
   Response.Write("ShipCountry: " & rs(4) & "<BR>")
   rs.close   
   Set rs = Nothing
%>

Der größte Gewinn ist dadurch zu verzeichnen, daß anstatt CreateObject("ADODB.Recordset") nun Server.CreateObject("ADODB.Recordset") verwendet wird. Daneben ist die Abfrage von Feldwerten mittels Index wie in rs(0) schneller als mittels Feldnamen. Die Zeiten zum instanzieren und verwerfen (mittels Set Object = Nothing) des Recordset Objektes sind wie folgt:

Server.CreateObject("ADODB.Recordset"): 0.363 ms

CreateObject("ADODB.Recordset"): 0.693 ms

Empfehlung zur Verwendung von Server.CreateObject() anstatt von CreateObject() in ASP Seiten siehe auch Microsoft Webworkshop Artikel "ASP Guidelines".

Im vorhergehenden Beispielskript wird kein extra Connection Objekt zum Herstellen der Datenbank-Verbindung instanziert. Genauer gesagt, das Skript instanziert kein Connection Object, intern erstellt ASP sehr wohl ein Connection Objekt beim Aufruf des Befehles rs.open strSQL, strConnection. Meinen Messungen zufolge ist das explizite Instanzieren und öffnen einer Datenbankverbindung mittels Connection Objekt nur dann sinnvoll, wenn die Verbindung für mehr als ein Recordset benötigt wird. Das einfache Skript zum Auslesen eines Recordsets wird zum Beispiel durch das explizite Instanzieren und Öffnen einer Connection um etwa 10% langsamer (2.95 ms zu 2.72 ms).

Parameter des Recordset Objektes

Speziell beim Auslesen großer Datenmengen ist es wichtig, daß die Recordsetparameter den Anforderungen gemäß gesetzt werden. Will man zum Beispiel eine große Anzahl von Datensätzen aus einer SQL Server Datenbank nur lesen und direkt zum Browser schreiben, so empfehlen sich die folgenden Recordset Einstellungen:

 CursorLocation = adUseServer
 CursorType     = adOpenForwardOnly 
 LockType       = adLockReadOnly
 CursorLocation = adUseServer
 CacheSize      = Je nach Webserver und Datenbank-Verbindung

Wenn man einige dutzende Datensätze aus einem Recordset ausliest, sollte die Performancesteigerung dadurch bei etwa 10% liegen.

adExecuteNoRecords Parameter einsetzen

Führt man Datenbankoperationen aus, bei denen kein Recordset benötigt wird, so empfiehlt sich bei Verwendung der Connection.Execute Methode der Einsatz des Parameters adExecuteNoRecords (Wert 128). Einsatzmöglichkeiten sind das Ausführen eines UPDATE oder INSERT Statements, oder die Ausführung der Command.Execute Methode bei einem Command Objekt das kein Recordset zurückgibt.

connection.Execute strSQL, , adExecuteNoRecords

command.Execute strSQL, , adExecuteNoRecords

Durch den Einsatz des adExecuteNoRecords Parameters werden Aktionen bis zu 30% schneller ausgeführt, das ADO kein temporäres Recordset Objekt instanziert.

Den adExecuteNoRecords Parameter kann man bei den folgenden Methoden benutzen: Connection.Execute, Command.Execute.

Ein direktes Zuweisen des adExecuteNoRecords Parameters zur CommandType Eigenschaft des Command Objektes ist nicht möglich und wird von ADO mit einem Laufzeitfehler quittiert.

Wenden wir uns jetzt den Bereichen zu, die den größten Einfluß auf die Geschwindigkeit und Performance einer Datenbankabfrage haben: Die unterschiedlichen Möglichkeiten einer Abfrage.

Unterschiedliche Ansätze von Datenbankabfragen

Ich möchte hier an Hand des folgenden Beispiels unterschiedliche Arten von Datenbankabfragen und die jeweilige Performance diskutieren: Es soll aus der SQL Server 7.0 Northwind Datenbank ein einzelner Datensatz aus der Orders Tabelle zurückgegeben werden.

Unter ADO und SQL Server hat man die unterschiedlichsten Möglichkeiten, eine Datenbankabfrage durchzuführen, die als Ergebnis einen einzelnen Datensatz zurückliefert:

Ausführen eines SELECT Statements: Dabei wird beim Öffnen eines Recordsets ein SQL SELECT Statement ausgeführt und die Werte des zurückgelieferten Datensatzes aus den Feldern des Recordset Objektes ausgelesen.
Verwenden einer STORED PROCEDURE und Rückgabe als Recordset: Mittels Command Object wird eine (am SQL Server definierte) STORED PROCEDURE ausgeführt. Der Ergebnisdatensatz wird an ein Recordset übergeben und von dort ausgelesen.
Einsatz einer STORED PROCEDURE mit OUPUT Parameter: Der Datensatz wird nicht als Recordset sondern in Form von OUTPUT Parameter über das Command Objekt zurückgeliefert.
Aufrufen der vordefinierten STORED PROCEDURE sp_executesql: Es wird ein parametrisiertes SQL Statement sowie eine Parameterliste an die Stored Prodcedure sp_executesql übergeben. Die zurückgelieferten Werte werden wieder über OUTPUT Parameter des Command Objektes ausgelesen.

Daneben gibt es noch andere Möglichkeiten, die hauptsächlich aus dem Kombinieren dieser Ansätze bestehen.

Untersuchen wir die einzelnen Ansätze einmal etwas genauer.

Ausführen eines SELECT Statements

Um einen einzelnen Datensatz aus der Orders Tabelle zu erhalten, wird einfach das folgende SQL SELECT Statement durchgeführt:

SELECT CustomerID, EmployeeID, OrderDate, ShipName, ShipCountry
FROM Orders WHERE OrderID = 10555

Das vollständige Listing finden Sie im Download zum Artikel unter dem Namen 20001013_SELECT.asp.

Ich habe dieses Listing noch leicht optimiert und mit Hilfe der Profiler Komponente durchgemessen. Das Listing, welches ich zum Messen benutzt habe, ist dem Download unter dem Namen 20001013_Speed_SELECT.asp beigefügt.

Die Messergebnisse dieses Listings für die einzelnen Schritte der Datenbankoperation sind wie folgt:

Erster Start:

Connection Öffnen: 1.50 ms

Erstellen des Recordsets: 0.35 ms

Öffnen des Recordsets: 1.70 ms

Auslesen der Werte des Recordsets: 0.050 ms

Gesamt: 3.60 ms

Durchschnitt:

Connection Öffnen: 0.70 ms

Erstellen des Recordsets: 0.08 ms

Öffnen des Recordsets: 0.83 ms

Auslesen der Werte des Recordsets: 0.044 ms

Gesamt: 1.65 ms

Die Unterschiede zwischen dem allerersten Aufruf und den folgenden Aufrufen, immerhin ein Faktor 2, ergibt sich daraus, daß erst ein Connection Objekt angelegt werden muß und der SQL Server für das SELECT Kommando einen sogenannten "Execution Plan" (Ausführungsplan) erstellt. Bei den weiteren Ausführungen des Skripts greift das ASP Skript auf den Connection Pool zurück, der die Wiederverwendung von ADO Connections erlaubt. Am SQL Server kann das SELECT Statement schneller ausgeführt werden, da im Execution Plan bereits gespeichert ist, wie die Tabellen und Felder abzufragen sind um das Abfrageergebnis so rasch als möglich zu erhalten.

Sehen wir uns zum Vergleich ein Skript an, das die selbe Abfrage über eine Stored Procedure ausführt.

Stored Procedure

Eine Stored Procedure einer SQL Server Datenbank ist ein Programm, das eine vordefinierte Datenbankabfrage durchführt. Die für die Stored Procedure verwendete Programmiersprache ist T-SQL, der Microsoft SQL Server Dialekt für SQL (Structured Query Language).

Ich habe die folgende Stored Procedure namens sp_TEST_SELECT erstellt, die nichts anderes tut als das SELECT Statement auszuführen und das Ergebnis als Recordset zurückzuliefern. Die Stored Procedure ist unter sp_TEST_SELECT.sql im Download zu finden.

Bild 1: Stored Procedure sp_TEST_SELECT

Sobald die Stored Procedure sp_TEST_SELECT zur Northwind Datenbank mittels SQL Server 7.0 Enterprise Manager hinzugefügt worden ist, kann man die Stored Procedure mittels ADO aus einer ASP Seite heraus aufrufen.

Das Skript 20001013_StoredProcedure.asp benutzt ein Command Objekt als Schnittstelle zur Stored Procedure. Da die Stored Procedure einen Eingangsparameter (@paramOrderID vom SQL Server Datentyp INT) erwartet, wird dieser auch im Command Objekt definiert.

Hier ist der wichtigste Teil des ASP Skripts:

   ...
   Set cmd = Server.CreateObject("ADODB.Command")
   ' Stored Procedure des SQL Server 7.0
   cmd.CommandText = "sp_TEST_SELECT"
   cmd.CommandType = adCmdStoredProc 
   Set cmd.ActiveConnection = conn
   
   ' Werte des InputParameter:
   lngParamOrderID = 10555

   ' Input Parameter uebergeben:
   Set tmpParam = cmd.CreateParameter("@paramOrderID", adInteger, adParamInput, _
                                     4, lngParamOrderID)
   cmd.Parameters.Append tmpParam
   
   ' Fuehre Command Objekt aus
   Set rs = cmd.Execute
   ...

Das Command Objekt wird mittels Server.CreateObject und der ProgID ADODB.Command instanziert. Der Name der aufzurufenenden Stored Procedure wird der CommandText Eigenschaft zugewiesen. Als "CommandType" wird eine Stored Procedure (adCmdStoredProc) angegeben.

Mittels der Anweisung Set cmd.ActiveConnection = conn wird eine geöffnete Datenbankverbindung in Form eines Connection Objektes an das Command Objekt übergeben. Der Input Parameter wird als Parameter Objekt mittels CreateParameter Methode erzeugt und dem Command Objekt mittels Append hinzugefügt.

Das Ausführen der Stored Procedure erfolgt schließlich durch aufrufen der Execute Methode des Command Objektes. Das in der Stored Procedure erzeugte Recordset wird als Rückgabewert der Execute Methode der Variable rs übergeben.

Aus dem Recordset rs kann man dann die Werte des mittels SELECT Abfrage in der Stored Procedure ausgelesenen Datensatzes abfragen.

Die Geschwindigkeitsmessung der optimierten Fassung dieses Listings (20001013_Speed_StoredProcedure.asp) mittels Profiler Komponente hat die folgenden Werte ergeben:

Erster Start:

Connection Öffnen: 1.50 ms

Erstellen des Command Objektes: 0.78 ms

Öffnen des Recordsets mit Command als Source: 1.48 ms

Auslesen der Werte des Recordsets: 0.050 ms

Gesamt: 3.81 ms

Durchschnitt:

Connection Öffnen: 0.70 ms

Erstellen des Command Objektes: 0.62 ms

Öffnen des Recordsets mit Command als Source: 0.93 ms

Auslesen der Werte des Recordsets: 0.044 ms

Gesamt: 2.29 ms

Bei einem Vergleich der Zeiten der Stored Procedure mit dem einfachen Ausführen eines SELECT Statements fällt auf, daß es durch die Bank langsamer ist. Sowohl das Erstellen des Command Objektes als auch die Übergabe des Command Objektes an das Recordsets beim Ausführen von Recordset.Open kosten Zeit.

Im Allgemeinen kann man sagen, daß das einfache Ausführen einer Stored Procedure nur dann schneller ist als die direkte Ausführen des SQL Kommandos, wenn eine komplexe Abfrage (SELECT mit Subqueries) oder viele Ähnliche Abfragen hintereinander auszuführen sind. Beim Ausführen von ähnlichen Abfragen sollte man nicht vergessen die Prepared Eigenschaft des Command Objektes auf True zu setzen, andernfalls wird das Ausführen der Stored Procedure kaum schneller sein als ein mittels Connection.Execute ausgeführtes SELECT, INSERT oder UPDATE Kommando.

Stored Procedure mit Output Parameter

Wenn man nur einen Datensatz von der Stored Procedure zurück bekommt, so kann man einiges an Performance gewinnen, indem man die Werte des Datensatzes nicht als Recordset, sondern in OUTPUT Parameter der Stored Procedure an das Command Objekt übergibt. Dadurch erspart man sich das instanzieren und die Verwendung des Recordset Objektes.

Eine Stored Procedure mit Output Parameter definiert man zum Beispiel wie folgt (sp_TEST_SELECT_OUTPUTPARAMETERS.sql):

Bild 2: Stored Procedure sp_TEST_SELECT_OUTPUTPARAMETERS

Die Outputparameter in der Stored Procedure müssen mit dem Keyword OUTPUT gekennzeichnet werden. Um die Rückgabewerte des SELECT Statements in die OUTPUT Parameter zu übergeben, muß man im SELECT Statement die Spaltennamen den Parameternamen zuweisen:

 SELECT @CustomerID = CustomerID, @EmployeeID = EmployeeID, ...

Um nun die Stored Procedure auszuführen und die von der Stored Procedure gelieferten Output Parameter über das Command Objekt abzufragen, geht man wie folgt vor (siehe auch 20001013_OutputParameter.asp im Download):

   ...
   Set cmd = CreateObject("ADODB.Command")
   ' Spezielle Stored Procedure des SQL Server 7.0
   cmd.CommandText = "sp_TEST_SELECT_OUTPUTPARAMETERS"
   cmd.CommandType = adCmdStoredProc 
   Set cmd.ActiveConnection = conn
   
   ' Werte des InputParameter:
   lngParamOrderID = 10555


   ' Definiere ADO Parameter Objekte und fuege Sie zu Command Objekt hinzu
   ' Input Parameter uebergeben:
   Set tmpParam = cmd.CreateParameter("@paramOrderID", adInteger, _
                                      adParamInput, 4, lngParamOrderID)
   cmd.Parameters.Append tmpParam
   
   ' Output Parameter definieren:
   Set tmpParam = cmd.CreateParameter("@CustomerID", adWChar, adParamOutput, 5)
   cmd.Parameters.Append tmpParam

   Set tmpParam = cmd.CreateParameter("@EmployeeID", adInteger, adParamOutput, 4)
   cmd.Parameters.Append tmpParam
   
   Set tmpParam = cmd.CreateParameter("@OrderDate", adDate, adParamOutput, 8)
   cmd.Parameters.Append tmpParam

   Set tmpParam = cmd.CreateParameter("@ShipName", adWChar, adParamOutput, 40)
   cmd.Parameters.Append tmpParam
   
   Set tmpParam = cmd.CreateParameter("@ShipCountry", adWChar, adParamOutput, 15)
   cmd.Parameters.Append tmpParam
   
   ' Command ausfuehren
   cmd.Execute

   ' Output Parameter auslesen
   strCustomerID = cmd.Parameters("@CustomerID").Value 
   intEmployeeID = cmd.Parameters("@EmployeeID").Value 
   dateOrderDate = cmd.Parameters("@OrderDate").Value 
   strShipName = cmd.Parameters("@ShipName").Value
   strShipCountry = cmd.Parameters("@ShipCountry").Value
   ...

OUTPUT Parameter für das Command Object werden öähnlich wie INPUT Parameter definiert. Jedoch wird als "Direction" (Übergaberichtung) adParamOutput anstatt adParamInput angegeben.

Der Syntax der CreateParameter Anweisung ist wie folgt:

command.CreateParameter(Name, Type, Direction, Size, Value)

Name bezeichnet dabei den Namen, unter dem man den Parameter in der Parameters Collection des Command Objektes ansprechen kann. Er muß nicht zwangsläufig mit dem echten Namen des Parameters in der Stored Procedure ident sein.

Type gibt den Datentyp des Output Parameters an. Für eine vollsändige Liste konsultieren Sie bitte das Dokument DataTypeEnum der MSDN.

Direction ist je nach Übergaberichtung des Parameters entweder adParamInput, adParamOutput oder adParamInputOutput.

Size schließlich gibt die Größe des Datentyps bzw. des übergebenen Wertes an.

Nachdem das Command mittels Execute Methode ausgeführt wurde, kann man die Output Parameter auslesen: cmd.Parameters(Name).Value oder kurz cmd(Name).

Die Messung der Verarbeitungszeiten hat für das ASP Skript (mit 20001013_Speed_OutputParameter.asp) bei Übergabe der Werte in Output Parametern (statt in einem Recordset) die folgenden Zeiten ergeben:

Erster Start:

Connection Öffnen: 1.50 ms

Erstellen des Command Objektes: 0.78 ms

Ausführen des Commands: 0.91 ms

Auslesen der Werte des Recordsets: 0.050 ms

Gesamt: 3.24 ms

Durchschnitt:

Connection Öffnen: 0.70 ms

Erstellen des Command Objektes: 0.62 ms

Ausführen des Commands: 0.64 ms

Auslesen der Werte des Recordsets: 0.035 ms

Gesamt: 1.99 ms

Im Vergleich zum Ausführen eines SQL Statements mittels Connection.Execute ist die Verwendung einer Stored Procedure mit Output Parameter dann zu empfehlen, wenn das Command Object für mehrere Abfragen verwendet wird. Ansonsten kommt die Methode mit der Stored Procedure und den Ouput Parametern bei einer einzelnen Abfrage zeitlich in die Nähe der Ausführung eines einfachen SELECT Statements. Speziell bei komplexeren Abfragen mit mehreren Input Parametern sollte die Verwendung einer Stored Procedure mit Input- und Output Parametern schon bei einer einzelnen Ausführung ohne Wiederverwendung des Command Objektes signifikant schneller sein.

Schließlich möchte ich noch eine etwas abgehobene Methode aufzeigen, wie man die System Stored Procedure sp_executesql des SQL Server 7.0 dazu benutzen kann, um eine parametrisierte Abfrage mit Input und Output Parametern bei nahezu unglaublicher Geschwindigkeit auszuführen.

sp_executesql mit Ouput Parameter

Die Stored Procedure sp_executesql kann man wie folgt einsetzen um beliebige parametrisierte Abfragen zu berechnen:

sp_executesql @stmt = SQL_Statement 
    @params =  N'@parameter_name1  data_type, @parameter_name2  data_type... ',
@parameter_name1  = 'value1',
@parameter_name2  = 'value2',
    ...

Die Stored Procedure sp_executesql erwartet also 3 verschiedene Arten von Eingaben:

Das eigentliche SQL Statement: Dies wird an die Variable @stmt übergeben und kann beliebige Input und Output Parameter (wie @parameter_name1 oder @parameter_name2) enthalten.
Die Parameter Liste: Diese enthält alle Parameter die im SQL Statement verwendet werden inklusive Datentyp und optionaler Kennzeichnung als Output Parameter mittels Keyword OUTPUT.
Die einzelnen Parameter und ihre Werte: Jedem Parameter des SQL Statements kann so vor der Ausführung des SQL Statements ein Wert zugewiesen werden. Außerdem kann man auf diese Weise auch die Werte der Ouput Parameter übergeben.

Zum Auslesen eines Datensatzes aus der Orders Tabelle der Northwind Datenbank könnte man zum Beispiel eine Abfrage wie folgt verwenden (Auszug aus 20001013_test_sp_executesql.sql):

/* Auszufuehrendes SQL Statement mit Parametern */
SELECT @SQLStatement = N'SELECT @paramCustomerID=CustomerID, ' +
   '@paramEmployeeID=EmployeeID, @paramOrderDate=OrderDate, ' +
   '@paramShipName=ShipName, @paramShipCountry=ShipCountry ' +
   'FROM Northwind.dbo.Orders WHERE OrderID=@paramOrderID'

/* Parameterdefinition */
SELECT @ParameterDefinition = N'@paramOrderID INT, ' +
   '@paramCustomerID NCHAR(5) OUTPUT, @paramEmployeeID INT OUTPUT, ' +
   '@paramOrderDate DATETIME OUTPUT, @paramShipName NVARCHAR(40) OUTPUT, ' +
   '@paramShipCountry NVARCHAR(15) OUTPUT'

/* Werte der Inputparameter: */
SELECT @OrderID = 10555

/* Ausfuehren des parametrisierten SQL Kommandos 
   mit Hilfe der System Stored Procedure sp_executesql */
EXECUTE sp_executesql 
   @stmt             = @SQLStatement, 
   @params           = @ParameterDefinition, 
   @paramOrderID     = @OrderID,
   @paramCustomerID  = @CustomerID  OUTPUT, 
   @paramEmployeeID  = @EmployeeID  OUTPUT,
   @paramOrderDate   = @OrderDate   OUTPUT,
   @paramShipName    = @ShipName    OUTPUT,
   @paramShipCountry = @ShipCountry OUTPUT

Die Verwendung der sp_executesql Stored Procedure zur Durchführung einer parametrisierten SQL Abfrage gestaltet sich in ASP mittels Command Objekt wie folgt (Auszug aus 20001013_sp_executesql.asp):

  Set cmd = CreateObject("ADODB.Command")
  ' Spezielle Stored Procedure des SQL Server 7.0
  cmd.CommandText = "sp_executesql"
  cmd.CommandType = adCmdStoredProc 
  Set cmd.ActiveConnection = conn
  
  ' Definiere SQL Statement:
  strSQL = "SELECT " & _
  "@CustomerID=CustomerID, @EmployeeID=EmployeeID, " & _
  "@OrderDate=OrderDate, @ShipName=ShipName, @ShipCountry=ShipCountry "  & _
  "FROM Orders WHERE OrderID=@paramOrderID"
  
  ' Parameterdefinition zur Verwendung in der Stored Procedure
  ' Input Parameter, werden an Stored Procedure uebergeben
  ' Jeweils Name, SQL Datentyp
  strInputParams = "@paramOrderID INT"

  ' Werte des InputParameter:
  lngParamOrderID = 10555

  ' Ouput Parameter, werden von der Stored Procedure geliefert
  strOuputParams = "@CustomerID NCHAR(5) OUTPUT, @EmployeeID INT OUTPUT, " & _
     "@OrderDate DATETIME OUTPUT, @ShipName NVARCHAR(40) OUTPUT, " & _
     "@ShipCountry NVARCHAR(15) OUTPUT"
  
  ' Parameter Liste: Wird an die Stored Procedure uebergeben
  strParams = strInputParams 
  If Len (strInputParams) > 0 Then strParams = strParams & ", "
  strParams = strParams & strOuputParams
  
  ' Definiere ADO Parameter Objekte und fuege Sie zu Command Objekt hinzu
  
  ' SQL Statement als Parameter an sp_executesql uebergeben:
  Set tmpParam = cmd.CreateParameter("@stmt", adVarWChar, adParamInput, 1024, strSQL)
  cmd.Parameters.Append tmpParam
  
  ' Parameter Liste uebergeben:
  
  cmd.Parameters.Append cmd.CreateParameter("@params", adVarWChar, _
                              adParamInput, 500, strParams)
  
  ' Input Parameter uebergeben:
  cmd.Parameters.Append cmd.CreateParameter("@paramOrderID", adInteger, _
                            adParamInput, 4, lngParamOrderID)
  
  ' Output Parameter definieren:
  cmd.Parameters.Append cmd.CreateParameter("@CustomerID", adWChar, adParamOutput, 5)
  cmd.Parameters.Append cmd.CreateParameter("@EmployeeID", adInteger, adParamOutput, 4)
  cmd.Parameters.Append cmd.CreateParameter("@OrderDate", adDate, adParamOutput, 8)
  cmd.Parameters.Append cmd.CreateParameter("@ShipName", adWChar, adParamOutput, 40)
  cmd.Parameters.Append cmd.CreateParameter("@ShipCountry", adWChar, adParamOutput, 15)
  
  ' Fuehre Command Objekt aus
  cmd.Execute

   ' Lese Output Parameter aus:
   strCustomerID = cmd.Parameters("@CustomerID").Value 
   intEmployeeID = cmd.Parameters("@EmployeeID").Value 
   dateOrderDate = cmd.Parameters("@OrderDate").Value 
   strShipName = cmd.Parameters("@ShipName").Value
   strShipCountry = cmd.Parameters("@ShipCountry").Value

Schließlich bleibt mir noch, die gemessene Geschwindigkeit dieses Ansatzes zu liefern. Die Performance Messung (siehe Datei 20001013_Speed_sp_executesql.asp) hat die folgenden Werte ergeben:

Erster Start:

Connection Öffnen: 1.50 ms

Erstellen des Command Objektes: 0.78 ms

Ausführen des Commands: 1.24 ms

Auslesen der Werte des Recordsets: 0.050 ms

Gesamt: 3.57 ms

Durchschnitt:

Connection Öffnen: 0.70 ms

Erstellen des Command Objektes: 0.62 ms

Ausführen des Commands: 0.72 ms

Auslesen der Werte des Recordsets: 0.035 ms

Gesamt: 2.07 ms

Die Geschwindigkeit ist damit fast so schnell wie der Aufruf einer in der Datenbank selbst vorliegenden Stored Procedure!

Noch eine Anmerkung zur Datei 20001013_sp_executesql.asp: Wollte man das ganze Statement als Command.Prepared = True kennzeichen, um so die Verarbeitung bei mehrmaligem Aufruf zu Beschleunigen, so wird man mit folgender Fehlermeldung daran gehindert:

Microsoft OLE DB Provider for SQL Server error '80004005' 
Syntax error or access violation

Nobody is perfect ...
Siehe auch MSDN Artikel "PRB: E_FAIL Returned from Prepare() When SQL Statement Contains a Parameter in a Subquery"

Schlußbemerkung

Will man eine Datenbankabfrage einer Webseite optimieren, so hilft oft nur mehrere Ansätze durchzutesten und gewissenhaft zu optimieren, bis man schließlich aufgrund der gemessenen Verarbeitungszeiten die beste Lösung einsetzt.

This printed page brought to you by AlphaSierraPapa

Download des Codes

Klicken Sie hier, um den Download zu starten.
http://www.aspheute.com/code/20001013.zip

Links zu anderen Sites

ADO Programming Considerations (SQL Server 7.0)
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/adoprg01_16.htm
Getting Started with Transact-SQL
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/tsqlcon.htm
PRB: E_FAIL Returned from Prepare() When SQL Statement Contains a Parameter in a Subquery
http://support.microsoft.com/support/kb/articles/Q235/0/53.ASP
Using Stored Procedures
http://msdn.microsoft.com/library/default.asp?URL=/library/partbook/asp/usingstoredprocedures.htm

©2000-2006 AspHeute.com
Alle Rechte vorbehalten. Der Inhalt dieser Seiten ist urheberrechtlich geschützt.
Eine Übernahme von Texten (auch nur auszugsweise) oder Graphiken bedarf unserer schriftlichen Zustimmung.

Server.CreateObject("ADODB.Recordset"):	0.363 ms
CreateObject("ADODB.Recordset"):	0.693 ms

Erster Start:
Connection Öffnen:	1.50 ms
Erstellen des Recordsets:	0.35 ms
Öffnen des Recordsets:	1.70 ms
Auslesen der Werte des Recordsets:	0.050 ms
Gesamt:	3.60 ms

Durchschnitt:
Connection Öffnen:	0.70 ms
Erstellen des Recordsets:	0.08 ms
Öffnen des Recordsets:	0.83 ms
Auslesen der Werte des Recordsets:	0.044 ms
Gesamt:	1.65 ms

Highspeed-Abfragen einer SQL Server Datenbank

Recordset und Connection Performance Tuning

Server.CreateObject statt CreateObject

Parameter des Recordset Objektes

adExecuteNoRecords Parameter einsetzen

Unterschiedliche Ansätze von Datenbankabfragen

Ausführen eines SELECT Statements

Stored Procedure

Stored Procedure mit Output Parameter

sp_executesql mit Ouput Parameter

Schlußbemerkung

Download des Codes

Verwandte Artikel

Links zu anderen Sites