elucero
asked on
slow running query when update stats hasn't run
Hi, I have a stor proc that takes normally 2 mins to run, it's loading data into a Type 2 dimension so its doing compares. The data is about half a million rows a day. It seems to take 2 mins to run after I have run update stats. When I haven't ran update stats for the day, it takes 3 hours to run. This stor proc is part of an etl process, so I don't want to put the update stats in the middle of the package, but I do need it to take its normal time to run (2 min) . Any suggestions how I can get this to work effciently?
--------------------------
--------------------------
dwkor
Compare the plans in both cases and try to tune query with the hints and/or plan guide.
Is Auto Update Stats is on/enabled? If not enable it and see if it improves.
You can run the updatestats as different step before etl runs...
You can run the updatestats as different step before etl runs...
Elucero,
Did you try for auto update option as Mohammed suggested? Try it out. This must help solve your problem.
Did you try for auto update option as Mohammed suggested? Try it out. This must help solve your problem.
ASKER
Its already turned on. I can see its really slow when I'm doing an insert into the tbl that has a lot of data, we load about half a million rows a day. This tbl also has an index on it which is why I'm having this issue. I'm going to drop the index on the tbl and load it. Then I'm going to make a working tbl with the index on it and load the daily data into that so I can push it to the mart. Which would be faster for the working tbl, loading the data with the index already there or dropping and recreating the index when loading?
ASKER
Isn't there a way I can run a partial updates stats? Can I run it on just one tbl?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And about index thing, if there are lots of inserts happening daily, you can setup a reorganize\rebuild index job to be run daily during off hours.
ASKER
I got it to work, I took the index off the main tbl the data is inserted into daily (so of course it loads fast again). I then made a working tbl with the index and loaded just the day data into it to load the dim. Before I load the working tbl I truncate it and run an update stats against it. I got the process down to 30 secs. What is more efficient, running an update stats against the tbl or rebuilding the index? And why do I need to do either or? Why does this happen?
It sounds like the query is not getting the appropriate plan. But without knowing any more details
I can only guess.
I can only guess.
ASKER
what do you mean by appropriate plan?
>>what do you mean by appropriate plan? <<
The SQL plan that the QO uses in order to optimize the query.
The SQL plan that the QO uses in order to optimize the query.