Posted by wizofaus on 01/18/07 04:59

JXStern wrote:
> On 15 Jan 2007 13:36:21 -0800, wizofaus@hotmail.com wrote:
>
> >> How large is your table?
> >
> >'bout 2 million records, 9 columns.
> >>
> >> Maybe a simple "update statistics" would also fix things?
> >>
> >Quite possibly - unfortunately I can't re-create the problem now to
> >test it!
> >Will be the first thing I try if I see the same problem again, though.
>
> Could probably reproduce it - insert a 1,000,000 rows = 1, then a
> handfull of rows numbered 2-10. Query it for =1, it will scan, then
> query it for =2. Or something like that.
>
Not sure, but now I have another issue which does seem to point towards
parameters being a problem.

If I run a query with quite a large number of parameters which
basically

SELECT Count(*) FROM MyTable WHERE Key1 IN (@0, @1, @2...@12) AND Key2
IN (@13, @14, @15...@20)

it takes over 2 seconds, but when I substitute the last 8 parameters
(13-20) with their values, i.e.

SELECT Count(*) FROM MyTable WHERE Key1 IN (@0, @1, @2...@12) AND Key2
IN ('value1', 'value2', 'value3', 'value4'...'value8')

the query takes 15 milliseconds! (actually it took slightly longer -
about 400msec - on the first run - the previous query always takes ~2
seconds no matter how often I run it).

Curiously, the fewer parameters I substitute for the Key2 filter, the
slower it runs (I can't improve on the 15 msec by doing the same for
they Key1 filter), in a more or less linear relationship.

I've tried all the previously mentioned "tricks", including reindexing
the table, but no luck.

So it seems maybe SQL server has problems optimizing if there are too
many parameters.
FWIW, I did try putting the parameter values in a temporary table and
even using a join, but the total time is significantly longer than
15ms.

Again, the parameter values are under my control, so there's no risk of
SQL injection, so if there's another good reason NOT to do my own
parameter substitution in this case, I'd be interested in hearing it.

[Back to original message]