Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > small dbi help

Reply
Thread Tools

small dbi help

 
 
debraj
Guest
Posts: n/a
 
      11-11-2003
Hi,

Just wanted a small help. I have a perl script (OS:Linux) in which I
am repeatedly evoking sqlplus, for connecting to the database(Oracle)
because the select statement changes often(about 500 times) and its
taking a lot of time. Now, I know this much that DBI helps in
connecting to the database and can do SQL queries but how much will it
improve the performance? The query is very simple only that the select
clause changes.

OR is there any better way to do it?

Example: select * from emp where emp_no=****;
The above is the query and in a loop the emp_no changes about 500
times.

Thanx in advance,

Debhatta
 
Reply With Quote
 
 
 
 
Thomas Kratz
Guest
Posts: n/a
 
      11-11-2003
debraj wrote:

> Hi,
>
> Just wanted a small help. I have a perl script (OS:Linux) in which I
> am repeatedly evoking sqlplus, for connecting to the database(Oracle)
> because the select statement changes often(about 500 times) and its
> taking a lot of time. Now, I know this much that DBI helps in
> connecting to the database and can do SQL queries but how much will it
> improve the performance? The query is very simple only that the select
> clause changes.
>
> OR is there any better way to do it?
>
> Example: select * from emp where emp_no=****;
> The above is the query and in a loop the emp_no changes about 500
> times.


Using DBI will help you in at least 2 ways. First you'll save process
creation time for shelling out to sqlplus 500 times.
Second you will be able to prepare the select statement with a
placeholder, so that you can execute the statement later many times
with different values for the placeholder.
The gain in speed should be quite noticable.

Like this (only a quick outline)

use DBI;
my $db = DBI->new(<insert connection parameters here>);
my $st = $db->prepare("select * from emp where emp_no=?");

for my $emp (1..500) {
$st->execute($emp);
$st->dump_results();
}

Thomas

 
Reply With Quote
 
 
 
 
Brian McCauley
Guest
Posts: n/a
 
      11-11-2003
http://www.velocityreviews.com/forums/(E-Mail Removed) (debraj) writes:

> Just wanted a small help. I have a perl script (OS:Linux) in which I
> am repeatedly evoking sqlplus, for connecting to the database(Oracle)
> because the select statement changes often(about 500 times) and its
> taking a lot of time. Now, I know this much that DBI helps in
> connecting to the database and can do SQL queries but how much will it
> improve the performance?


It will probably be a lot (several orders of magnitude) more efficient
than creating a process, creating a connection, then tearing it all
down again for every record you retrive.

> The query is very simple only that the select
> clause changes.
>
> OR is there any better way to do it?
>
> Example: select * from emp where emp_no=****;
> The above is the query and in a loop the emp_no changes about 500
> times.


In that case there is no need for the SELECT to change, only the
parameter.

my $sth = $db->prepare('select * from emp where emp_no=?');

for my $emp_no ( @emp_nos ) {
$sth->execute($emp_no);
my $emp_record = $sth->selectrow_hashref;
$sth->finish; # Redundant if emp.emp_no is PK
# Do stuff with $emp_record
}

--
\\ ( )
. _\\__[oo
.__/ \\ /\@
. l___\\
# ll l\\
###LL LL\\
 
Reply With Quote
 
Mina Naguib
Guest
Posts: n/a
 
      11-12-2003
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



debraj wrote:
> Hi,
>
> Just wanted a small help. I have a perl script (OS:Linux) in which I
> am repeatedly evoking sqlplus, for connecting to the database(Oracle)
> because the select statement changes often(about 500 times) and its
> taking a lot of time. Now, I know this much that DBI helps in
> connecting to the database and can do SQL queries but how much will it
> improve the performance?


The answer is yes, it will definately improve the performance. At a bare minimum it will illiminate
the forking (very expensive) and the pipes between your process and sqlplus.

> The query is very simple only that the select
> clause changes.
>
> OR is there any better way to do it?


Yes - use DBI and DBD::Oracle

>
> Example: select * from emp where emp_no=****;
> The above is the query and in a loop the emp_no changes about 500
> times.


1. Make sure the emp_no column is indexed
2. See perldoc DBI, especially the section on "placeholders" - it will allow you to prepare the SQL
statement once and execute it multiple times with different values
3. Try "where emp_no in ('x', 'y', 'z', . . .)" - I don't know how efficient oracle is with this
type of query, but it might be worth a shot while you're benchmarking.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/sbc1eS99pGMif6wRAjzqAJ9bykpxVKAxVbdw8K32q3aQhLe9Kw CggL91
+3o3K5PmDASrrW2/+44FKaE=
=9J9e
-----END PGP SIGNATURE-----

 
Reply With Quote
 
Tore Aursand
Guest
Posts: n/a
 
      11-12-2003
On Tue, 11 Nov 2003 23:29:36 -0500, Mina Naguib wrote:
> 3. Try "where emp_no in ('x', 'y', 'z', . . .)" - I don't know how
> efficient oracle is with this type of query, but it might be worth a
> shot while you're benchmarking.


A friend of mine did some work against Oracle once, and he encountered
problems when the IN() expression contained more than x elements. I don't
remember what the excact limit was, but I think it was about 1,000.

Anyone have some more information on this? I have almost nevner used
Oracle - and I don't have it available - so...


--
Tore Aursand <(E-Mail Removed)>
 
Reply With Quote
 
Mina Naguib
Guest
Posts: n/a
 
      11-15-2003
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



Tore Aursand wrote:
> On Tue, 11 Nov 2003 23:29:36 -0500, Mina Naguib wrote:
>
>>3. Try "where emp_no in ('x', 'y', 'z', . . .)" - I don't know how
>>efficient oracle is with this type of query, but it might be worth a
>>shot while you're benchmarking.

>
>
> A friend of mine did some work against Oracle once, and he encountered
> problems when the IN() expression contained more than x elements. I don't
> remember what the excact limit was, but I think it was about 1,000.
>
> Anyone have some more information on this? I have almost nevner used
> Oracle - and I don't have it available - so...


I ran into the same limit once too, I think it was either 100, 500 or 999 but I'm not sure.

If "in()" is found to be the way to go resource-and-speed-wise (I doubt it), then the limitation
could be easily worked around with perl with a while loop and splice.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/tXL2eS99pGMif6wRAkRNAJ9ZXmlqgqTm++oqcMLOET962AowCw Cgi9cM
ag92mfourgzEOnnjHkEE8+8=
=UUGB
-----END PGP SIGNATURE-----

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Small cameras getting too small? GRL Digital Photography 50 02-03-2006 03:12 AM
DBI Mysql storing DBI:binary, trouble with character 26 Jerome Hauss Ruby 0 10-13-2004 03:04 PM
DBI and DBI::Oracle packages configuration ulloa Perl 1 07-22-2004 05:52 PM
DBI problem : How can I load quickly one huge table with DBI ??. Tim Haynes Perl Misc 3 09-13-2003 03:43 AM
Mason, DBI, and DBI::Pg Asby Perl Misc 0 07-24-2003 09:04 PM



Advertisments