Thread: 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
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: > > 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
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