Performance: Perl-DBI vs. PG Stored Procedures

Yes. The PL/SQL programs will be faster since theyre stored in postgres.

Doing things like nested selects, however, will be faster in perl with
arrays/hashes than they would be in sql. you might also consider dropping
any indexes you have before inserting and then re-creating them. this is
provided, of course, you only have one concurrent user.

alex

-----Original Message-----
From: David Link [mailto:dlink@soundscan.com]
Sent: Tuesday, November 20, 2001 11:10 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Performance: Perl-DBI vs. PG Stored Procedures

Hi,

Could there be performance gains by using PG stored procedures rather
than using generic Perl DBI programs?

Namely for a task that loops thru a cursor of several 50,000 rows and
does UPDATES or INSERTS back into that same table. It is takes hours.

Thanks, David

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

Alex Avriette wrote:

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