bad query plan when using parameterised queries on partitioned vie

Recently I found rather a nasty consequence by using parameterised queries o
n
a partitioned view.
I have a .net app that sends parameterised queries to SQL Server 2000. SQL
Server obliges by caching the query plan to increase efficiency of subsequen
t
queries.
In my case the query is running against a partitioned view, partitioned by a
date constraint.
A query is run when the cache is cleared and the query optimiser correctly
creates a query plan specifying only the correct partition and ignoring the
other partitions, so far so good…
Subsequent queries run fine and use the cached query plan until we get a
query where the date range selected now relates to a different underlying
partition – blam, full table scan against all partitions! As you might gue
ss
the query execution times blow out by several orders of magnitude! It seems
it tries to use a bad query plan from the cache and when it realises it can
t
get the data it needs it is not smart enough to re-evaluate the query plan
properly.
Currently I am forcing a recompile on these guys go make sure the cached
plan is not used as it obviously cannot be trusted! This workaround does the
trick but I’m wondering if anyone has experienced something similar before
?
Or is there a better way to deal with this? It seems to me to be in fact a
bug as the optimiser should handle this a lot better than it does.
Thoughts anyone?
Cheers,
Chrissounds like parameter sniffing problem. Sure there are ways better than
WITH RECOMPILE. Search for "parameter sniffing" in this group or google.