small dbi help

Discussion in 'Perl Misc' started by debraj, Nov 11, 2003.

  1. debraj

    debraj Guest

    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
    debraj, Nov 11, 2003
    #1
    1. Advertising

  2. debraj

    Thomas Kratz Guest

    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
    Thomas Kratz, Nov 11, 2003
    #2
    1. Advertising

  3. (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\\
    Brian McCauley, Nov 11, 2003
    #3
  4. debraj

    Mina Naguib Guest

    -----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/sbc1eS99pGMif6wRAjzqAJ9bykpxVKAxVbdw8K32q3aQhLe9KwCggL91
    +3o3K5PmDASrrW2/+44FKaE=
    =9J9e
    -----END PGP SIGNATURE-----
    Mina Naguib, Nov 12, 2003
    #4
  5. debraj

    Tore Aursand Guest

    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 <>
    Tore Aursand, Nov 12, 2003
    #5
  6. debraj

    Mina Naguib Guest

    -----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++oqcMLOET962AowCwCgi9cM
    ag92mfourgzEOnnjHkEE8+8=
    =UUGB
    -----END PGP SIGNATURE-----
    Mina Naguib, Nov 15, 2003
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. James Hull
    Replies:
    2
    Views:
    698
    Slaven Rezic
    Oct 13, 2003
  2. ulloa
    Replies:
    1
    Views:
    523
    Juha Laiho
    Jul 22, 2004
  3. Jerome Hauss
    Replies:
    0
    Views:
    166
    Jerome Hauss
    Oct 13, 2004
  4. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    173
  5. Tim Haynes
    Replies:
    3
    Views:
    140
    Ron Reidy
    Sep 13, 2003
Loading...

Share This Page