Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re^5: DBI, place holders and CGI forms

by mje (Curate)
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 browsing the Monastery: (16)
As of 2015-07-02 16:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (44 votes), past polls