To stage or not to stage
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.
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.
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.
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.