Why is the execution plan for these two queries so different?

select * from foo where foosearch = :searchfor
(set the param to 'bar%')

select * from foo where foosearch = 'bar%'

One results in a partial search against a key and returns instantly, the other results in
a scan of the entire index which in our case will have hundreds of thousands of rows.

Jeff


I just tried the equivalent on one of my tables and get:

select * from contacts where _surname = 'smith%'
"_surname" = 'smith%' [Index scan (Contacts.ContactName):, 0 keys, 4096 bytes
estimated cost]

select * from contacts where _surname = :name
"_surname" = 'smith%' [Index scan (Contacts.ContactName):, 0 keys, 4096 bytes
estimated cost]

ie both the same

Then I tried what I think you meant as the sql using LIKE and I do get a difference

select * from contacts where _surname like 'smith%'
"_surname" = 'smith' [Partial length match on 5 char(s)] [Index scan
(Contacts.ContactName):, 75 keys, 12288 bytes estimated cost]


select * from contacts where _surname like :name
"_surname" LIKE 'smith%' [Index scan (Contacts.ContactName):, 7912 keys, 954368
bytes estimated cost]

This is in b11 non unicode

I'd guess it that the parser can figure out "in advance" that you're doing a partial match on an index in the first case but the substitution of the variable into the parameter is to late for that to be determined but I'm sure Tim will give the real answer.

Roy Lambert [Team Elevate]

Jeff,

<< Why is the execution plan for these two queries so different?

select * from foo where foosearch = :searchfor
(set the param to 'bar%')

select * from foo where foosearch = 'bar%'

One results in a partial search against a key and returns instantly, the
other results in a scan of the entire index which in our case will have
hundreds of thousands of rows. >>

Are you sure that it isn't LIKE that you're using ?  If it is LIKE, then the
issue has to do with the way that the parser/compiler has to structurally
convert the expression when dealing with it as a partial-length search as
opposed to a full-blown LIKE comparison.  I'll see if I can improve on this,
but for now you're going to have to build the SQL dynamically if you want to
change the LIKE comparison value.

--
Tim Young
Elevate Software
www.elevatesoft.com

Indeed it was the LIKE expression.

For our uses there would be definite high value in being able to trust queries with
parameters to be as optimal as otherwise. On the topic of performance, you also know that
we've been waiting patiently for optimization on multi-column locates. This is becoming
urgently important.

Jeff,

<< For our uses there would be definite high value in being able to trust
queries with parameters to be as optimal as otherwise. On the topic of
performance, you also know that we've been waiting patiently for
optimization on multi-column locates. This is becoming urgently important.
>>

Yes, I've been keeping Graeme up-to-date on this.

--
Tim Young
Elevate Software
www.elevatesoft.com