Posted by wizofaus on 01/13/07 23:24

Erland Sommarskog wrote:
> (wizofaus@hotmail.com) writes:
> > Thanks...one of the most helpful replies I've had on usenet for some
> > time now!
> >
> > The problem is that it's pretty hard for me to know that a value is
> > "odd".
>
> I can understand that this is not always simple. I didn't say this, in
> hope it would be. :-)
>
> However, I think I have a cure for you:
>
> > In this case, like I said the query in this case is
> >
> > SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0
> >
> > Where @0 is actually a value from a list that is obtained from
> > elsewhere (not the database). It loops through this list, calling the
> > same query for each one.
>
> Stop! Don't do that! The problems with query plans aside, this is an
> ineffecient use of SQL Server. Get all data at once with:
>
> SELECT t.MyKey, Min(t.MyValue)
> FROM MyTable t
> JOIN list_to_table(@list) f ON t.MyKey = f.value
> GROUP BY t.MyKey
>
> Where list_to_table is a table-valued function that transform the list
> to a table. I have a whole bunch of such functions on
> http://www.sommarskog.se/arrays-in-sql.html.
>
I agree this sort of thing would be preferable, and more efficient, but
as it is, providing I don't get the bad plan problem, it's efficient
enough as it is, and I'm wary of making too many big changes at this
point. That sort of thing I'd prefer to leave for the next version.

The thing is, I just tried exporting all the records to a CSV file,
creating a clean database with the same tables/indices, re-importing
all the records, and the same problem DOESN'T happen: I can run that
query with the "odd" value first, then with the common value, and both
queries run fast. So something else has happened to my database that
surely I must be able to reset somehow?

[Back to original message]