Query with Sub-Select takes longer than a temp table

[TheDrider]

I have a query that needs to compare it's summed results against another set of summed results.

When I join to a sub-select in the main query it takes 3 seconds to run. I rewrote it to put the sub-select into a temp table first, then run the second query joined to it directly and it takes less than one second.

The exection plan is a bit greek to me still, so I couldn't determine the main difference between the two.

Here's the original, single statemnt:

		-- TAKES *THREE* SECONDS
		-- Document balances by OBAN.PEC.EEIC
		SELECT OBAN_TX, PEC_TX, EEIC_TX,
			IsNULL(SUM(ANNUAL_AM), 0) AS FDOC_ANNUAL_AM, IsNULL(TOTAL_ANNUAL_AM, 0) AS TOTAL_ANNUAL_AM
		FROM FC_FUNDING_DOCUMENT_DETAILS FDD
			JOIN FC_FUNDING_DOCUMENTS FD ON FDD.FDOC_ID = FD.FDOC_ID
			LEFT JOIN OBAN ON FDD.OBAN_ID = OBAN.OBAN_ID
			LEFT JOIN PEC ON FDD.PEC_ID = PEC.PEC_ID
			LEFT JOIN EEIC ON FDD.EEIC_ID = EEIC.EEIC_ID
		
			LEFT JOIN (
				-- Overall balances by OBAN.PEC.EEIC
				SELECT FC_ID, FY_DT, OBAN_ID, PEC_ID, EEIC_ID, SUM(ANNUAL_AM) AS TOTAL_ANNUAL_AM
				FROM FC_FUNDING_DOCUMENT_DETAILS FDD JOIN FC_FUNDING_DOCUMENTS FD ON FDD.FDOC_ID = FD.FDOC_ID
				WHERE STAT_CD = 6
				      AND FD.FC_ID = 1 AND FD.FY_DT = '1 jan 2006'
				GROUP BY FC_ID, FY_DT, OBAN_ID, PEC_ID, EEIC_ID
			) AS TOT ON FD.FC_ID = TOT.FC_ID AND FD.FY_DT = TOT.FY_DT
				AND IsNULL(FDD.OBAN_ID, 0) = IsNULL(TOT.OBAN_ID, 0)
				AND IsNULL(FDD.PEC_ID, 0) = IsNULL(TOT.PEC_ID, 0)
				AND IsNULL(FDD.EEIC_ID, 0) = IsNULL(TOT.EEIC_ID, 0)

		WHERE FD.FDOC_ID = 6958
		GROUP BY FD.FC_ID, FD.FY_DT, OBAN_TX, PEC_TX, EEIC_TX, TOTAL_ANNUAL_AM
		HAVING IsNULL(SUM(ANNUAL_AM), 0) + IsNULL(TOTAL_ANNUAL_AM,0) < 0
		      AND IsNULL(SUM(ANNUAL_AM), 0) < 0  --  Don't care if the transaction is positive

Here is the same query with the sub-select broken out. Why does it go so much faster? I'm sure I did something abnormal, I just can't see it.

		-- TAKES *ONE* SECOND
		-- Overall balances by OBAN.PEC.EEIC
		SELECT FC_ID, FY_DT, OBAN_ID, PEC_ID, EEIC_ID, SUM(ANNUAL_AM) AS TOTAL_ANNUAL_AM
		INTO #TMP_RECS
		FROM FC_FUNDING_DOCUMENT_DETAILS FDD JOIN FC_FUNDING_DOCUMENTS FD ON FDD.FDOC_ID = FD.FDOC_ID
		WHERE STAT_CD = 6
		      AND FD.FC_ID = 1 AND FD.FY_DT = '1 jan 2006'
		GROUP BY FC_ID, FY_DT, OBAN_ID, PEC_ID, EEIC_ID


		-- Document balances by OBAN.PEC.EEIC
		SELECT OBAN_TX, PEC_TX, EEIC_TX,
			IsNULL(SUM(ANNUAL_AM), 0) AS FDOC_ANNUAL_AM, IsNULL(TOTAL_ANNUAL_AM, 0) AS TOTAL_ANNUAL_AM
		FROM FC_FUNDING_DOCUMENT_DETAILS FDD
			JOIN FC_FUNDING_DOCUMENTS FD ON FDD.FDOC_ID = FD.FDOC_ID
			LEFT JOIN OBAN ON FDD.OBAN_ID = OBAN.OBAN_ID
			LEFT JOIN PEC ON FDD.PEC_ID = PEC.PEC_ID
			LEFT JOIN EEIC ON FDD.EEIC_ID = EEIC.EEIC_ID
		
			LEFT JOIN #TMP_RECS TOT ON FD.FC_ID = TOT.FC_ID AND FD.FY_DT = TOT.FY_DT
				AND IsNULL(FDD.OBAN_ID, 0) = IsNULL(TOT.OBAN_ID, 0)
				AND IsNULL(FDD.PEC_ID, 0) = IsNULL(TOT.PEC_ID, 0)
				AND IsNULL(FDD.EEIC_ID, 0) = IsNULL(TOT.EEIC_ID, 0)
		WHERE FD.FDOC_ID = 6958
		GROUP BY FD.FC_ID, FD.FY_DT, OBAN_TX, PEC_TX, EEIC_TX, TOTAL_ANNUAL_AM
		HAVING IsNULL(SUM(ANNUAL_AM), 0) + IsNULL(TOTAL_ANNUAL_AM,0) < 0
		      AND IsNULL(SUM(ANNUAL_AM), 0) < 0  --  Don't care if the transaction is positive


		DROP TABLE #TMP_RECS

Maybe there's a simpler way to accomplish the same goal. I have a funding document which is a container of transactions. Each transaction has an account # of OBAN.PEC.EEIC. I need to compare the account totals in the document to the overall account totals to check for funds availability.

So in essence, is document #xxxx trying to take too much funding from any account?

 

[Catadmin]

There could be several reasons why the second query is faster than the first. The first 2 I can think of are:

1) Check the execution plan of both queries. Which one has more table / index scans? Probably the first query. Scans are bad. Use Index Hints to change scans to seeks.

2) The Temp table is "stored" and manipulated in TempDB. If TempDB is on a different logical/physical drive, you might actually have a disk I/O issue with the first query trying to process all that data via one disk controller. Where as the second query is utilizing 2 disks to process your data.

Also, remember, the second query is actually 2 different queries. It's processing the Temp Table first, which is simple as it's only a SELECT statement with no sub-queries, then processing the rest of the data set joined to the Temp table.

Your first query has to process everything all at the same time. And while sub-queries are usually (if I recall correctly) the first to process in SQL, it's still not "done" with the data set when it goes to the rest of the query, so has to hold the sub-query's data in cached memory which isn't necessarily the same thing as caching data in the TempDB.

Make sense? Or have I just further confused you?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"