May 30, 2012 at 1:33 am
Hi experts,
I have 2 tables in my DB.
1. BookSales(135m rows)
2. AdminFees(13m rows)
Both having the same structure.
I want to load data from these tables to fact table.
These 2 tables again joined with some other tables to get aggregates.
I am doing this way.
DECLARE @TransId INT
;WITH Src As
(
Select TransId, col2, col3, SUM(Amount) from BookSales
inner join table2 on ......
where BookSales.TransId > @TransId
UNION ALL
Select TransId, col2, col3, SUM(Amount) from AdminSales
inner join table2 ON .....
where AdminSales.TransId > @TransId
)
MERGE dbo.FactTables src USING
(
select TransId, col2, col3, SUM(Amount) from src
)
.........
It is taking 90 minutes to load.
Is there any other way to increase performance.
Indexes are set properly. Execution plan also not suggesting any indexes.
Please reply
May 30, 2012 at 1:41 am
Very hard to say from the information given. An execution plan with runtime statistics (an 'actual' execution plan) would be very useful, as would definitions for the tables and indexes concerned, and the query text in full.
May 31, 2012 at 7:58 am
1) Nothing personal, but I ALWAYS question when someone says "indexes are set properly" or something similar.
2) How many rows match > @TranID?
3) Have you checked for massive hash or sort operators? They can kill your system by getting flushed out to tempdb.
4) What is the spec of the server?
5) Any triggers or foreign keys involved?
6) Have you tried batching up into smaller chunks to avoid item 3?
7) Are statistics out of whack and the estimated rows for the operation is a low number and actual row count is a telephone number?? That would lead to index seek type plans for kajillion rows and you can pack a lunch while that query spins.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply