David, the reason theyre faster in PL/SQL is that postgres is written in C.
C is just faster than perl. Databases compile stored procedures down into a
much more optimized (when compared to perl or a straight query) form. Oracle
even uses a LIFO so the most frequently used procedures are actually stored
right in ram, ready to run without the slightest hesitation. To do the same
in perl, you'd have to:
invoke the perl interpreter
load the module
parse the script
connect to the database
issue the query
move the data across the bus
parse the data
[do whatever with output]
and yes, that's what i mean about using perl for complex de-duping and
nested selects. perl is just faster than sql for nested selects and
multilevel queries. unless of course youre using PL/SQL in which case it
gets optimized.
alex
-----Original Message-----
From: David Link [mailto:dlink@soundscan.com]
Sent: Wednesday, November 21, 2001 9:21 AM
To: Alex Avriette
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance: Perl-DBI vs. PG Stored Procedures
How does the fact that the program is stored in the postgres make it
faster? Or perhaps the question: How much performance overhead is
there to use the Perl:DBi and Perl:DBD packages? -- Do they rely on
ODBC?
Also, what do you mean by doing nested selects in perl with arrays and
hashes? Do you mean break the SQL query up into parts and run them
separately and combine them in Perl itself?
Thanks for your reply.
David
