Gary,
Is "componentIDList" something that comes from the database via a
query, or is this something you build in the app? If this is coming
from DB, you can probably use an extra join instead of IN. But I guess
you would've thought about it already...
With Oracle you could've probably optimized this query (as I don't know
the sizes of all the tables, I am just guessing) by fetching all
"indexable_components_all_active" by id right in the FROM clause, and
only then join with the rest of the tables to reduce the size of the
join product... AFAIK, SQLServer does not support that.
Andrus
On Jun 16, 2004, at 7:42 PM, Gary Jarrel wrote:
> ...AND indexable_components_all_active.component_id IN
> $componentIDList")
>
> 2. The IN clause can get pretty slow when there are 500 or more
> components. Not a big fan of the IN expression myself simply for this
> reason. But given that the number of component IDs is never certain I
> can’t think of a better way to do this. Does anyone have any ideas?
>
> I’ll be spending time profiling this particular part of the code, so
> hopefully at a later date I may have some more info in how fast this
> executes. For now I can say for observing the logs is that it takes
> 851ms to execute a query that returns 81 components.
This archive was generated by hypermail 2.0.0 : Thu Jun 17 2004 - 21:56:09 EDT