March 4, 2010 at 9:13 am
Hi All,
We have nightly process that builds indexes. During the day, new data gets added to the table. When we run the query on records which were been added today, it takes a long time, but queries which return previous day or a month old data runs perfectly fine.
We speculate 'cause, every night the indexes are being built, and hence the data being added today are not indexed properly.
Does anyone have suggestions on how we can improve the performance of the query for data that are being added today.
Regards
March 4, 2010 at 9:25 am
It's possible that the issue is statistics related, rather than index.
Particularly if some of the tables are large and busy, hte statistics for that day may be returning an estimated vallue of zero rows, leading to a sub-optimal execution pland (specifically nested loop joins where another join type would be more appropriate, for example a hash join.
Look at the query plans for extimated and actual rows and join type. If this is your issue it may be worthwhile setting Updte Stats asynchronously to on and rebuilding stats regularly during the dy
March 4, 2010 at 2:44 pm
Andrew's answer is excellent. Two ther potential ways of dealing with this other than updating the statistics is:
1. Use hash join hint - not a good idea for the procedure ongoing, but a good thing to check for trouble shooting to see if indeed the hash join would help.
2. optimize the parameter for a specific value picking a value that would be representative. This case can be good if most of the new values fall into a given range, so for instance
OPTION ( OPTIMIZE FOR ( @City = 'Newark' ) )
At the end of the statement.
Regards,
Toby
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply