Index a temp table
Posted on 2011-05-13
I need to, I think, create an index on the temp table that I have attached as code to this question. The script was created to being back reults where the date is a parameter, the thing I need to index is the serviceType then countryCode, cityCode, storeType and storeCode.
I have a crystal report that runs this function and the reports are defined by the service type then split between country, city, storetypes and stores. The function only had a few results prior to a few days ago, now it has around half a million records to process so i'm thinking that if I index the temp table created here it would speed it up a lot, it takes around an hour and 10 minutes to complete. This would be too bad but the user of the report that uses this data has the flexibility to select different dates to get the results, up to 7 days ahead so. If they didn't and it only returned results for the next day I'd just run the function during the night and we may not have an issue.
So I think putting an index on the columns above may help here?
Any help would be much appreciated.