asked
SQL Sub Queries Executing More Than Once?
Hi there, Is there anyway possible to speed up the execution of the following SQL: SELECT CLIENT, (SELECT TOP 1 PERSON FROM TABLE2 WHERE TABLE2.CLIENT = TABLE1.CLIENT) AS PERSON, (SELECT COUNT(*) FROM TABLE2 WHERE TABLE2.CLIENT = TABLE1.CLIENT) AS INFO FROM TABLE1 WHERE CLIENT IN (SELECT DISTINCT CLIENT FROM TABLE2) Table2 is quite large and appears to have 3 sub queries in the above statement. Is it possible to execute it once to pass the results back? TIA --- EDIT TO ADD ------ Thaks for all the replies so far however I probably should have posted more of the query as it's a bit more involved in the subqueries. This is more of the SQL code: SELECT T1.CLIENTNO, (SELECT TOP 1 PERSON FROM TABLE2 WHERE TABLE2.CLIENTNO = T1.CLIENTNO AND PERSON IS NOT NULL AND PERSON > '' ORDER BY TYPE ASC) AS PERSON, (SELECT COUNT(*) FROM TABLE2 WHERE TABLE2.CLIENTNO = T1.CLIENTNO AND PUBLICATION like 'E7%') AS INFO FROM TABLE1 T1 WHERE (T1.CLIENTNO IN (SELECT DISTINCT CLIENTNO FROM TABLE2)) I tried and experimented with the temporary table however due to the subqueries the large table isn't still being queried 3 times to insert into the temp table? I'm trying to execute the query of TABLE2 just once. Do you think this is possible? Thanks again everyone. ---- EDIT TO ADD part 2 ---- Remembered someone else suggested before that I could use CASE Here's my working solution: SELECT DISTINCT T.CLIENT, STAFF2, INFO FROM TABLE1 T LEFT JOIN ( SELECT CLIENT, MAX(CASE WHEN STAFF IS NOT NULL AND STAFF > '' THEN STAFF ELSE NULL END) AS STAFF2, COUNT(CASE WHEN INFO like '%ADULT%' THEN 1 ELSE NULL END) AS INFO FROM TABLE2 GROUP BY CLIENT) I ON I.CLIENT = C.CLIENT LEFT JOIN TABLE2 I2 ON I2.CLIENT = I.CLIENT WHERE STAFF2 IS NOT NULL AND INFO IS NOT NULL Thanks everyone for all advices.
With thist query you cant guarantee which person that will be fetched from table2 if you have more than one person per record in table1. To guarantee the person you need an order by. In SQL 2000 I would create a temp table and populate it with the persons from table2, aggregated on the column you defined in the where clause.
Select min(person) from table2 group by columnX
@Håkan +1 for the idea. I think we can also add COUNT(*) as well while populating the temp table. So the new query would be something like this INSERT #temp ( CLIENT ,PERSON ,INFO ) SELECT CLIENT , MIN(PERSON) PERSON , COUNT(*) INFO FROM table2 GROUP BY CLIENT SELECT T.CLIENT , T.PERSON ,
T.INFO FROM TABLE1 INNER JOIN #temp T ON TABLE1.CLIENT = T.CLIENT Moreover, I would also create a PRIMARY KEY on field CLIENT of the temp table. Hope this makes sense. Cheers.
That should work for you, I appology for my short answer, it was written on an iPhone durin SQL rally Nordic.
Nice to hear you were there. Hopefully, one day I would be able to join PASS events as well :(
I have managed to squeeze it down to two calls but getting difficult to squeeze it into one call as the solution should be for 2000. Moreover, you can speed up your query with proper indexes. IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE [#temp] CREATE TABLE #temp ( CLIENTNO INT PRIMARY KEY ,INFO INT ) INSERT [#temp] ( [CLIENTNO] ,[INFO] ) SELECT [CLIENTNO] ,COUNT(CASE WHEN [PUBLICATION] LIKE 'E7%' THEN 1 ELSE NULL END) INFO FROM TABLE2 AS T GROUP BY [CLIENTNO] SELECT T.[CLIENTNO] , ( SELECT TOP 1 PERSON FROM TABLE2 WHERE TABLE2.CLIENTNO = T1.CLIENTNO AND PERSON IS NOT NULL AND PERSON > '' ORDER BY [TYPE] ASC ) AS PERSON ,
T.INFO FROM TABLE1 T1 INNER JOIN #temp T ON T1.CLIENTNO = T.CLIENTNO
As you can see, I'm not entirely sure what you're actually doing with this report but at least our code seems to give the same result. CREATE TABLE #Table1 (ClientNo INT) CREATE TABLE #Table2 (ClientNo INT, Person VARCHAR(50) NULL, Publication VARCHAR(50), TYPE int) INSERT INTO #Table1 SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 INSERT INTO #Table2 SELECT 1, 'shakespeare','E7Love''s labour lost',2 UNION SELECT 3, 'Tennyson','E7Tennyson''s collected works',4 UNION SELECT 3, 'Byron','Childe Harold',2 UNION SELECT 4, 'Stevenson','Treasure Island',5 --original solution SELECT T1.CLIENTNO, (SELECT TOP 1 PERSON FROM #TABLE2 WHERE #TABLE2.CLIENTNO = T1.CLIENTNO AND PERSON IS NOT NULL AND PERSON > '' ORDER BY TYPE ASC) AS PERSON, (SELECT COUNT(*) FROM #TABLE2 WHERE #TABLE2.CLIENTNO = T1.CLIENTNO AND PUBLICATION LIKE 'E7%') AS INFO FROM #TABLE1 T1 WHERE (T1.CLIENTNO IN (SELECT DISTINCT CLIENTNO FROM #TABLE2)) --quicker solution SELECT #table2.ClientNo, Person, Info FROM #table2 INNER JOIN (SELECT #Table1.ClientNo, min(type) AS lowestType, SUM(CASE WHEN PUBLICATION LIKE 'E7%' THEN 1 ELSE 0 END ) AS info FROM #Table1 INNER JOIN #table2 ON #TABLE2.CLIENTNO = #Table1.CLIENTNO GROUP BY #Table1.ClientNo) F ON f.clientno=#table2.ClientNo AND #table2.TYPE=lowesttype DROP TABLE #Table1 DROP TABLE #Table2
@Phil very glad to hear from you. Just wandering if there are multiple records for same TYPE against one Client, then it would return multiple Persons. But the requirement was to fetch only one Person. Check for these values INSERT INTO #Table1 SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 INSERT INTO #Table2 SELECT 1, 'shakespeare','E7Love''s labour lost',2 UNION SELECT 1, 'Tennyson','E7Tennyson''s collected works',2 UNION SELECT 3, 'Byron','Childe Harold',2 UNION SELECT 4, 'Stevenson','Treasure Island',5
