Wie wird eine CLR-Tabelle bewertungsfunktion 'streaming' bewertet?

Question

Die MSDN-Dokumente zu tabellenwertigen Sql Clr-Funktionen besagen:

Transact-SQL-Tabellenwertige Funktionen materialisieren die Ergebnisse des Funktionsaufrufs in eine Zwischentabelle. ... Im Gegensatz dazu stellen CLR-Tabellenwertige Funktionen eine alternative Streaming-Möglichkeit dar. Es besteht keine Anforderung, dass der gesamte Ergebnissatz in einer einzelnen Tabelle materialisiert wird. Das von der verwalteten Funktion zurückgegebene IEnumerable-Objekt wird direkt vom Ausführungsplan der Abfrage aufgerufen, die die tabellenwertige Funktion aufruft, und die Ergebnisse werden inkrementell verarbeitet. ... Es ist auch eine bessere Alternative, wenn sehr viele Zeilen zurückgegeben werden, weil sie nicht als Ganzes im Speicher materialisiert werden müssen.

Dann finde ich heraus, dass kein Datenzugriff in der 'Fill row'-Methode erlaubt ist. Das bedeutet, dass Sie immer noch all Ihren Datenzugriff in der Init-Methode erledigen müssen und ihn im Speicher behalten müssen, bis 'Fill row' aufgerufen wird. Habe ich etwas missverstanden? Wenn ich meine Ergebnisse nicht in ein Array oder eine Liste zwingen, erhalte ich einen Fehler: 'ExecuteReader erfordert eine offene und verfügbare Verbindung. Der aktuelle Zustand der Verbindung ist geschlossen.'

Codebeispiel:

[]
static member InitExample8() : System.Collections.IEnumerable = 
   let c = cn() // öffnet eine Kontextverbindung
   // Ich möchte hier das Erzwingen einer Enumeration vermeiden:
   let data = getData c |> Array.ofSeq
   data :> System.Collections.IEnumerable

static member Example8Row ((obj : Object),(ssn: SqlChars byref)) = 
   do ssn <- new SqlChars(new SqlString(obj :?> string))
   ()

Ich habe hier mit mehreren Millionen Zeilen zu tun. Gibt es eine Möglichkeit, dies faul zu machen?

Answer 1

Ich gehe davon aus, dass Sie SQL Server 2008 verwenden. Wie von einem Microsoft-Mitarbeiter auf dieser Seite erwähnt wurde, erfordert 2008, dass Methoden wesentlich häufiger mit DataAccessKind.Read markiert werden als 2005. Einer dieser Fälle ist, wenn die TVF an einer Transaktion teilnimmt (was anscheinend immer der Fall war, als ich es getestet habe). Die Lösung besteht darin, enlist=false in der Verbindungszeichenfolge anzugeben, was leider nicht mit context connection=true kombiniert werden kann. Das bedeutet, dass Ihre Verbindungszeichenfolge im typischen Clientformat vorliegen muss: Data Source=.;Initial Catalog=MyDb;Integrated Security=sspi;Enlist=false und Ihre Assembly mindestens mit permission_set=external_access erstellt werden muss. Folgendes funktioniert:

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

namespace SqlClrTest {
    public static class Test {
        [SqlFunction(
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read,
            TableDefinition = "RowNumber int",
            FillRowMethodName = "FillRow"
            )]
        public static IEnumerable MyTest(SqlInt32 databaseID) {
            using (var con = new SqlConnection("data source=.;initial catalog=TEST;integrated security=sspi;enlist=false")) {
                con.Open();
                using (var cmd = new SqlCommand("select top (100) RowNumber from SSP1 where DatabaseID = @DatabaseID", con)) {
                    cmd.Parameters.AddWithValue("@DatabaseID", databaseID.IsNull ? (object)DBNull.Value : databaseID.Value);
                    using (var reader = cmd.ExecuteReader()) {
                        while (reader.Read())
                            yield return reader.GetInt32(0);
                    }
                }
            }
        }
        public static void FillRow(object obj, out SqlInt32 rowNumber) {
            rowNumber = (int)obj;
        }
    }
}

Hier ist dasselbe in F#:

namespace SqlClrTest

module Test =

    open System
    open System.Data
    open System.Data.SqlClient
    open System.Data.SqlTypes
    open Microsoft.SqlServer.Server

    []
    let MyTest (databaseID:SqlInt32) =
        seq {
            use con = new SqlConnection("data source=.;initial catalog=TEST;integrated security=sspi;enlist=false")
            con.Open()
            use cmd = new SqlCommand("select top (100) RowNumber from SSP1 where DatabaseID = @DatabaseID", con)
            cmd.Parameters.AddWithValue("@DatabaseID", if databaseID.IsNull then box DBNull.Value else box databaseID.Value) |> ignore
            use reader = cmd.ExecuteReader()
            while reader.Read() do
                yield reader.GetInt32(0)
        } :> System.Collections.IEnumerable

    let FillRow (obj:obj) (rowNumber:SqlInt32 byref) =
        rowNumber <- SqlInt32(unbox obj)

Die gute Nachricht ist: Microsoft betrachtet dies als Bug.

Answer 2

Ja, Sie müssten die Ergebnisse in den Speicher laden und von dort zurückgeben. Obwohl die Absicht wäre, dass Sie solche Operationen vermeiden.

Sie können ein Beispiel für diesen Ansatz in einem der Abschnitte des von Ihnen verlinkten MSDN-Dokuments sehen ("Beispiel: Rückgabe der Ergebnisse einer SQL-Abfrage")

Die Beispiele sind jedoch etwas konstruiert, da eine Implementierung der E-Mail-Validierung in der realen Welt eine Skalarfunktion anstelle einer Tabellenfunktion verwenden würde - sie gibt einen bool für jeden Eingabewert der E-Mail zurück, anstelle einer Liste der ungültigen E-Mails.

Können Sie ein wenig mehr darüber erklären, was Sie erreichen möchten? Es könnte eine bessere Möglichkeit geben, die Funktion zu strukturieren.

Answer 3

Was du tun kannst, ist, einen SqlDataReader class mit einem IEnumerable zu umhüllen, das einen Enumerator verwendet, der , wenn die Methode "Next" aufgerufen wird, MoveNext auf dem SqlDataReader durchführt und den SqlDataReader zurückgibt. Dann erwartet deine FillRow-Methode SqlDataReader als Klasse. Wenn du deinen Enumerator die Datenbankverbindung und den SqlDataReader schließen lässt, wenn er nicht mehr "next" kann, dann hast du effektiv deine Ausgabe an die FillRows-Funktion gestreamt. Du kannst dies auch mit ContextConnection=true machen...

...das Problem hierbei ist, dass du in der Lage sein musst, die Ergebnisse einer tatsächlichen Abfrage zurückzugeben: Wenn du komplexere Dinge machst, um deinen Resultatsatz zu erstellen, dann hast du Pech.