question

David 2 1 avatar image
David 2 1 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.
t-sqlsql-server-2000sub-query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
@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.
1 Like 1 ·
Håkan Winther avatar image Håkan Winther commented ·
That should work for you, I appology for my short answer, it was written on an iPhone durin SQL rally Nordic.
1 Like 1 ·
Usman Butt avatar image Usman Butt commented ·
Nice to hear you were there. Hopefully, one day I would be able to join PASS events as well :(
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
@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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.