Large Database, Slow Query, need help

[Kurt Schell]

I'm currently developing a web application with a large database backend. Think multiple 1million+ row tables.

I think I've done all that I know how to do to get the queries and tables optimized (indexes, splitting up larger tables into smaller logical tables), and most of the queries complete very quickly (< 1 second). But, there is 1 query that is taking up to 10 seconds to run the first time it runs, and then each additional time in runs for the same "category" it runs lightning fast. However, if you come back to the category after much time has gone by, it will again require a 10 second first run before additional runs will be fast again. I'm assuming it's because of the query caching, which is fine. I just want to cut the runtime down for the first run to below 1 second so that it will be fast whether it's cached or not.

If someone believes they can assist with this problem, and do so in a professional manner, please let me know, I'd be willing to pay for a solution.

Thanks,

Kurt