I was trying to find some well performing Tsql equivalents for the SSIS Lookup component. Especially for Fact table loading.
After few different query patterns the most obv performed the best:
INSERT INTO Fact WITH (TABLOCK) SELECT F.Attribute1, ... d1.DimAId, d2.DimBId, d3.DimCId, ... F.Amount ... from Staging.dbo.GeneralLedger F WITH(NOLOCK) LEFT OUTER JOIN DimA1 d1 WITH(NOLOCK) on d1.AId = F.AId LEFT OUTER JOIN DimB d2 WITH(NOLOCK) on d2.BId = F.BId LEFT OUTER JOIN DimC d3 WITH(NOLOCK) on d3.CId = F.CId
Performance wise I tested this with 5 million rows and lookups to 9 dimensions.
SSIS: 1m14s TSQL: 1m0s
Which will be the implications if I choose TSQL over SSIS on larger datasets (+100M rows) Currently I tested this on a 200GB RAM server so no problems fitting everything in memory.
However I suppose if this query would be used on a machine with less ram it will starts spilling to disk and hurt performance badly. SSIS would be memory wise more efficient then. Am I correct on this assumption?
Another idea I come to mind is using a cursor loop to only do this with tsql for exemple for 1M rows at the time till all data is loaded. By doing this you will have less chance to get an out of memory and start wasting to disk. Is this a viable approach or see you any other approaches?
PS: I am fully aware that SSIS is the way to go for fact table loading. However let us assume we only want TSQL. PS2: Question also posted on stackoverflow https://stackoverflow.com/questions/26638590/tsql-vs-ssis-lookup. However no real groundbreaking response.