Mehrfachverwendung einer SQL-Abfrage

Question

Hallo NG,
ich habe ein Problem, wo ich ein DataSet mit verschiedenen Tabellen füllen muss. Das sind entspr. viele SQL-Abfragen.

Nun ist es allerdings so, dass ich gern eine Voarab-Tabelle erstellen möchte, wo ich aus der Datenbank bereits bestimmte Datensätze extrahiere um sie dann in den einzelnen SQL-Abfragen nutzen zu können "Where id IN (Select * From temp)". Das würde sicherlich die SQL-Last verringern und das Programm würde übersichtlicher. Jede SQL-Abfrage hat quasi die gleiche Bedingung.

Nun gibt es da Cursor, Tabellenvariablen, temporäre Tabellen oder WITH. Aber alles scheint nicht so recht zu passen. Cursor geht nur mit Fetch Next, Tabellenvariablen gelten nur im Batch und temporäre Tabellen sind auch nicht so einfach zu handeln, da man zw. tempdb und der eigentlichen DB umherswitchen muss mit unterschiedlichen Nutzer-IDs.

Ich denke, es gibt dafür sicherlich eine komfortable Lösung, da so eine Aufgabenstellung doch öfter vorkommen könnte.

Uwe

---

Mittelung vom Forum

Answer 1

Hallo Uwe,

wenn die Abfrage bzw. das Resultset mehrfach für separat an den DB Server gestellte SQL Abfragen verwendet werden soll, käme evtl. eine global temporäre Tabelle in Frage (CREATE TABLE ##Name anstelle von #Name, also mit ##). Siehe dazu:

  SQL SERVER – How to Create A Global Temporary Table?

Was Du jetzt mit "zwischen Tempdb und ... switchen" meinst, verstehe ich nicht. Du kannst problemlos über Datenbank.Schema.Objektname (entsprechende Berechtigungen des ausführenden Benutzer natürlich vorausgesetzt) auch in einem einzigen SQL Statement Zugriffe über Datenbanken hinweg durchführen.

Wenn Du das Resultset nur in einer großen Abfrage/Prozedur/Funktion mehrfach brauchst aber nicht requestübergreifend, wäre eine Tabellenvariable evtl. sinnvoller. Aber auch da verstehe ich das Problem nicht so wirklich.

Evtl. wäre es sinnvoll, wenn Du mal an einem konrekten Beispiel zeigst, was genau Du machen willst.

 

---

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

Answer 2

Hallo Stefan,
danke für die schnelle Antwort.

Mir wäre eine Tabellenvariable am liebsten, aber die wird nach dem SQL-Statement offenbar wieder frei gegeben.

Ich habe mehrere SQL-Befehle:DataSet ds = new DataSet();
cmd1 = new SqlDataAdapter("CREATE TABLE #t (id int PRIMARY KEY CLUSTERED);
            Insert Into #t (id) Select ...  // temp. Tab erzeugen
Select ... Join #t ...",sqlConn);
cmd1.Fill(ds, "Tab1");

...

cmd1 = new SqlDataAdapter("Select ... Join #t ...",sqlConn);
cmd1.Fill(ds, "Tab10");

Damit rufe ich diverse Daten ab. In der Where -Klausel kommt fast immer das gleiche vor, weshalb mir eine temporäre Tabelle lieber wäre.

Ich habe allerdings festgestellt, dass bei dem 2. cmd1 die #t-Tabelle weg ist. Steht so ja auch in der Beschreibung, dass die Tabellenvariable nur im Batch gilt. Nun kann ich aber in einem cmd1-Statement nicht mehrere Tabellen in das Dataset laden.

Mit Tempdb habe ich, ehrlich gesagt, noch nicht gearbeitet. In dem Beispiel musste ich dafür aber einen User definieren, um die Tabelle anzulegen. Normalerweise ist ja der IIS eingeloggt und arbeitet auf einer anderen Datenbank.

Uwe

---

Mittelung vom Forum

Answer 3

Hallo Uwe,

für lokale temporäre Tabellen - die mit einem '#' - gilt, dass sie gelöscht werden, wenn die Sitzung (bei .NET SqlConnection) geschlossen wird, in der sie erstellt wurden. Wobei das durch Connection Pooling u. U. verzögert erfolgt und bei einem mehrfachen Öffnen der Verbindung nicht unbedingt immer die gleiche (physikalische) Datenbankverbindung sein muss.

Eine globale temporäre Tabelle (##) lebt solange wie es noch eine Verbindung gibt, die darauf zugreift. Jedoch ist der Tabellen-Name für alle Sitzungen sichtbar, so dass du Probleme bekommst, wenn mehrere Sitzungen, die gleiche Tabelle verwenden. Da ist es allgemein besser eine "normale" Arbeitstabelle zu verwenden und die Sitzungen it einem eindeutigen Schlüssel (z. B. uniqueidentifer) auseinander zu halten.

Damit eine lokale/globale temporäre Tabelle nicht unter den Füssen weggezogen wird, eröffne die Verbindung explizit, anstatt den DataAdapter es tun zu lassen, und halte die Verbindung solange offen, bis du alle Abfragen mit dieser Verbindung ausgeführt hast.

Ähnliches gilt für Tabellenvariablen, die im Hintergrund temporäre Tabellen sind, sobald sie nicht mehr in den Speicher passen. Nur ist ihre Gültigkeit auf den jeweiligen Stapel (Batch) und nicht die Verbindung beschränkt. Allerdings werden bei Abfragen mit Tabellenvariablen mangels Statistiken häufig schlechtere Abfragepläne erstellt.[1]

Im Falle eines Datasets gibt es durchaus die Möglichkeit, mehrere DataTables in einem Rutsch zu füllen, sofern du für den DataAdapter ein  DataTableMapping angibst - oder es abgeleitet werden kann. Dann tut es eine große SQL Abfrage (Batch). Wobei man dabei aufpassen muss, um evtl. vorhandene DML Befehle (INSERT, UPDATE, DELETE), die zum Erstellen benötigt werden, auszusortieren - die liefern leere (ignorierte) Ergebnisse.

Wenn Du etwas mehr über die Art der zu verkettenden Abfragen mitteilen würdest, könnte man bei der Lösungsfindung besser helfen.

Gruß Elmar

[1] Es gibt zudem speicheroptimierte Tabellen, siehe Schnellere temporäre Tabellen und Tabellenvariablen durch Speicheroptimierung, die passende SQL Server Version/Edition vorausgesetzt.

Answer 4

Hallo Elmar,
danke für die ausführliche Info.

Wie gesagt, das mit der temp. Tabelle scheint mir die beste Variante zu sein.

Ich habe das schon so gelöst: ich öffne die SqlConnection, da ich mit einem SqlCommand die temp. Tabelle erzeuge. Die Connection bleibt bis zum Programmende bestehen und wird erst dort geschlossen.

sqlConn.Open();
SqlCommand cmd= new SqlCommand(@"CREATE TABLE #t (id_tlnmz int PRIMARY KEY CLUSTERED);
            Insert Into #t (id_tlnmz)
             Select id_tlnmz ...", sqlConn);
            cmd.ExecuteNonQuery();

           SqlDataAdapter cmd1 = new SqlDataAdapter("Select * From #t", sqlConn);
            DataTable dtX = new DataTable();
            cmd1.Fill(dtX);

sqlConn.Close();

Trotzdem bekomme ich den Fehler: Ungültiger Objektname '#t'. 

Uwe

---

Mittelung vom Forum

Answer 5

Hallo Uwe,

steht doch eigentlich alles von Anfan an da: ## anstelle von #.

---

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

Answer 6

Hi Uwe,
Du fragst im ASP.NET Forum und schreibst "bis zum Programmende". Was meinst Du damit? Wenn Du unter "Programmende" das Lebensende einer Seite meinst, dann gibt es beim nächsten Seitenaufruf die temporäre Tabelle nicht mehr.

---

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

Answer 7

Hallo Peter,
du hast recht. Ich meinte natürlich bis zum Ende der Methode.

Uwe

---

Mittelung vom Forum

Answer 8

Hallo Stefan,
mit ##t funktioniert das. Danke!

Uwe

---

Mittelung vom Forum

Answer 9

Hallo Uwe,

da wirst Du etwas ausgelassen haben, denn es sollte auch mit "#" funktionieren. Als kleine Anleitung ein Beispiel für die Northwind, in dem die OrderIds in eine temporäre Tabelle wandern und darauf zwei Abfragen ausgeführt werden.

        internal static void SqlUseTemporaryTable()
        {
            DataTable ordersTable = new DataTable("Orders");
            DataTable orderDetailsTable = new DataTable("OrderDetails");
            using (var connection = new SqlConnection(Properties.Settings.Default.NorthwindConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand("CREATE TABLE #OrderIds (OrderID int NOT NULL);",
                    connection))
                {
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO #OrderIds (OrderID)\n"
                      +  "SELECT OrderId FROM dbo.Orders\n"
                      +  "WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = N'Germany');";
                    command.ExecuteNonQuery();


                    command.CommandText = "SELECT o.OrderID, o.CustomerID, o.OrderDate\n"
                        + "FROM dbo.Orders AS o INNER JOIN #OrderIds AS t ON o.OrderID = t.OrderID;";

                    var adapter = new SqlDataAdapter(command);
                    adapter.Fill(ordersTable);

                    command.CommandText = "SELECT od.OrderID, od.ProductID, od.Quantity\n"
                    + "FROM dbo.[Order Details] AS od INNER JOIN #OrderIds AS t ON od.OrderID = t.OrderID;";
                    adapter.Fill(orderDetailsTable);

                    command.CommandText = "DROP TABLE #OrderIds;";
                    command.ExecuteNonQuery();
                }
            }
            Debug.WriteLine($"Orders: {ordersTable.Rows.Count}, Details: {orderDetailsTable.Rows.Count}");
        }

Im übrigen ist es nicht zwingend, alles über ein SqlCommand und/oder SqlDataAdapter zu gehen, das habe ich oben nur der Kürze halber gemacht.

Das Ganze ginge ebenso als einzelner Batch, oder als Stored Procedure (dann ein DataSet), soll hier nur zeigen, dass man sich durchaus mit mehreren Anweisungen auf eine temporäre Tabelle beziehen kann.

Gruß Elmar