Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re^5: DBI, place holders and CGI forms

by mje (Deacon)
on Jun 03, 2011 at 09:20 UTC ( #907937=note: print w/ replies, xml ) Need Help??


in reply to Re^4: DBI, place holders and CGI forms
in thread DBI, place holders and CGI forms

I've seen various ways in sqlplus to time queries. Here is a simple one:

set autotrace on; set timing on; select count(*) from table; COUNT(*) ---------- 164078859 Elapsed: 00:01:02.02 Execution Plan ---------------------------------------------------------- Plan hash value: 2319838783 ---------------------------------------------------------------------- +------- | Id | Operation | Name | Rows | Cost (%CPU)| Time + | ---------------------------------------------------------------------- +------- | 0 | SELECT STATEMENT | | 1 | 87965 (2)| 00:17: +36 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| PRICE_IDX18 | 152M| 87965 (2)| 00 +:17:36 | ---------------------------------------------------------------------- +------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 321153 consistent gets 321106 physical reads 0 redo size 422 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

Using Perl DBI you can quickly do something like:

use DBI; use strict; use warnings; use Benchmark; my $h = DBI->connect('dbi:Oracle:host=xxx;sid=yyy','xxx','xxx'); my $s = $h->prepare(q/select * from action where action_id = ?/); timethese(10000, { 'placeholders' => sub {placeholders($s)}, 'quote' => sub {quote($h)}}); sub placeholders { $_[0]->execute(1); } sub quote { $s = $h->prepare("select * from action where action_id = " . $_[0] +->quote(1)); $s->execute; }
$ perl bm.pl Benchmark: timing 10000 iterations of placeholders, quote... placeholders: 3 wallclock secs ( 0.43 usr + 0.20 sys = 0.63 CPU) @ +15873.02/s (n=10000) quote: 16 wallclock secs ( 5.51 usr + 1.32 sys = 6.83 CPU) @ 14 +64.13/s (n=10000)


Comment on Re^5: DBI, place holders and CGI forms
Select or Download Code
Re^6: DBI, place holders and CGI forms
by Anonymous Monk on Jun 17, 2011 at 14:33 UTC
    So the advice works great for selects, I'm now thinking about how can I do this with insert statements, the same idea, I don't know what columns have been submitted. Will the same idea work here?

      Which idea are you referring to?

        Your select advice -
        select col1, col2, col3 from tab_name where (? is null or foo = ?) a +nd (? is null or bar = ?) and (? is null or baz = ?)
        .

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://907937]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (9)
As of 2014-07-31 04:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (244 votes), past polls