Re: DBI vs. piping query to Mysql
If you are running apache with mod_perl and if the site has more than a
couple of visitors an hour and you use Apache::DBI for connection
pooling, you can bet that you will speed up by using DBI. A lot. A
whole lot.
On Wed, 2002-04-03 at 18:33, Peter Scott wrote:
At 04:28 PM 4/3/02 -0500, Kevin Old wrote:
Hello all,
I am a consultant brought in to manage and restructure some Perl scripts that
were written some time ago. The programmer at that time was using the
following code to do a query from within a CGI page.
${query} = SELECT ccyymmddhh FROM inventory ORDER BY ccyymmddhh ; ;
open( INPUT, echo \${query}\ |
/usr/local/mysql/bin/mysql -A -q -N gso| ) ;
{ccyymmddhh} = INPUT ;
chomp( {ccyymmddhh} ) ;
close( INPUT ) ;
*Shudder*
I think that I should clean this up and reprogram this to use DBD::mysql
rather than the way he does it here.
That's an understatement.
Anyone have any idea if it would improve performance?
The only way to be sure is to try both ways, but... I would bet long odds
that the performance will be greatly improved. The above has to fire off a
subprocess and build up and tear down a connection for each query. If the
DBI way turns out to be slower, look me up at the Perl Conference and I'll
buy you a drink. So, I suspect, will Tim Bunce :-)
I'd love to hear from people that have gone doing it this way to using DBI.
Obviously I can run benchmarks before and after and see which takes longer,
and I think that using DBI is not only much easier to read and manage, but
probably a little faster. Just seeking the advice of others.
Go with easier to read and manage first. The above code is NOT capable of
being reused in obvious ways (suppose $query contained quote marks or shell
metacharacters).
--
Peter Scott
Pacific Systems Design Technologies
http://www.perldebugged.com
RE: DBI vs. piping query to Mysql
Wow, that's some silly code:-) Yes, you can do this and in some instances
running through mysql command might be faster, but the portability, design,
expandability of this sucks. You can use placeholders, long column queries,
etc... Did this guy write this years and years ago or something, or was he
just trying to finish up and hurry and leave? DBI/DBD::MySQL is the way to
go, definitely. The interface allows you to do way more things, with better
control than the piping does or ever will provide.
Ilya
-Original Message-
From: Kevin Old
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: 4/3/02 2:28 PM
Subject: DBI vs. piping query to Mysql
Hello all,
I am a consultant brought in to manage and restructure some Perl scripts
that
were written some time ago. The programmer at that time was using the
following code to do a query from within a CGI page.
${query} = SELECT ccyymmddhh FROM inventory ORDER BY ccyymmddhh ;
;
open( INPUT, echo \${query}\ |
/usr/local/mysql/bin/mysql -A -q -N gso| ) ;
@{ccyymmddhh} = INPUT ;
chomp( @{ccyymmddhh} ) ;
close( INPUT ) ;
I think that I should clean this up and reprogram this to use DBD::mysql
rather than the way he does it here.
Anyone have any idea if it would improve performance?
I'd love to hear from people that have gone doing it this way to using
DBI.
Obviously I can run benchmarks before and after and see which takes
longer,
and I think that using DBI is not only much easier to read and manage,
but
probably a little faster. Just seeking the advice of others.
Thanks,
Kevin
[EMAIL PROTECTED]
Re: DBI vs. piping query to Mysql
At 04:28 PM 4/3/02 -0500, Kevin Old wrote:
Hello all,
I am a consultant brought in to manage and restructure some Perl scripts that
were written some time ago. The programmer at that time was using the
following code to do a query from within a CGI page.
${query} = SELECT ccyymmddhh FROM inventory ORDER BY ccyymmddhh ; ;
open( INPUT, echo \${query}\ |
/usr/local/mysql/bin/mysql -A -q -N gso| ) ;
{ccyymmddhh} = INPUT ;
chomp( {ccyymmddhh} ) ;
close( INPUT ) ;
*Shudder*
I think that I should clean this up and reprogram this to use DBD::mysql
rather than the way he does it here.
That's an understatement.
Anyone have any idea if it would improve performance?
The only way to be sure is to try both ways, but... I would bet long odds
that the performance will be greatly improved. The above has to fire off a
subprocess and build up and tear down a connection for each query. If the
DBI way turns out to be slower, look me up at the Perl Conference and I'll
buy you a drink. So, I suspect, will Tim Bunce :-)
I'd love to hear from people that have gone doing it this way to using DBI.
Obviously I can run benchmarks before and after and see which takes longer,
and I think that using DBI is not only much easier to read and manage, but
probably a little faster. Just seeking the advice of others.
Go with easier to read and manage first. The above code is NOT capable of
being reused in obvious ways (suppose $query contained quote marks or shell
metacharacters).
--
Peter Scott
Pacific Systems Design Technologies
http://www.perldebugged.com
RE: DBI vs. piping query to Mysql
if this is a single query in a web page, at least he avoids the hassle of
trying to install DBI and DBD.. :)
we all know how many hundreds of messages we see from people who can't get
their DBD drivers to make properly.
what kind of overhead does DBI and DBD add to your script in a plain cgi
environment where these modules are loaded with each execution?
this is ugly, but maybe was a simple solution at the time to the need for a
single simple query result in a web page.
use PHP instead. :)
just kidding. i love the DBI stuff, just wish I didn't have to battle the
DBA's to get DBD::Oracle built on sites that have no local oracle database.
if this is the only thing changed in the web page, i would love to see the
benchmarks.
Job
-Original Message-
From: Kevin Old [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 03, 2002 4:28 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: DBI vs. piping query to Mysql
Hello all,
I am a consultant brought in to manage and restructure some Perl scripts
that
were written some time ago. The programmer at that time was using the
following code to do a query from within a CGI page.
${query} = SELECT ccyymmddhh FROM inventory ORDER BY ccyymmddhh ; ;
open( INPUT, echo \${query}\ |
/usr/local/mysql/bin/mysql -A -q -N gso| ) ;
@{ccyymmddhh} = INPUT ;
chomp( @{ccyymmddhh} ) ;
close( INPUT ) ;
I think that I should clean this up and reprogram this to use DBD::mysql
rather than the way he does it here.
Anyone have any idea if it would improve performance?
I'd love to hear from people that have gone doing it this way to using DBI.
Obviously I can run benchmarks before and after and see which takes longer,
and I think that using DBI is not only much easier to read and manage, but
probably a little faster. Just seeking the advice of others.
Thanks,
Kevin
[EMAIL PROTECTED]
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
