Solved

slow running query when update stats hasn't run

Posted on 2011-02-11
11
270 Views
Last Modified: 2012-05-11
     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?
--------------------------------------------------------------------------------
0
Comment
Question by:elucero
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 13

Expert Comment

by:dwkor
ID: 34874981
Compare the plans in both cases and try to tune query with the hints and/or plan guide.
0
 
LVL 15

Expert Comment

by:MohammedU
ID: 34876204
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...
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 34878423
Elucero,
      Did you try for auto update option as Mohammed suggested? Try it out. This must help solve your problem.
0
 

Author Comment

by:elucero
ID: 34880194
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?  
0
 

Author Comment

by:elucero
ID: 34880202
Isn't there a way I can run a partial updates stats?  Can I run it on just one tbl?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34880284
>>Can I run it on just one tbl?<<
Of course. You can do it this way:
UPDATE STATISTICS YourTableNameGoesHere
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 34894310
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.
0
 

Author Comment

by:elucero
ID: 34897387
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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34903322
It sounds like the query is not getting the appropriate plan.  But without knowing any more details
I can only guess.
0
 

Author Comment

by:elucero
ID: 35002967
what do you mean by appropriate plan?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35038804
>>what do you mean by appropriate plan? <<
The SQL plan that the QO uses in order to optimize the query.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
default constraint within a function 3 37
Rename SQL Instance/SQL Developer Edition 2012 2 10
Extract string portion 2 13
Sql Query 4 16
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now