Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: Where the advice to use DBI bind parameters can go wrong (long)

by mpeppler (Vicar)
on Jun 14, 2002 at 22:58 UTC ( #174741=note: print w/replies, xml ) Need Help??


in reply to Where the advice to use DBI bind parameters can go wrong (long)

Interesting observation.

For Sybase the optimizer determines index usage when the query is run the first time. So if the first request only has a trailing '%' and the column has an index it will be used. A subsequent query with a leading '%' won't be incorrect - the optimizer detects the leading '%' and while it uses the index it will look at all the values for a match.

However, if the first query has a leading '%' then you will get a table scan each time.

I ran the following:

my $sth = $dbh->prepare("select * from market where commodity like ?") +; $sth->execute("SPM%"); while(my $d = $sth->fetch) { ; } $sth->execute("%XXX%"); while(my $d = $sth->fetch) { ; }
The showplan output shows that the index is used, but the stats IO shows that the second execute() call does a lot more work:
Table: market scan count 1, logical reads: (regular=111 apf=0 total=11 +1), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
vs.
Table: market scan count 1, logical reads: (regular=51889 apf=0 total= +51889), physical reads: (regular=0 apf=0 total=0), apf IOs used=0

FWIW :-)

Michael

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://174741]
help
Chatterbox?
[moritz]: choroba in python I use jsonschema
[moritz]: I'm sure there's a Perl implementation of it too
[choroba]: it seems Kelp and similar help you a lot with dispatching, but how to do something similar with contents?
[moritz]: like JSON::Schema
[Corion]: ambrus++ # more bugs than lines
[Corion]: That's like "works on my machine" ;-)
[choroba]: I mean, I'd like to say "this path should run this sub, but first it should check the user is authenticated, has the following permissions, and the contents is valid against a given schema
[ambrus]: no, that means I haven't even tried to run it, might not even parse
[ambrus]: so it doesn't work on my machine
[choroba]: i.e. I know JSON::Schema, I'm just curious whether there's a ready solution to plug it into a web service

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (9)
As of 2016-12-08 12:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:













    Results (141 votes). Check out past polls.