Zwei Server in einem SQL-String ansprechen?

Hallo,

ich habe eine Frage, wer kann mir sagen wie ich einen sql String schreiben muss wo ich zwei Datenbanken abfragen kann, die aber auf verschiedenen (in meinem Fall MSSQL-Servern) liegen.

Also ich möchte einfach einen Select machen wo ich Daten auslesen die jedoch mit einer (Left Join) Where Bedingung von einer anderen Datenbank auf einem anderen Server liegen. [Benutername und Passwort sind identisch]…

Meine Frage ist nur wie ich zwei Server gleichzeitig (also in einem SQL-String) abfragen kann.

Danke für eure Hilfe!

Gruss
Bernhard

Hallo Bernhard,
das wäre (bei oracle :wink: ein typischer Fall für einen Datenbanklink. Die Frage ist, gibt es sowas überhaupt beim MS-SQL-Server? Hab auf die Schnelle nichts dergleichen gefunden.
Womit machst Du die Abfrage, was für einen Client hast Du?
Mit Scripten wie Perl und PHP könnte ich mir auch Lösungen vorstellen.

Gruß, muzel

Hallo,

ich habe eine Frage, wer kann mir sagen wie ich einen sql
String schreiben muss wo ich zwei Datenbanken abfragen kann,
die aber auf verschiedenen (in meinem Fall MSSQL-Servern)
liegen.

Hallo,

Du benötigst zwei separate Verbindungen Pipelines zu der jeweiligen DB Man kann sicher komplizierte Konstrukte bauen. Je nach Ergebnismenge wäre zu überlegen, ob man nicht sinnvollerweise die Abfragen nacheinanderlegt.
Warum ?
Wenn Du ein Abfrage startest, legst Du sinnvollerweise die Bedingung, die die größte Ausschlussmenge darstellt als Erste. Dadurch beschränken sich die restlichen Bedingungen nur noch auf den übriggebliebene Menge.
Das Problem mit den Joins ist, die kartesische Ergebnismenge. Es ensteht ein deutlicher Geschwindigkeitsverlust.

Aus der Erfahrung heraus lässt sich auch hier sagen. Je Aufwendiger die Anfrage, desto langsamer // bei steigender Datenmenge. Es empfiehlt sich diese simpler und nacheinander zu legen.

Ansonsten sollte es ev. möglich sein diese über
die definierte
Verbindung A als A.Tabelle.Zeile
Verbindung B als B.Tabelle.xxx

also

Select * From A.Tabelle.Zeile leftJoin B.Tabelle.Zeile

Ich gebe aber gern zu, dass dies nur ne Theorie ist…

Gruss
Theo

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Hallo Muzel,

ich muss erst nachsehen ob ich beim MS-SQL Server eien Datenbanklink machen kann aber werde mich da mal schlau machen, danke für den Hinweis. Ich habe den Enterprise-Manager als „Abfragetool“ dabei verwende ich den Query-Analyser … Also ich schicke direkt den SQL-String zur Datenbank und kriege ein Ergebnis zurück.

Später jedoch wird es vorraussichtlich in eine Visual Basic App. eingebaut. Und in einem halben Jahr verwende ich das in Delphi … weil das VB ablösen wird bei uns.

Danke auf alle fälle für den Tipp!!!

Gruss
Bernhard

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Hallo,

danke auf jedenfall für die Antwort. Jedoch wie kann ich beim „vordefinieren“ auch einen „Server“ angeben? Ich meine ich kann ja sagen wie du beschrieben hast:

Verbindung A als A.Tabelle.Zeile
Verbindung B als B.Tabelle.xxx

Aber folgendes geht ja nicht oder?

Verbindung A als A.Server.dbo.Datenbank.Tabelle.Zeile
Verbindung B als B.Server.dbo.Datenbank.Tabelle.xxx

Gruss
Bernhard

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Hallo Theo!

Ich kann’s einfach nicht glauben, dass MS-SQL so schlecht ist (wiewohl ich Oracle-Fan bin):

Je nach Ergebnismenge wäre zu überlegen, ob man nicht
sinnvollerweise die Abfragen nacheinanderlegt.

Du meinst also in etwa folgendes: Ich habe eine Tabelle mit 1 Mio. Datensätzen, jeder 100 Bytes lang, also 100 MB. Ich mach jetzt mal als erstes:
SELECT * FROM big_table WHERE bedingung1;
bedingung1 scheidet schon mal 50 % der Daten aus. Der Server errechnet also mal die Ergebnismenge und schickt mir 500.000 Rows (~50 MB). Diese Menge bearbeite ich dann „händisch“ bei mir am Client, und zwar mit sagen wir mal 5 Bedingungen, die jeweils 40 % der Rows ausscheiden. Mir bleiben dann der Reihe nach 300.000, 180.000, 108.000, 64.800 und 38.880 Rows, d.h. ~3,9 MB übrig (sagen wir mal, das ist eine nicht sehr selektive Abfrage, aber auch Google liefert durchaus solche Resultatsmengen). Weil aber niemand 38.880 Sätze sehen will zeigt meine Applikation nur mal die ersten 100 Rows an (~10 KB). Und das soll allen Ernstes schneller gehen, als hätte ich der DB gleich alle meine Bedingungen mitgeteilt und dann mal die ersten 100 Sätze „abgeholt“?
Das erscheint mir durchaus recht unwahrscheinlich.

Warum ?
Wenn Du ein Abfrage startest, legst Du sinnvollerweise die
Bedingung, die die größte Ausschlussmenge darstellt als Erste.

Das macht mit Sicherheit der Optimizer der DB (mag bei MS-SQL anders heissen, aber geben tut es so ein Ding mit Sicherheit auch).

Dadurch beschränken sich die restlichen Bedingungen nur noch
auf den übriggebliebene Menge.

Auch das kann der Optimizer…

Das Problem mit den Joins ist, die kartesische Ergebnismenge.
Es ensteht ein deutlicher Geschwindigkeitsverlust.

Welche kartesische Ergebnismenge? Wenn ich schreibe „SELECT a.*, b.* FROM a, b WHERE a.primary_key=b.foreign_key AND a.primary_key=4711“ dann bildet die DB mit hoher Wahrscheinlichkeit keine kartesische Ergebnismenge um danach zu filtern. Vielmehr ist bei entsprechenden Indices mit zwei direkten Zugriffen über den Index zu rechnen.

Aus der Erfahrung heraus lässt sich auch hier sagen. Je
Aufwendiger die Anfrage, desto langsamer // bei steigender
Datenmenge. Es empfiehlt sich diese simpler und nacheinander
zu legen.

Dann hat dein Optimizer (oder eher noch dein Schemadesign) ein Problem. Ein SELECT von einer Row ist potentiell beinahe gleich schnell, egal ob die Tabelle eine oder eine Million Einträge hat.

Ich gehe ja eigentlich davon aus, dass ich dich völlig falsch verstenden habe und bitte daher um Aufklärung…

Danke,
Martin

Hallo,

Du meinst also in etwa folgendes: Ich habe eine Tabelle mit 1
Mio. Datensätzen, jeder 100 Bytes lang, also 100 MB. Ich mach
jetzt mal als erstes:
SELECT * FROM big_table WHERE bedingung1;
bedingung1 scheidet schon mal 50 % der Daten aus. Der Server
errechnet also mal die Ergebnismenge und schickt mir 500.000
Rows (~50 MB). Diese Menge bearbeite ich dann „händisch“ bei
mir am Client, und zwar mit sagen wir mal 5 Bedingungen, die
jeweils 40 % der Rows ausscheiden. Mir bleiben dann der Reihe
nach 300.000, 180.000, 108.000, 64.800 und 38.880 Rows, d.h.
~3,9 MB übrig (sagen wir mal, das ist eine nicht sehr
selektive Abfrage, aber auch Google liefert durchaus solche
Resultatsmengen). Weil aber niemand 38.880 Sätze sehen will
zeigt meine Applikation nur mal die ersten 100 Rows an (~10
KB). Und das soll allen Ernstes schneller gehen, als hätte ich
der DB gleich alle meine Bedingungen mitgeteilt und dann mal
die ersten 100 Sätze „abgeholt“?
Das erscheint mir durchaus recht unwahrscheinlich.

Kurioser Weise - ich versuchs mal im Konjunktiv - glaube ich dies
genauso verstanden zu haben (wie gesagt ich kann mich irren.)
Im Rahmen einer LehrVeranstaltung über GIS wurde dies so dargestellt.
Nicht bei 2 Tabellen es waren schon ein paar mehr.
Doch der Grundton war genauso. Schneide im ersten Schritt ab, was deine Ergebnismenge voraussichtlich am Kleinsten werden lässt.
Vielleicht ist das mit 50% etwas weniger sinnvoll. -aber gerade bei den Gis hast Du reichliche Datenmengen, die auch bei uns auch über Oracle laufen. Wenn ich also schon im ersten Schritt 99,5% ausschliessen kann, dann scheint mir das nicht zwangsläufig abwegig.

…aber das geht hier eigentlich zu weit.
Ich habe lediglich geschrieben, da ich vor einiger Zeit ein Problem durch Nacheinanderlegung deutlich besser durchführen konnte. In dieser Sache bot sich das als Antwort an.

Das macht mit Sicherheit der Optimizer der DB (mag bei MS-SQL
anders heissen, aber geben tut es so ein Ding mit Sicherheit
auch).

Das kann und wird sicher schon so sein.

Welche kartesische Ergebnismenge? Wenn ich schreibe „SELECT
a.*, b.* FROM a, b WHERE a.primary_key=b.foreign_key AND
a.primary_key=4711“ dann bildet die DB mit hoher
Wahrscheinlichkeit keine kartesische Ergebnismenge um

Richtig. Nimm aber mal Abfragen die sich auf einige Tabellen mehr beziehen. Wie schliesst Du aus, dass nicht jedesmal alles, kombiniert mit jedem, auf ein Attribut durchsucht wird.

danach zu filtern. Vielmehr ist bei entsprechenden
Indices mit zwei direkten Zugriffen über den Index zu rechnen.

Dann hat dein Optimizer (oder eher noch dein Schemadesign

– das ist durchaus möglich

) ein
Problem. Ein SELECT von einer Row ist potentiell beinahe
gleich schnell, egal ob die Tabelle eine oder eine Million
Einträge hat.

ich habe da leise Zweifel, vor allem wenn zwei Server angesprochen werden.

Ich gehe ja eigentlich davon aus, dass ich dich völlig falsch
verstenden habe und bitte daher um Aufklärung…

eigentlich nicht, aber ich habe das Gefühl, dass ich gerade wieder was dazulerne …
Ich lass mich gern aufklären.

Danke,
Martin

Hallo Theo!

Kurioser Weise - ich versuchs mal im Konjunktiv - glaube ich
dies
genauso verstanden zu haben (wie gesagt ich kann mich irren.)
Im Rahmen einer LehrVeranstaltung über GIS wurde dies so
dargestellt.

Ich hoffe du irrst dich.

Nicht bei 2 Tabellen es waren schon ein paar mehr.

Vorweg: Die Terminologie ist bei mir schwer Oracle-lastig. Einige punkte heissen bei anderen RDBMSs sicher anders, was sie tun ist aber eigentlich immer das Gleiche.

Das Prinzip bleibt aber immer das gleiche, auch wenn der Optimizer bei mehr Tabellen auch mehr mögliche Execution Paths (vielleicht sollte ich gleich auf Englisch antworten?) berücksichtigen muss. Das Problem dabei ist: Die Datenbank weiss potentiell wesentlich mehr über Tabelln, Indices etc. als du. Wenn also die Datenbank einen Zugriffsplan erstellt, dann ist der schon aus diesem Grund potentiell besser. Zumindest bei neueren Versionen von Oracle (ab ~9iR2) liegt der Optimizer schon fast immer richtig. Ausnahme: Er weiss bestimmte Dinge nicht, die ich aber weiss/mein Schemadesign ist suboptimal und vereinzelte Irrtümer (schlisslich ist auch bei Oracle nicht alles perfekt :wink:.

Doch der Grundton war genauso. Schneide im ersten Schritt ab,
was deine Ergebnismenge voraussichtlich am Kleinsten werden
lässt.

Schon, aber wie weiter? Die Ergebnismenge dann auf den Client holen? Dort selber weiterwerken? Das KANN nicht schneller sein als nur die Daten vom Server zu holen, die man braucht.

Vielleicht ist das mit 50% etwas weniger sinnvoll.

Gebe ich ja zu. Aber das Prinzip bleibt das gleiche, egal ob das jetzt 50 oder 99,5 % sind.

-aber
gerade bei den Gis hast Du reichliche Datenmengen, die auch
bei uns auch über Oracle laufen. Wenn ich also schon im ersten
Schritt 99,5% ausschliessen kann, dann scheint mir das nicht
zwangsläufig abwegig.

Schon, aber wie schon gesagt, wie weiter? Mir fällt auf die schnelle keine Möglichkeit ein das Ergebnis eines SELECTs weiter zu bearbeiten, ohne es auf den Client zu holen. Vielleicht habe ich da jetzt ja nur ein Brett vor dem Kopf.

Andererseits: Der Server hat folgende Möglichkeiten, ein Ergebnis zu ermitteln:
a) Er liest die gesamte Tabelle und vergleicht bei jeder Row für jedes Kriterium. Welches der Kriterien er zuerst prüft weiss meine Applikation zwar nicht, aber es kann ihr auch völlig egal sein, der Aufwand liegt hier ohnehin im Lesen der Daten, weniger im Vergleich.
b) Er hat für ein oder mehrere Kriterien einen Index. Dann liest er (selten/manchmal/oft/immer) zuerst natürlich den Index und scheidet so schon mal alle Rows aus, die nicht diesem Kriterium entsprechen. Mit zusammengesetzten Indices kann ich so auch schon mal mehrere Attribute verknüpfen. Was dann noch übrigbleibt geht er wie in a) durch. Wenn der Server vernünftige Statistiken hat, dann weiss er auch, welche Kriterien am selektivsten sind, also wird er (wahrscheinlich) auch diese zuerst auswerten, während deine Applikation da zwar oft eine Ahnung hat, aber nix definitives nicht weiss…

…aber das geht hier eigentlich zu weit.
Ich habe lediglich geschrieben, da ich vor einiger Zeit ein
Problem durch Nacheinanderlegung deutlich besser durchführen
konnte. In dieser Sache bot sich das als Antwort an.

Das Problem (bzw. die Lösung) würde mich wirklich interessieren. War das etwa so wie oben beschrieben - oder eher sowas hier: „SELECT a.* FROM (SELECT * FROM my_table a WHERE bedingung1) WHERE bedingung2“? Letzteres führt übrigens bei mir immer zum gleichen Execution Path, der Optimizer lässt sich hier also nicht austricksen :wink:

Welche kartesische Ergebnismenge? Wenn ich schreibe „SELECT
a.*, b.* FROM a, b WHERE a.primary_key=b.foreign_key AND
a.primary_key=4711“ dann bildet die DB mit hoher
Wahrscheinlichkeit keine kartesische Ergebnismenge um

Richtig. Nimm aber mal Abfragen die sich auf einige Tabellen
mehr beziehen. Wie schliesst Du aus, dass nicht jedesmal
alles, kombiniert mit jedem, auf ein Attribut durchsucht wird.

Prinzipiell hat die DB zwei Möglichkeiten einen Join durchzuführen: HASH JOINs (die Rows aus den verschiedenen Tabellen gelesen, nach dem Vergleichskriterium sortiert und dann gejoint) und NESTED LOOPs (Für jede Row aus Tabelle A wird ein Teil/alles von Tabelle B gelesen und auf das Vergleichskriterium geprüft). Wenn du mit dem kartesischen Produkt die Nested Loops meinst, dann liegst du ja im Prinzip richtig, allerdings können auch die ihren Vorteil haben.

Ein Beispiel (das ich von Herrn Thomas Kyte, „Effective Oracle by Design“ gestohlen habe):
Man nehme eine Tanzschule mit je 1000 Männlein und Weiblein. Diese sollen der Grösse nach zu Paaren zusammengestellt werden. Erster Ansatz: Ich suche mir mal den grössten Mann und danach die grösste Frau. Das geht relativ schnell und ich habe recht flott mein erstes Paar (meine erste Row). Brauche ich nur 5 Paare, dann wird das wohl der Weg sein, den ich wählen werde. Brauche ich aber alle 1000 Paare, dann scheint eine andere Vorgehensweise effizienter: Ich sortiere alle Männer der Grösse nach, danch (oder davor) die Frauenund stelle sie in einer Reihe auf. Diese Sortierung wird relativ lange dauern (im Vergleich zum finden des jeweils grössten), aber wenn ich das mal habe, dann habe ich fast sofort auch alle Paare gefunden. Ersteres entspricht in etwa den NESTED LOOPs, letzteres dem HASH JOIN.
Daraus ergibt sich: Auch das kartesische Produkt (auch wenn es nicht wirklich eines ist, weil ja das erste Paar beim zweiten Durchgang nicht mehr berücksichtigt wird) kann so seine Vorteile haben, je nachdem, was ich von meiner DB will. Wenn ich jetzt noch einen Weg finde, das meiner DB mitzuteilen, dann habe ich auch schon gewonnen. Und Wunder: Zumindest unter Oracle gibt es dafür sogar (mindestens) zwei Möglichkeiten: Query hints und OPTIMIZER_GOAL…

Ein SELECT von einer Row ist potentiell beinahe
gleich schnell, egal ob die Tabelle eine oder eine Million
Einträge hat.

ich habe da leise Zweifel, vor allem wenn zwei Server
angesprochen werden.

Unter Oracle hat ein B*Tree-Index in der Regel 2-3 Ebenen, und zwar auch noch bei 1 Mio Datensätzen. Es müssen also bei einem Index-Read idR 3-4 Blocks gelesen werden - (fast) unabhängig von der Anzahl der Rows in der Tabelle.

Wo ich dir natürlich recht geben muss: Wenn der Zugriff über einen DB-Link erfolgt, dann kann auf der Remote-DB in vielen Fällen nur ein Full-Table-Scan (FTS) durchgeführt werden. Auskunft darüber gibt mir übrigens wieder mein herzallerliebster Execution Plan. Wenn diese FTS auf wirklich grosse tabellen zugreifen, dann sollte ich mir aber ohnehin Gedanken über Replikation machen, damit lässt sich auch dieses Porblem beheben…

ich habe das Gefühl, dass ich gerade
wieder was dazulerne …
Ich lass mich gern aufklären.

Ich hoffe mich einigermassen verständlich ausgedrückt zu haben…

Gruß
Martin

Hallo nochmal,

kann es sein, daß wir hier mehr oder weniger akademischen Betrachtungen ganz schön weit vom Thema abgekommen sind?
Es ging um eine Abfrage auf zwei Datenbanken gleichzeitig.
Bei Oracle per Datenbank-Link. Es gibt offenbar auch Datenbanklinks von Oracle nach MSSQL-Server. Aber ich habe noch immer keinen Hinweis auf eine ähnliche Konstruktion zwischen zwei SQL-Servern gefunden.

Und wie auch schon erwähnt arbeitet ein Datenbanklink oft ziemlich uneffektiv (fts), da der Optimizer ja nur weiß, was auf „seiner“ Seite passiert.

Schon, aber wie weiter? Die Ergebnismenge dann auf den Client
holen? Dort selber weiterwerken? Das KANN nicht schneller sein
als nur die Daten vom Server zu holen, die man braucht.

Das eben bleibt die Frage, schneller wär’s sicher, aber wie geht’s überhaupt…

(Wollte auch noch meinen Senf dazugeben, auch wenn’s nicht wirklich hilft)

muzel

Hallo muzel!

kann es sein, daß wir hier mehr oder weniger akademischen
Betrachtungen ganz schön weit vom Thema abgekommen sind?

Im Prinzip ja (der Fragende kann ja immer noch nicht auf zwei Datenbanken zugreifen). Allerdings halte ich diese Betrachtungen immer noch für absolut nicht akademisch. Ein effektives Arbeiten mit der Datenbank funktioniert eben nur, wenn ich weiss, was diese tut. Irgendwie geht’s immer, aber schnell und noch dazu richtig ist dann schon ein ganz anderes Thema.

Es ging um eine Abfrage auf zwei Datenbanken gleichzeitig.
Bei Oracle per Datenbank-Link. Es gibt offenbar auch
Datenbanklinks von Oracle nach MSSQL-Server. Aber ich habe
noch immer keinen Hinweis auf eine ähnliche Konstruktion
zwischen zwei SQL-Servern gefunden.

Und wie auch schon erwähnt arbeitet ein Datenbanklink oft
ziemlich uneffektiv (fts), da der Optimizer ja nur weiß, was
auf „seiner“ Seite passiert.

Ich gebe dir (fast) uneingeschränkt recht, allerdings erwähnst du da in Klammer den FULL TABLE SCAN als Synonym für uneffektiv, und das ist genau genommen einfach falsch. FTS’s sind nicht per se uneffektiv, selbst wenn ein Index vorhanden wäre. Beispiel dazu gibt’s auf Anfrage.

Schon, aber wie weiter? Die Ergebnismenge dann auf den Client
holen? Dort selber weiterwerken? Das KANN nicht schneller sein
als nur die Daten vom Server zu holen, die man braucht.

Das eben bleibt die Frage, schneller wär’s sicher, aber wie
geht’s überhaupt…

Da du so überzeugt bist, dass das schneller wäre hätte ich da gerne ein paar zusätzliche Erläuterungen dazu. Was genau kann dein Client wie schneller machen als der Server (und warum kann der Server das nicht schon selbst tun)?

(Wollte auch noch meinen Senf dazugeben, auch wenn’s nicht
wirklich hilft)

Ich erstehe meinen Senf mittlerweile im Gastronomiegroßhandel, geholfen hat’s aber auch bei mir nicht :wink:

Gruß
Martin

Hallo nochmal,

Da du so überzeugt bist, dass das schneller wäre hätte ich da
gerne ein paar zusätzliche Erläuterungen dazu. Was genau kann
dein Client wie schneller machen als der Server (und warum
kann der Server das nicht schon selbst tun)?

Mißverständnis, ich war ganz Deiner Meinung, daß die Servervariante (falls realisierbar) sicher schneller ist.
m.