April 26, 2008 at 11:02 pm
Hi All,
I came across a typical issue in my data warehousing solution. We are providing a backup job for our ETL, this job deletes all the data from Mart databases and all the staging data bases and pulls all the data from source system.
Normally my ETL will take around 40 mins, but after deleting all the data my ETL is taking longer in 3 steps. Verified those procedure, if we force the queries to use hash join, it is working fast. But the same queries are working before, so we don’t want to change the queries. Updated the statistics after deleting the data, but of no use. Please suggest me any other options I need to look
Thanks for your help.
Regards,
Ramesh K
April 27, 2008 at 8:23 am
perhaps the query plans for your procs are no longer optimal.
what happens if you recompile all the procs?
---------------------------------------
elsasoft.org
April 28, 2008 at 6:52 am
The queries work well when the data is loaded, but don't work when the tables are empty? I think the previous post may be right, the execution plans are still in cache for the old data sets. You're updating statistics too, but maybe your indexes need to get rebuilt instead. You might try doing that instead of updating statistics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2008 at 7:02 am
Can you please post the queries that are taking longer, as well as the delete statement.
Are you running SQL 2005? If so, please save the execution plan as a .sqlplan file, zip and attach it. If possible, one with thje data deleted and one without the data deleted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2008 at 11:59 am
If you are truncating the tables before loading them, I would also drop the indexes (except for the clustered index if the data is loaded in clustered index order). Then rebuild them. The load will probably go faster also.
Todd Fifield
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply