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

[mpeppler]

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