lookup performance

[vikas.peshwani]

hi all,

we r using sql server 2005, and we are facing many issues with the lookup performance..wherever we r using more than one lookup the performance drastically decreases and even if it caches all the rows, it does not proceeds..
it takes almost half hour for a single lookup for approx 7-10 million records.

We have tried almost all the options, i.e. enabling the memory cache and increasing it to even 500mb.

A script component seems to work a little fast if we use it instead of lookup. but still its too slow.
If anyone has ne workaround for this issue, pls reply.

thanks,
vikas peshwani

[hspc]

What do you mean by lookup? a query ?
What is the search criteria ? What indexes did you build?
What is the number of records returned by the query ?
are the 7-10 million records the resultset returned from the query (which implies a bad or strange application design) or the number of records in the table ?