On 23 Jun 2010, at 04:58, "Maddin, Peter" <[email protected]> wrote:
> I actually made the requested changed to my SQL command but there was no > discernable difference. > > I really wasn’t expecting any. > If you think it's related to the amount if data, some basics to try; 1. Take a copy of prod & load into development. Do this by detach & copy the data & log files and also by taking a dump (of the DB). 2. See if there's any difference. 3. Delete some data, check performance. 4. Rebuild indexes, check performance. 5. Look at the query plan being used. Rather than index hints, try specifying the join type. 6. Check the index type. Your query implies you have a small range of data in the joining tables - a bitmap index might be useful, if not already used. 7. Fragmentation? Infrastructure being overloaded (is the data on a SAN)? On your dev box, check SQL profiler (she's your friend here) for high CPU or IO. High CPU might mean the optimizer is getting bogged down (not likely here) or high IO implies lots of reads - the query execution plan should show this with high cost. Compare the query plan and profiler results with production too!! I recently helped a client where prod was slow (15mins for a query plan) but was almost instant in test. Long story short, the prod server had 'too much' (GBs) RAM and the optimizer got 'lost' - lowering the ram (to MBs) got the query down to 30 secs but batch processing died. This was an exceptional case where the 3rd party system was joining over 20 tables. It's a relatively simple query, it *should* be quick. > > > Regards Peter Maddin > > Regards, -- Richard Carde
