|
|
Posted by Gert-Jan Strik on 01/18/07 19:35
wizofaus@hotmail.com wrote:
>
> 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).
Indeed, that is because with literals will really compile the statement
based on the actual values. The optimizer will build a kind of binary
tree. It will also remove any duplicates (when applicable). So the
execution phase will be very fast.
On the other hand, the compilation phase is relatively expensive. And if
you have hundreds of values, the optimizer start to choke.
> 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.
Explanation: see above
> 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.
It is hard to optimizer a scenario like yours. You might try something
like this:
SELECT SUM(cnt)
FROM (
SELECT COUNT(*) AS cnt FROM MyTable WHERE Key1=@0
UNION ALL
SELECT COUNT(*) AS cnt FROM MyTable WHERE Key1=@1
UNION ALL
...
) AS T
Although the optimizer might automatically come up with a query plan
that reflects this strategy, I doubt that it actually will.
Please let me know if it actually increases your query performance.
HTH,
Gert-Jan
Navigation:
[Reply to this message]
|