To stage or not to stage

GokhanVarol 2013-07-14 01:02:41

"StagingInsert_Actual.queryanalysis" took 2331 seconds to complete.
alt text

link text

I am very happy with this so far. My idea was to first create a staging table and then to create dimension table I will add lookups and insert into a predefined fact table.
"FromStagingToFactTable_AllHash_Estimated.sqlplan" is me trying to lookup all dimensions. I stopped running this query when it already took too long.
alt text

Then I said let me get rid of the physical lookups and use case statements for lookups which have less than 50 cases and anything more than 50 I would use an inline values clause to lookup "FromStagingToFactTable_ValueClauseMixedWithCasesForLessThan50Lookups_Estimated.sqlplan", which also took too long.
alt text

Then I changed everything to cases "FromStagingToFactTable_AllConvertedToCases_Actual.queryanalysis" and this time I gave up and let it run, it took 6832 seconds to complete.
alt text

The last two cases were serial plans.

Working from staging table is taking longer than pulling data from the database, no good. I am thinking of putting all the code into the first query that pulls the data from the main database where everything runs under maxdop 8 by default or I can increase it to maxdop 16 or so to speed up (memory problems elevate higher on maxdop 16 but may worth to try).
In that case what how should I lookup, should I use case statement with up to 50 or 100 or 1000 cases or combination of case statements and inline values clauses for lookups or should I use physical tables for lookups to get this done as quick as possible.
Any suggestions?
Thank you