Rückgabe einer Liste aus einem SqlCommand-Objekt - wie kann ich der Datenbank so wenig Arbeit wie möglich machen?

Question

Ich schreibe eine private Methode für meine Klasse. Ich übergebe als Parameter eine Liste von Ganzzahlen, die die ID einer Zeile in meiner SQL Server 2008-Tabelle darstellen.

Ich möchte eine List<string> der "Name" (eine Spalte) in allen Zeilen, in denen eine der übergebenen ganzen Zahlen gleich einer "ID" . Wenn ich also die List<int> {1, 2, 3 } .

Ich möchte im Wesentlichen den folgenden Befehl ausführen (SELECT Name FROM Table WHERE ID = 1 OR ID = 2 OR ID = 3).ToList<string>() .

Die Datenbank, die ich verwende, ist sehr stark ausgelastet, und daher ist es sehr wichtig, dass ich meine Lösung so weit wie möglich optimiere. Vor diesem Hintergrund frage ich mich, ob es besser wäre, eine Verknüpfung zu dieser DB mithilfe einer .dbml-Datei zu erstellen und Linq to SQL zur Abfrage der Datenbank zu verwenden?

Oder einfach ein SQLCommand-Objekt erstellen, es einmal ausführen, über einen Leser iterieren und es in einer Liste speichern? Wie lässt sich dies am besten bewerkstelligen? Ist die Erstellung einer .dbml-Datei zur Darstellung einer stark ausgelasteten Datenbank eine schlechte Praxis?

Answer 1

Die Erstellung einer .dbml hat nur sehr wenig mit der serverseitigen Leistung zu tun; das ändert das Tooling auf der aufrufenden Seite - aber der Server wird den Unterschied zwischen Befehlen, die aus einer .dbml kommen, und handcodierten Befehlen nicht wirklich bemerken, zumindest nicht bei so einfachen Dingen (ich sollte anmerken, dass bei komplexen Abfragen handcodierte Abfragen oft besser abschneiden als maschinell erstellte Abfragen).

Was die Leistung beim Aufrufer betrifft, so ist eine .dbml nur ein Wrapper um all die üblichen Befehle/Leser/etc - es kann die Dinge nicht schneller machen. In einigen Fällen könnte es das machen Langsamer wenn es einen Ausdruck nicht gut parst oder das geparste Ergebnis nicht zwischenspeichert (im Sinne von TSQL).

Was ich wird sagen jedoch, dass flott wird dies sehr gut für Sie erledigen:

var ids = new List<int>{1,2,3};
var names = conn.Select<string>("select Name from Table where ID in @ids",
      new {ids}).ToList();

dapper wird die in @ids Verwendung, und wird diese als Parameter, Ausführung erweitern:

select Name from Table where ID in (@p__0, @p__1, @p__2)

(oder so ähnlich) - mit den Werten 1, 2 und 3.

Das gibt Ihnen:

• Bequemlichkeit für den Anrufer
• Leistung beim Aufrufer (dapper ist stark optimiert)
• vollständige Parametrierung
  • Ermöglichung einer optimalen Wiederverwendung von Abfrageplänen auf dem Server

Mehr allgemein Dapper übernimmt auch gerne allgemeine Zuordnungen von Entitäten, zum Beispiel:

int id = 12345;
var customer = conn.Select<Customer>("select * from Custom where Id = @id",
    new { id }).Single();

Answer 2

Ich würde mehrere Dinge tun:

A. Verwenden Sie einen tabellenwertigen Parameter

CREATE TYPE LocationTableType AS TABLE 
( ID INT);
GO

B. Verwenden Sie eine gespeicherte Prozedur (mit Ihrer TVP)

CREATE PROCEDURE dbo. usp_GetLocationNames
    @TVP LocationTableType READONLY
    AS 
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    SELECT Name
    FROM dbo.Location l
    JOIN @TVP t ON l.ID = t.ID

C. Dirty Reads zulassen - SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

D. Zählt keine Zeilen - SET NOCOUNT ON;

E. Zwischenspeichern der Ergebnismenge für eine bestimmte Zeitspanne

Da ich sehr wenig über Ihre Anwendung oder Ihre Situation weiß, sind diese Punkte "im Allgemeinen" das, was ich bei den meisten Procs tun würde. Wenn Sie den Kontostand einer Person überprüfen, bevor Sie Bargeld ausgeben, würden Sie natürlich keine schmutzigen Lesevorgänge zulassen und die Ergebnismenge nicht zwischenspeichern. Aber in den meisten Situationen sind diese Dinge akzeptabel.

Answer 3

Um den Datenverkehr zu begrenzen, ist es besser, die Anzahl der Roundtrips zur Datenbank zu reduzieren, also ist es besser, wenn Sie nur einen Befehl erteilen, vielleicht mit der IN-Klausel anstelle von mehreren oder und Ihre Abfrage parametrisieren.

Answer 4

Wenn Sie nach numerischen IDs auswählen, ist es sicher, die WHERE-Klausel dynamisch zu bilden (d.h. WHERE ID IN (1,2,3,...)

Eine fortgeschrittenere Methode ist die Erstellung von SP mit XML-Parametern. Beispiel-Code-Schnipsel:

DECLARE @xmlIds AS XML
SET @xmlIds = '<Ids><ID>1</ID><ID>2</ID></Ids>'

SELECT Name FROM Table
WHERE ID IN (
    SELECT
        Data.row.value('.', 'INT')
        FROM
        @XmlIds.nodes('/Ids/ID') As Data(row))

Answer 5

Sie können all dies nativ mit nativem C# und Sql 2008 tun. In Sql 2008 wurde eingeführt Benutzerdefinierte Tabellentypen y Tabellenbewertete Parameter für Stored Procedures.

Das Folgende würde Ihnen also genau das geben, was Sie wollen,

CREATE TYPE UdtId AS TABLE
(
    [ID] INT NOT NULL
    PRIMARY KEY NONCLUSTERED ([ID] ASC)
)

CREATE PROCEDURE spGetCustomerByIds
    @IDS UdtId READONLY
AS
BEGIN
    SELECT
        C.*
    FROM Customer C
    INNER JOIN UdtId I ON
        C.ID = I.ID
END

Hoffentlich ist der C#-Code dahinter klar, aber es würde wie folgt aussehen,

public foo GetCustomerDataByIds(IEnumerable<int> ids)
{
    using (var command = new SqlCommand())
    using (var adatper = new SqlAdapter())
    using (var dataSet = new DataSet())
    {
        command.Text = "spGetCustomerByIds";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@IDS", GetDataTableOfIds(ids));
        // execute and return the stuff you're after
    }
}

private DataTable GetDataTableOfIds(IEnumerable<int> ids)
{
    var table = new DataTable();
    table.Columns.Add(new DataColumns("ID", typeof(int));
    foreach (var id in ids)
    {
        table.Rows.Add(id);
    }
    return table;
}