Tabellenname in SQL konstruieren

Hallo allerseits,

zur Zeit bin ich dabei eine alte Webanwendung neu zu schreiben. Dabei werfe ich auch alte MySQL Sünden über Bord und steige auf PostgreSQL um. Da kam mir aber auch die Idee ein paar Abfragen einzusparen und nun stehe ich vor einem Problem.

Jeder Nutzer der Anwendung ist in der Tabelle users gespeichert und wird generell per uid referenziert. Jeder Nutzer hat eine eigene Tabelle in der Datenbank, welche die uid enthält. Aber die Aufrufe in der Anwendung laufen per loginname in der URL.

Bisher lief das ungefähr so:
SQL: SELECT uid FROM users WHERE loginname=user1;
Skript: $tabellenname = ‘objects_’.$SQL-Ergebnis
SQL: SELECT * FROM $tabellenname;

Schön wäre es, wenn ich das in einer einzigen Abfrage hinkriege. Oder wenigstens ohne den Umweg über das Skript. Ein Gedanke waren Subqueries, doch das klappt irgendwie nicht, selbst wenn ich den Tabellennamen komplett in die user Tabelle schreibe.

SELECT t.* FROM (SELECT tab FROM user WHERE loginname=‘user1’) AS t;

Obriges liefert mir den Tabellennamen, aber nicht den Inhalt selbiger.

Hab hier im Forum was über Execute gelesen, doch damit komme ich irgendwie nicht klar, ständig werden die Anführungszeichen angemeckert. Doch vielleicht verstehe ich das Prinzip auch nicht richtig.

Wie dem auch sei, vielleicht kann mir jemand weiterhelfen oder hat sogar ne ganz andere, verwegene Idee. Bin für alles offen.

Besten Dank im voraus.

Adios

PS: Ach ja, eine Frage die sich stellt, ist natürlch auch die der Performance. Normalweise heißt es ja, das die Abfragen eine Anwendung verlangsamen, bzw. Ressourcen fressen, drum will ich da auch einsparen. Kann man das aber bei allen Lösungen so verallgemeinern?

Du könntest jedem User ein eigenes Schema geben und mittels alter user set search_path = den passend setzen. Dann kann jeder eine Tabelle ‘objekte’ haben - in seinem search_path.

Oder eine Tabelle für alle, mit einer Spalte für den Usernamen. Bei Abfragen dann prüfen, ob dieser gleich dem Loginnamen ist. Man kann da auch einen VIEW machen drauf.


Das mit Execute ist übrigens was anderes - das brauchst Du für dynamische SQL-Befehle innerhalb von Stored Procs.


Andreas

Denke das mit Schemas ist eher nix, weil ich den Zugriff nur mit dem Nutzer nachen will, den ich in der Webanwendung eingetragen habe. Wäre zwar auch aus Sicherheitsgründen eine interessante Alternative hier zu unterscheiden, doch irgendwie stelle ich mir was einfacheres vor, weshalb ich nicht ein neues Datenbankdesign festlegen muss. Müsste ja dann die entweder ohne Passwort für die Schemas arbeiten (da sowohl gelesen, als auch geschrieben werden muss, ein no-go) oder diese in Klartext speichern. Zudem bräuchte ich dann immer noch eine Abfrage mehr.

Die eine Tabelle für alle möchte ich aus verschiedenen Gründen vermeiden. Zum einen bin ich Größenwahnsinnig und kann mir deshalb vorstellen, dass sich da mal Abermillionen von Einträgen befinden (das wird schon bei einer anderen Tabelle schwer, für dich es mal potentiell 36 Trilliarden Einträge geben könnte, da muss ich dann irgendwann mal splitten… und das wäre im Grunde dann die kleinere von beiden Tabellen). Zum anderen will ich in den Nutzertabellen einfache IDs verwenden, die man sich merken könnte (serial), aber auch auf den Datenschutz achten (kleinliches Szenario, dass man anhand der ID merken kann wer etwas direkt vor einem selbst gemacht hat).

Ähm, wenn du es schon neu machen willst, mach es richtig: alle Daten in eine Tabelle und eine zusätzliche Spalte mit der UID drin. Alles andere bringt nur viel, viel Ärger beim Zusammensetzen der Queries.

Die UID kannst du dann direkt über einen Subselect mit einer Suche über den Nicknamen ansprechen - oder du baust dir passenderweise einen View, der die Spalte mit dem Usernamen schon mit enthält.

Wie oben geschrieben, würde ich aus verschiedenen Gründen gern einzelne Tabellen verwenden.

Wenn eine einzige Tabelle “richtiger” sein soll, würde ich gerne wissen wieso. Denn ich persönlich stelle mir vor, dass ein Query über eine gezielte Tabelle mit ein paar Tausend Einträgen schneller ist als auf eine allgemeine Tabelle mit Millionen von Einträgen. Lasse mich natürlich gerne belehren, so lange ich es nachvollziehen kann.

So ganz paßt das aber nicht zu Deinem ersten Beitrag. Zudem stimmt es nicht. Du kannst ja die Zugriffe auf die einzelnen Schemas entsprechend regeln. Nur der Berechtigte darf halt schreiben.


Das mit dem einen User: auch das paßt nicht ganz zum ersten Beitrag. Egal.

Die eine Tabelle für alle möchte ich aus verschiedenen Gründen vermeiden. Zum einen bin ich Größenwahnsinnig und kann mir deshalb vorstellen, dass sich da mal Abermillionen von Einträgen befinden (das wird schon bei einer anderen Tabelle schwer, für dich es mal potentiell 36 Trilliarden Einträge geben könnte, da muss ich dann irgendwann mal splitten… und das

36 Trilliarden. Ach so.

wäre im Grunde dann die kleinere von beiden Tabellen). Zum anderen will ich in den Nutzertabellen einfache IDs verwenden, die man sich merken könnte (serial), aber auch auf den Datenschutz achten (kleinliches Szenario, dass man anhand der ID merken kann wer etwas direkt vor einem selbst gemacht hat).

Nun ja, Du hast Anregungen erhalten.


Andreas

Siehe mein Vorschlag.

Wenn eine einzige Tabelle “richtiger” sein soll, würde ich gerne wissen wieso. Denn ich persönlich stelle mir vor, dass ein Query über eine gezielte Tabelle mit ein paar Tausend Einträgen schneller ist als auf eine allgemeine Tabelle mit Millionen von Einträgen. Lasse mich natürlich gerne belehren, so lange ich es nachvollziehen kann.

Weil Du bei einer Tabelle eben eine Tabelle mit einem Index hast. Das belegt wenig Platz in den Systemtabellen etc. Viele Tabellen belegen dementsprechend vie Platz in den Systemtabellen, Du braucht auch für jede die Indexe etc.
Bei vielen Tabellen ist die Chance, Daten für eine aktuell laufende Abfrage aus dem Cache holen zu können geringer, als wenn alles in einer Tabelle wäre, denn es könnt ja sein, daß eine ähnliche Anfrage, aber anderer User, grad eben lief.

Nicht daß PG nicht mit Millionen von Tabellen könnte The million table challenge - Greg’s Postgres stuff , aber Du willst vielleicht mal eines Tages eine Abfrage über alles User machen. Das wird dann echt gruselig, wenn diese auf hunderte Tabellen verteilt sind. Nochmal: beides geht, beides hat Vor- wie Nachteile. Nur Du kennst Deine Anforderung und Deine Ziele…


Andreas

Da war ich vielleicht nicht ganz deutlich. Bei den Nutzern handelt es sich um Nutzer der Webanwendung. Und diese Webanwendung greift mit einem Nutzeraccount auf die Datenbank zu. Die Nutzer haben also keine eigene Tabelle im eigentlichen Sinn, sondern eine Tabelle, die ihnen zugewiesen ist, aber von der Anwendung mit dem einen Account verwaltet wird.

(Wie gesagt) Potentiell, weil ich den Algorithmus für die ID so ausgelegt habe. Das das mit ziemlicher Sicherheit nie erreicht wird (und wenn doch, wird es eh Änderungen an der DB geben, doch die ID muss beibehalten werden), ist mir klar, aber man sollte bei Konzepten auch mit Extremen rechnen, sonst kann es ganz schnell passieren, dass man sich selbst an die Wand spielt. Also rechne ich mal mit einem Extrem und teile die Daten auf einzelne Nutzer auf.

Dafür bin ich auch sehr dankbar. Entweder führt es zu einer Lösung oder durch das Ausschlussverfahren dazu, dass es einfach nicht geht wie ich es mit vorstelle. Und der Input mit den Schemas hilft mir vielleicht bei einem anderen Problem, was auch schon klasse ist :slight_smile: .

Ganz generell: viele Daten über viele Tabellen zu verteilen macht nur selten Sinn. Speziell, wenn du vielleicht mal die Daten kombinieren musst … dann setzt du nämlich Tausende Abfragen ab - und das wird dann richtig langsam.

Datenbanken sind darauf optimiert, aus großen Datenmengen ggf. einzelne Informationen schnell herauszusuchen. Das ist deren Hauptaufgabe, das können sie gut, das üben sie jeden Tag. Mein Vorschlag wäre, dass du einfach mal probierst, wie sich deine 36 Trilliarden Datensätze in einer Tabelle machen …

Um mal den Speicherplatz außen vor zu lassen… was mich eher interessiert ist Geschwindigkeit. Was bringt eine optimalere Performance? Eine Tabelle oder eine Aufteilung selbiger?

Das sich Queries mit den selben Ergebnissen wiederholen ist unwahrscheinlich. Aber gut, hier eine grobe Vorstellung von dem worum es geht. (Reduziert und vereinfacht auf relevante Daten für eine Standardabfrage.)

Bisheriges Design
Tabelle user: user_id, loginname
Tabelle objekte_user: objekt_id, objekt_typ_o_adresse, eltern_objekt_id, adress_id, name, beschreibung
Tabelle adressen: adress_id, adresse

objekt_id ist eine serial, objekt_typ_o_adresse ist entweder “ordner” oder eine adress_id, adress_id die 36 Trilliaden Sache.

Nutzer surft auf eine URL, in der loginname sowie objekt_id enthalten sind.

Skript sucht nach der user_id zum gelieferten loginname.
Skript sucht in der entsprechenden Objektetabelle nach der objekt_id. (Hiernach kommt eine kleine Ausgabe.)
Abschließend sucht das Skript aber nach allen Objekten die die objekt_id der letzten Abfrage als eltern_objekt_id haben und nicht objekt_typ_o_adresse “ordner” und verknüpft diese Anhand der adress_id mit der Tabelle adressen.

(Adressen ist in einer eigenen Tabelle, für den nicht unwahrscheinlichen Fall, dass eine Adresse mehrmals von mehreren Nutzern geführt wird. Die Nutzer sollen allerdings die Möglichkeit haben den Einträgen eigenen Namen und Beschreibungen zu geben.)

In meinen Augen insgesamt ein normalisiertes Datenbankdesign mit der Hoffnung auf gute Performance.

Man könnte nun die Tabellen für objekte kombinieren und gewissermaßen aus user_id und objektid einen geteilten Schlüssel machen. Technisch keine Sache, aber die Geschwindigkeit interessiert mich. Das Ergebnis muss im Sekundenbruchteil parat sein.

Und natürlich würde ich da schon gerne wissen, bevor ich anfange die Datenbank zu füllen :wink: .

Vermutlich komme ich nicht ums experimentieren herum, bin aber für jede Info dankbar :slight_smile: .

Mahlzeit.

Was auch immer deine Anwendung daraus macht - das wird die Grundlage für deine Performance Optimierung sein.


Und natürlich würde ich da schon gerne wissen, bevor ich anfange die Datenbank zu füllen > :wink: > .

Dann fang an zu testen … und zwar mit Daten, die denen ähnlich sind, die du später nutzt … und mit Anfragen, die aus deiner Applikation kommen.

Werde ich am Wochenende mal ein Skript schreiben, um das zu machen. Aber trotzdem würde mich immer noch die ursprüngliche Frage mit der Konstruktion des Tabellennamens interessieren. Jemand ne Idee?

Um deine ursprüngliche Frage zu beantworten: in SQL bekommst du das nicht hin.

In einer Funktion könntest du den Querystring dynamisch zusammensetzen … ist aber Overhead, dafür eine Funktion zu schreiben.


Dein Problem ist eine Folge deiner Idee mit den vielen Tabellen - wofür relationale Datenbanken jedoch nicht gedacht sind.

Speicherplatz sollte man aber nicht außen vor lassen, wenn man Geschwindigkeit will. Die höchsten Kosten verursachen nun einmal Festplattenzugriffe.

Das sich Queries mit den selben Ergebnissen wiederholen ist unwahrscheinlich.

Ich sprach von änlichen Abfragen und Cache.

Aber gut, hier eine grobe Vorstellung von dem worum es geht. (Reduziert und vereinfacht auf relevante Daten für eine Standardabfrage.)

Bisheriges Design
Tabelle user: user_id, loginname
Tabelle objekte_user: objekt_id, objekt_typ_o_adresse, eltern_objekt_id, adress_id, name, beschreibung
Tabelle adressen: adress_id, adresse

objekt_id ist eine serial, objekt_typ_o_adresse ist entweder “ordner” oder eine adress_id, adress_id die 36 Trilliaden Sache.

Und Du hast nun für jeden User eine eigene Objekt-Tabelle?

Warum kann objekt_typ_o_adresse einmal ein String (“ordner” ) sein und einmal ein Int (adress_id)? Warum nicht NULL, wenn es keine adress_id ist? Und wie kommst Du bei 6 Milliarden Menschern derzeit auf 36 Trilliarden Adressen?


Nutzer surft auf eine URL, in der loginname sowie objekt_id enthalten sind.

Skript sucht nach der user_id zum gelieferten loginname.
Skript sucht in der entsprechenden Objektetabelle nach der objekt_id. (Hiernach kommt eine kleine Ausgabe.)
Abschließend sucht das Skript aber nach allen Objekten die die objekt_id der letzten Abfrage als eltern_objekt_id haben und nicht objekt_typ_o_adresse “ordner” und verknüpft diese Anhand der adress_id mit der Tabelle adressen.

Aha. Das Script sucht anhand der Resultate des verhergehenden Selects, oder wie? Das Script ist dann sicher noch PHP oder sowas, ja? Und ich dachte, es sollte schnell werden…

Warum nicht alles in einer Abfrage?

Schnellschuß:

test=*# select * from objekte_user;
 objekt_id | user_id | typ | eltern_objekt | adress_id
-----------+---------+-----+---------------+-----------
         1 |       1 |     |               |         1
         2 |       1 |     |             1 |         1
(2 rows)

test=*# select * from adressen ;
 id | adresse
----+----------
  1 | adresse1
  2 | adresse2
(2 rows)

test=*# select u.login, o.*,a.adresse from objekte_user o left join usr u on (u.usr_id=o.user_id) left join adressen a on (a.id=o.adress_id) where o.eltern_objekt in (select objekt_id from objekte_user o left join usr u on (u.usr_id=o.user_id) ) and u.login='foo';
 login | objekt_id | user_id | typ | eltern_objekt | adress_id | adresse
-------+-----------+---------+-----+---------------+-----------+----------
 foo   |         2 |       1 |     |             1 |         1 | adresse1
(1 row)

(man könnt schauen, ob es was bringt, im inneren select noch den user zu begrenzen)



(Adressen ist in einer eigenen Tabelle, für den nicht unwahrscheinlichen Fall, dass eine Adresse mehrmals von mehreren Nutzern geführt wird. Die Nutzer sollen allerdings die Möglichkeit haben den Einträgen eigenen Namen und Beschreibungen zu geben.)

6 Milliarden Menschen nutzen aus einem Pool von 36 Trillionen Adressen diese sehr wahrscheinlich also gemeinsam.

In meinen Augen insgesamt ein normalisiertes Datenbankdesign mit der Hoffnung auf gute Performance.

Ja, wenn man passend abfragt. Eine Aufteilung der Tabelle auf die User ist IMHO komplett unsinnig. Falls Du das dennoch noch immer willst, wirst Du zum Abfragen dynamische SQL-Befehle generieren dürfen. Oder den ‘Trick’ über Schemas gehen.

Man könnte nun die Tabellen für objekte kombinieren und gewissermaßen aus user_id und objektid einen geteilten Schlüssel machen. Technisch keine Sache, aber die Geschwindigkeit interessiert mich. Das Ergebnis muss im Sekundenbruchteil parat sein.

Wird es. Passende Indexe vorausgesetzt. Und eine aktuelle Version. Man braucht im übrigen keine zusammengesetzten Schlüssel bzw. Indexe. Das hier ist nicht MySQL, was pro select und table nur einen Index nutzen kann.



Andreas