Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

prepare statement within DBI

by Paulster2 (Priest)
on Aug 25, 2004 at 14:51 UTC ( #385694=perlquestion: print w/ replies, xml ) Need Help??
Paulster2 has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to figure out a better way of doing this. The following code works just fine, but am wondering how to do it better, ie: Can I put both sql statements within the same prepare statement? Will it even make that big of a difference? Is the real time spent in the connection of the DB vs the actual time spent on the queries? : (Please forgive typo's as I have to retype from another system!)

use DBI; $dsn = "dbi:Oracle:host=<ip withheld>;sid=ORA8I;port=1521"; $user = "<data withheld>"; $password = "<data withheld>"; $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit => 1}); $sth = $dbh->prepare("select count(*) from table_one"); $sth->execute(); $row1 = $sth->fetchrow_array; $sth = $dbh->prepare("select count(*) from table_two"); $sth->execute(); $row2 = $sth->fetchrow_array; print "$row1, $row2 \n"; $sth->finish(); $dbh->disconnect();

It seems that if I could combine the two sql statements into one statement that it would help it function cleaner. Mind you that with just two statements, it's not going to make much of a difference, but if I want to make it more complicated, it has the potentiality of creating a big diffence, at least to my way of thinking.

I have checked on SuperSearch, which I believe that the answer is there. I just don't seem to be coming up with the combination of parameters in order to find what I need. Hoping someone out here has a suggestion.

Thanking you all in advance ;-)

Paulster2


You're so sly, but so am I. - Quote from the movie Manhunter.

Comment on prepare statement within DBI
Download Code
Re: prepare statement within DBI
by atcroft (Monsignor) on Aug 25, 2004 at 15:13 UTC

    Correct me if I am wrong, but basically you're asking if you can do something on the order of:

    $sth = $dbh->prepare("SELECT COUNT(*) FROM ?"); foreach my $table_name (qw(table_one table_two)) { $sth->execute($table_name); my $rowcount = $sth->fetchrow_array; print "Number of rows in $table_name: $rowcount\n"; }

    According to Chapter 5 ("Interacting with the Database") of Programming the Perl DBI (by Alligator Descartes and Tim Bunce),

    "On most databases, this statement (statement on p.123, similar to above) would actually fail to parse the prepare() call, because placeholders can generally be used only for literal values. This is because the database needs enough information to create the query execution plan, and it can't do that with incomplete information (e.g., if it doesn't know the name of the table)." (p.124)

    Hope that helps.

Re: prepare statement within DBI
by dragonchild (Archbishop) on Aug 25, 2004 at 15:23 UTC
    The cost of preparing the statement, while non-zero, usually is effectively zero when compared with the cost of executing the statement.

    Additionally, there is the cost of maintaining the code. One prepare() call has traditionally been used for preparing one statement. Breaking that could cause issues when someone else tries to maintain your code.

    As always, I would suggest trying it out and seeing what happens. The results might surprise us all.

    Caveat - as with every non-vanilla usage of DBI, this is going to be dependent on the specific RDBMS you're going to be using. You have been warned.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

Re: prepare statement within DBI
by mpeppler (Vicar) on Aug 25, 2004 at 15:29 UTC
    Sending more than one SQL statement in a prepare() is in general not supported. Exceptions exist for databases such as Sybase and MS-SQL where multiple statements are supported natively by the server.

    However, you could push the actual prepare/execute/fetch loop into a subroutine which would avoid the duplicated code (minimal and simplistic implementation):

    my $row1 = fetch_count($dbh, "table_one"); my $row2 = fetch_count($dbh, "table_two"); print "$row1, $row2\n"; sub fetch_count { my $dbh = shift; my $table = shift; my $sth = $dbh->prepare("select count(*) from $table"); $sth->execute; my $row = $sth->fetch_arrayref; return $row->[0]; }
    Michael

      For most of the duplication you avoid with your fetch_count function, DBI has a built-in way of avoiding it: whenever you've got a query on which ->fetch_... is only invoked once, you can skip the separate ->prepare and ->execute by using a $db->select_... method.

      So the 4 active lines in your function become just 1:

      sub fetch_count { my ($db, $table) = @_; $db->selectrow_array("SELECT COUNT(*) FROM $table"); }

      It probably isn't worth writing a function for that, since there isn't that much duplication without it:

      my $row1 = $dbh->selectrow_array('SELECT COUNT(*) FROM table_one'); my $row2 = $dbh->selectrow_array('SELECT COUNT(*) FROM table_two');

      But you could always put it in some sort of loop:

      my ($row1, $row2) = map { $dbh->selectrow_array("SELECT COUNT(*) FROM $_") } qw<table_one table_two>;

      (By the way, I think your ->fetch_arrayref is a typo.)

      Smylers

Re: prepare statement within DBI
by grinder (Bishop) on Aug 25, 2004 at 15:42 UTC
    I could combine the two sql statements into one statement

    Well, you could with a union:

    select 't1', count(*) from t1 union select 't2', count(*) from t2

    ... but you must add something to each select (here, the name of the table as a string) to what is returned in order to guarantee that you can determine which count(*) corresponds to which table. You never know which order they'll come back in. But now you have just about as much make-work code to pick the apart the result set and get what you want.

    My advice would be to encapsulate it in a sub and use cached prepares.

    my $t1_count = table_row_count( $dbh, 'table1' ); my $t2_count = table_row_count( $dbh, 'table2' ); sub table_row_count { my $db = shift; my $table = shift; my $sth = $db->prepare_cached( "select count(*) from $table" ); $sth->execute(); return $sth->fetchrow_array; }

    Error checking is left as an exercise to the reader.

    - another intruder with the mooring of the heat of the Perl

Re: prepare statement within DBI
by gmax (Abbot) on Aug 25, 2004 at 15:47 UTC

    Whatever your database driver accepts as a valid SQL statement is fine as far as the DBI is concerned. Thus, if Oracle accepts a query like "SELECT COUNT(*) from t1; SELECT COUNT(*) from t2;" so you can do it.

    If that is not the case, you can force that behavior with some SQL features:

    my $query = qq{ select "t1" as t, count(*) as c from table_one UNION ALL select "t2", count(*) from table_two }; # Gives # +----+---+ # | t | c | # +----+---+ # | t1 | 4 | # | t2 | 2 | # +----+---+ my $table_status = $dbh->selectall_arrayref($query, {Slice => {}} ); # $table_status = [ # { t => 't1', c => 4 }, # { t => 't2', c => 2 } # ];

    Or:

    my $query = qq{ SELECT ( select count(*) from table_one ) AS t1, ( select count(*) from table_two ) AS t2; }; # Gives # +----+----+ # | t1 | t2 | # +----+----+ # | 4 | 2 | # +----+----+ my @table_status = $dbh->selectrow_array($query); # @table_status = ( 4, 2 );

    I would personally recommend against this, since I prefer to have better control on my SQL statements. However, if you know what you're doing, it's up to you to decide.

     _  _ _  _  
    (_|| | |(_|><
     _|   
    

      Additionally, consider looking at the results of your database's query optimizer and analyzer to see what the trade-off will be in creating more complex SQL queries to offset the cost on the perl side in making multiple calls.

      Maybe a stored proc is in order?

      Thoughts,
      -v
      "Perl. There is no substitute."
Re: prepare statement within DBI
by CountZero (Bishop) on Aug 25, 2004 at 19:43 UTC
    There is only one way to find out: try both approaches and time x runs and see what is best.

    COUNT(*) should be highly optimized on the database server side, probably not even needing to run through the whole of the datatable, so it could indeed be that most of the time is spent in connecting to the DB and preparing the query.

    On a MySQL database with about 70,000 records, COUNT(*) took 1/10 of a second to come back with the result. The database was on the local machine, so transmission time was neglible.

    Querying a table with only 27 records, took 0.10 seconds as well.

    Probably 0.10 of second is the minimum time to run a query.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

      Note - MyISAM and Heap tables will return the count in constant time. InnoDB and BDB tables will not. It's a function of the data structure used under the covers.

      Oracle tables are worse than MyISAM, but better than InnoDB, in that the cost of count scales logarithmically, not linearly.

      ------
      We are the carpenters and bricklayers of the Information Age.

      Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

      I shouldn't have to say this, but any code, unless otherwise stated, is untested

        A select count(*) usually has to traverse at least an index tree to find the actual number of rows currently in the table. A quick test on Sybase on a fairly busy server shows that it can take up to 3 seconds to count the rows in a 4+ million row table, with a lot of the time spent fetching data pages into cache. Keep in mind that the database engine has to be able to run the select count(*) where somecondition as well, for which no real short-cut is available, unless an appropriate index exists that can satisfy the WHERE clause.

        Michael

Re: prepare statement within DBI
by Juerd (Abbot) on Aug 26, 2004 at 09:42 UTC

    $sth = $dbh->prepare("select count(*) from table_one"); $sth->execute(); $row1 = $sth->fetchrow_array; $sth = $dbh->prepare("select count(*) from table_two"); $sth->execute(); $row2 = $sth->fetchrow_array;

    Unrelated comment: with DBIx::Simple, you can save yourself some typing.

    $db->query('select count(*) from table_one')->into($row1); $db->query('select count(*) from table_two')->into($row2);
    or even, giving up a lot of speed:
    $db->select(table_one => '*')->into($row1); $db->select(table_two => '*')->into($row2);

    DBIx::Simple caches queries automatically, so there is no need to separate prepare and execute yourself.

    Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }

      You don't need DBIx::Simple to simplify that. Just do this with plain old DBI:
      $row1 = $dbh->selectrow_array('select count(*) from table_one'); $row2 = $dbh->selectrow_array('select count(*) from table_two');

        You don't need DBIx::Simple to simplify that. Just do this with plain old DBI

        Yes, but with one important difference: selectall_array prepares a new sth each time, while DBIx::Simple re-uses them (as if prepare_cached were used, but safer).

        Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://385694]
Approved by ybiC
Front-paged by Caron
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (13)
As of 2014-08-27 18:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (248 votes), past polls