small dbi help

[debraj]

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

 

[Thomas Kratz]

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

 

[Brian McCauley]

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\\

 

[Mina Naguib]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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/sbc1eS99pGMif6wRAjzqAJ9bykpxVKAxVbdw8K32q3aQhLe9KwCggL91
+3o3K5PmDASrrW2/+44FKaE=
=9J9e
-----END PGP SIGNATURE-----

 

[Tore Aursand]

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...

 

[Mina Naguib]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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++oqcMLOET962AowCwCgi9cM
ag92mfourgzEOnnjHkEE8+8=
=UUGB
-----END PGP SIGNATURE-----