Dynamisches SQL in einer Stored Procedure

[fanderlf]

Würde String zusammenbasteln und diesen irgendwie ausführen auch gehen? Bin nicht sooo tief in SPs drin, aber mich würde interessieren ob das auch gehen würde.

Abgespalten aus diesem Thread: http://entwickler-forum.de/showthrea...%C3%BCbergeben

[Ralf Jansen]

Man kann auch dynamisches SQL ausführen. Das würde aber eines der primären Funktionen einer Stored Proc zerlegen. Eben das die Stored Procs und damit die Statements darin vorkompiliert sind und das ganze potentiell performanter ist.

[Highlight=SQL]CREATE PROCEDURE usp_Meldung @columnname NVARCHAR(32), @id bigint
AS
EXECUTE ('SELECT ' + @columnname + ' FROM Meldungen WHERE Id=@id;')[/Highlight]

Edit:

Ups. Falsche Datenbank. Aber ich denke es geht in MySql ähnlich.

[defo]

bin auch kein mySQL Spezi, hier sind 2 Hinweise dazu:
https://www.otreva.com/blog/mysql-ex...iate-solution/
http://dev.mysql.com/worklog/task/?id=2793

An der Stelle sollte man wie im 1. Link auch auf die Rechte Problematik hinweisen und möglichen Missbrauch des Verfahrens.

[akretschmer]

Eben das die Stored Procs und damit die Statements darin vorkompiliert sind und das ganze potentiell performanter ist.

Potentiell aber auch langsammer, da der Planner zur Zeit der Planung die Parameter nicht kennt.

[defo]

Potentiell aber auch langsammer, da der Planner zur Zeit der Planung die Parameter nicht kennt.

Was meinst Du damit? Parameter kennt man immer erst im Moment der Ausführung.
Meinst Du der Planer arbeitet bei unterschiedlichen Parameterwerten anders?
Oder sprichst du von komplett geänderten statements "dank execute"?

[Ralf Jansen]

Potentiell aber auch langsammer, da der Planner zur Zeit der Planung die Parameter nicht kennt.

Theoretisch richtig. Dafür müsste der aber die Häufigkeit eines bestimmten Parameter Werts in der Spalte kennen um sinnvoll unterschiedliche Ausführungspläne auswählen zu können. Mir wäre jetzt nicht bewusst das irgendeine Datenbank solche Statistiken führt. Kennst du eine? Macht MySql das?

[akretschmer]

Was meinst Du damit? Parameter kennt man immer erst im Moment der Ausführung.
Meinst Du der Planer arbeitet bei unterschiedlichen Parameterwerten anders?
Oder sprichst du von komplett geänderten statements "dank execute"?

Ja, wenn der Planner weiß, wie der Parameter ist, kann er dieses Wissen verwenden.

test=*# create table bla as select 1::int as x from generate_series(1,100000) s;
SELECT 100000
Time: 162,359 ms
test=*# insert into bla values (2);
INSERT 0 1
Time: 0,216 ms
test=*# create index idx_bla on bla(x);
CREATE INDEX
Time: 112,909 ms
test=*# analyse bla;
ANALYZE
Time: 33,642 ms
test=*# explain select * from bla where x=1;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on bla  (cost=0.00..1643.01 rows=100001 width=4)
   Filter: (x = 1)
(2 rows)

Time: 0,446 ms
test=*# explain select * from bla where x=2;
                               QUERY PLAN
------------------------------------------------------------------------
 Index Only Scan using idx_bla on bla  (cost=0.29..4.31 rows=1 width=4)
   Index Cond: (x = 2)
(2 rows)

Welcher Plan sollte bei einer Funktion oder Prepared Statement nun genommen werden?

[defo]

hmm, also ich frag mich, ob das Beispiel taugt. Eigentlich ist meine Leidenschaft Planer zu verstehen relativ gering, aber in diesem Fall:
Das Statement unterscheidet sich wirklich nur durch den Wert und nicht in sich.
Für die Abfrage 2er verschiedener Werte müsste der Planer also zum gleichen Plan kommen.
Tut er dies nicht, woran liegt es dann?
die Tabelle ist auf der Spalte physikalisch partitioniert?
Hier nicht der Fall..
Die Entscheidungsgrundlagen sind mager oder irrelevant, weil kein nenneswertes Volumen oder unvollständige/unfertige statistiken?
Hier schon eher möglich. Da ich aber weder die Statistikverfahren von mysql (worum es hier geht) noch von pgsql (was Du hier verwendet hast) kenne, kann ich dazu nicht viel sagen.

[Ralf Jansen]

Das ist aus PostgreSQL?

Edit: Sehr clever wie Postgres rausbekommt ob ein Parameterwert häufig ist oder nicht. http://www.postgresql.org/docs/9.1/s...-examples.html

[defo]

Das ist aus PostgreSQL?

Edit: Sehr clever wie Postgres rausbekommt ob ein Parameterwert häufig ist oder nicht. http://www.postgresql.org/docs/9.1/s...-examples.html

Zumindest vermute ich das wegen

..as x from generate_series(1,100000)..

.
Das ist aber der Zeitpunkt-für mich- wo ich sage, "soll die db machen wie sie will". Mit diesen Überlegungen kommt man vom 100sten ins 1000ste und das je RDBMS und Fall. Ich begnüge mich damit, das anzugehen, wenn es mein System akut betrifft.

Was mir in dem Fall hier wichtiger scheint:
Wird eine SP als Funktion verwendet, also in einem Statement eingebunden, so kann sie mit einem Aufruf 1 - millionenfach gerufen werden. Dahinter folgt dann entsprechend oft das Parsing des Execute Statements oder eben nicht, wenn es statisch ist.
Auch diese Bewertung ist natürlich fallabhängig, aber im Allgemeinen vermeidet man das Statementparsing ja gerne und nicht ohne Grund.

[akretschmer]

Theoretisch richtig. Dafür müsste der aber die Häufigkeit eines bestimmten Parameter Werts in der Spalte kennen um sinnvoll unterschiedliche Ausführungspläne auswählen zu können. Mir wäre jetzt nicht bewusst das irgendeine Datenbank solche Statistiken führt. Kennst du eine? Macht MySql das?

Ja. Nein.

Siehe andere Postings von mir.

[akretschmer]

Wird eine SP als Funktion verwendet, also in einem Statement eingebunden, so kann sie mit einem Aufruf 1 - millionenfach gerufen werden. Dahinter folgt dann entsprechend oft das Parsing des Execute Statements oder eben nicht, wenn es statisch ist.
Auch diese Bewertung ist natürlich fallabhängig, aber im Allgemeinen vermeidet man das Statementparsing ja gerne und nicht ohne Grund.

Ja. Aber man kann dabei auch hart auf die Fresse fallen.

[akretschmer]

Das ist aus PostgreSQL?

Edit: Sehr clever wie Postgres rausbekommt ob ein Parameterwert häufig ist oder nicht.

Ja, Funktionert auch recht gut und ist auch noch steuerbar, man kann die Anzahl der Werte im Histogramm noch ändern je Spalte, geht via alter table ...

[defo]

Ja. Aber man kann dabei auch hart auf die Fresse fallen.

Möglich, ist mir noch nicht passiert, aber das bedeutet ja nichts. Die Wahrscheinlichkeit, dass es passiert scheint mir aber relativ gering.
Häufig weiß ein Entwickler offenbar nicht mal, dass soetwas wie ein Planer existiert. Und spätestens seit JPA und Hibernate ist DB Zugriff schon auf einem viel viel höheren Level eine Blackbox. (Was leider gar nicht bedeutet, dass man keine Performanceprobleme mit dem Kram hat, aber wenn ich Physik oder Implementierungsgegebenheiten zwanghaft ignorieren möchte, bin ich dann wohl auch selbst schuld)

Auch wenn es ein interessantes Thema ist, dem TE hilft es vermutlich nicht (für mysql), weil es sehr speziell ist.

[Ralf Jansen]

Auch wenn es ein interessantes Thema ist, dem TE hilft es vermutlich nicht (für mysql), weil es sehr speziell ist.

Fanderlf hat glaube ich die Macht diesen Teilthread abzuspalten. Mich würde schon interessieren ob das ein Postgres Alleinstellungsmerkmal ist und was (bzw. wie) das andere DBs handhaben.