sql server - net - ¿Cómo funciona una tabla valorada de CLR ''streaming''?

habilitar clr enabled sql server

Question

Los documentos de MSDN sobre las funciones Sql Clr con valores de tabla indican:

Las funciones con valores de tabla de Transact-SQL materializan los resultados de llamar a la función en una tabla intermedia. ... Por el contrario, las funciones CLR con valores de tabla representan una alternativa de transmisión. No es necesario que todo el conjunto de resultados se materialice en una sola tabla. El objeto IEnumerable devuelto por la función administrada es llamado directamente por el plan de ejecución de la consulta que llama a la función de valor de tabla, y los resultados se consumen de forma incremental. ... También es una mejor alternativa si tiene un gran número de filas devueltas, porque no tienen que materializarse en la memoria como un todo.

Luego descubro que no se permite el acceso a datos en el método ''Rellenar fila'' . Esto significa que todavía tiene que hacer todo su acceso a los datos en el método init y mantenerlo en la memoria, esperando a que se llame a ''Fill row''. ¿He entendido mal algo? Si no fuerzo mis resultados en una matriz o lista, aparece un error: ''ExecuteReader requiere una conexión abierta y disponible. El estado actual de la conexión está cerrado.

Muestra de código:

[<SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "Example8Row")>]
static member InitExample8() : System.Collections.IEnumerable = 
   let c = cn() // opens a context connection
   // I''d like to avoid forcing enumeration here:
   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))
   ()

Estoy lidiando con varios millones de filas aquí. ¿Hay alguna manera de hacer esto perezosamente?

Answer 1

Lo que puede hacer es ajustar una clase SqlDataReader con un IEnumerable que usa un enumerador que, cuando se llama al método "Siguiente", hace MoveNext en el SqlDataReader y devuelve el SqlDataReader. Entonces, su método FillRow espera SqlDataReader como una clase. Si su enumerador cierra la conexión de la base de datos y el SqlDataReader cuando ya no puede "seguir", entonces efectivamente ha transmitido su salida a la función FillRows. Puedes hacer esto con ContextConnection = true también ...

... el problema aquí es que tienes que poder devolver los resultados de una consulta real: si estás haciendo cosas más complejas para crear tu conjunto de resultados, entonces no tienes suerte.

Answer 2

Sí, necesitarías llevar los resultados a la memoria y luego regresar de allí. Aunque la intención sería evitar la necesidad de hacer tales operaciones.

Puede ver un ejemplo del enfoque en una de las secciones del documento de MSDN al que se ha vinculado ("Ejemplo: devolver los resultados de una consulta SQL")

Sin embargo, los ejemplos son un tanto artificiales ya que una implementación en el mundo real de la validación de correo electrónico usaría una función escalar en lugar de tabla, devolviendo un bool para cada valor de correo electrónico de entrada en lugar de una lista de aquellos que no son válidos.

¿Eres capaz de explicar un poco más sobre lo que estás tratando de lograr? Puede haber una mejor forma de estructurar la función.

Answer 3

Supongo que está utilizando SQL Server 2008. Tal como lo menciona un empleado de Microsoft en esta página , 2008 requiere que los métodos se marquen con DataAccessKind. Se lee con mucha más frecuencia que en 2005. Una de esas veces es cuando TVF participa en una transacción. (que parecía ser siempre el caso, cuando probé). La solución es especificar enlist=false en la cadena de conexión, que, por desgracia, no se puede combinar con la context connection=true . Esto significa que la cadena de conexión debe estar en el formato de cliente típico: Data Source=.;Initial Catalog=MyDb;Integrated Security=sspi;Enlist=false y su ensamblaje debe crearse con permission_set=external_access , como mínimo. Los siguientes trabajos:

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;
        }
    }
}

Esto es lo mismo en F #:

namespace SqlClrTest

module Test =

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

    [<SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.Read,
        TableDefinition = "RowNumber int",
        FillRowMethodName = "FillRow"
        )>]
    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)

La buena noticia es que Microsoft considera que esto es un error .