I've noticed this several times and still don't fully understand it.In cases where I have a complex derived table joined to another table it's often faster to load the derived table into a temp table then join to the temp table, instead of using a subquery. Why is this?For example:SELECT A, B, C, X, Y, RFROM TableA ta LEFT JOIN ( SELECT X, Y, ROW_NUMBER() OVER (PARTITION BY X, Y ORDER BY Q, SUM(Z)) as R FROM TableB WHERE D = 4 GROUP BY X, Y ) tb ON ta.X = tb.X AND ta.Y = tb.YWHERE A = 10
Such code may take a minute to run or more, but if I do the following:SELECT X, Y, ROW_NUMBER() OVER (PARTITION BY X, Y ORDER BY Q, SUM(Z)) as R INTO #tempFROM TableB WHERE D = 4 GROUP BY X, Y;ALTER TABLE #temp ADD CONSTRAINT PK_temp(X,Y);SELECT A, B, C, X, Y, RFROM TableA ta LEFT JOIN #temp tb ON ta.X = tb.X AND ta.Y = tb.YWHERE A = 10
It runs in 0 seconds.Why?--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware
Query much faster using temp vs. derived table
Posted - 2012-08-24 : 17:34:55
