CODEWITHSUNDEEP
sqlsql-server
j.l.pg
j.l.pg

Reputation: 1

Is there a way to increase performance of a query which uses multiple subquerys in select clause

I have a query which selects data from two tables, TABLE_1 and TABLE_2. The two tables are connected with id. Every row in TABLE_1 can have exactly 4 rows on TABLE_2. The customer requires a View which returns all data from TABLE_1 and all data from TABLE_2 but the data from TABLE_2 must be shown like columns in the same row with. For example:

Here is a query i have used:

Select ID, NAME, LASTNAME,
(SELECT HomeType from #TABLE_2 where id = t1.ID order by ID OFFSET 0 ROW FETCH NEXT 1 ROW ONLY) as Row1Column1,
(SELECT HomeCost from #TABLE_2 where id = t1.ID order by ID OFFSET 0 ROW FETCH NEXT 1 ROW ONLY) as Row1Column2,
(SELECT HomeType from #TABLE_2 where id = t1.ID order by ID OFFSET 1 ROW FETCH NEXT 1 ROW ONLY) as Row2Column1,
(SELECT HomeCost from #TABLE_2 where id = t1.ID order by ID OFFSET 1 ROW FETCH NEXT 1 ROW ONLY) as Row2Column2,
(SELECT HomeType from #TABLE_2 where id = t1.ID order by ID OFFSET 2 ROW FETCH NEXT 1 ROW ONLY) as Row2Column1,
(SELECT HomeCost from #TABLE_2 where id = t1.ID order by ID OFFSET 2 ROW FETCH NEXT 1 ROW ONLY) as Row2Column2,
(SELECT HomeType from #TABLE_2 where id = t1.ID order by ID OFFSET 3 ROW FETCH NEXT 1 ROW ONLY) as Row2Column1,
(SELECT HomeCost from #TABLE_2 where id = t1.ID order by ID OFFSET 3 ROW FETCH NEXT 1 ROW ONLY) as Row2Column2 from #TABLE_1 as t1

A example of my data is 

CREATE TABLE #TABLE_1(ID int, NAME varchar(100), LASTNAME varchar(100))
CREATE TABLE #TABLE_2(ID int,HomeType varchar(100), HomeCost varchar(100) )



Insert into #TABLE_1 (ID, NAME, LASTNAME) VALUES (1, 'JOHN', 'SNOW')
Insert into #TABLE_2 (ID, HomeType, HomeCost) VALUES (1, 'Type1', 'Cost1')
 Insert into #TABLE_2 (ID, HomeType, HomeCost) VALUES (1, 'Type2', 'Cost2')
 Insert into #TABLE_2 (ID, HomeType, HomeCost) VALUES (1, 'Type3', 'Cost3')
 Insert into #TABLE_2 (ID, HomeType, HomeCost) VALUES (1, 'Type4', 'Cost4')

I Want results like this but for 50000 rows it is to slow

ID| NAME| ROW1COLUMN1 | ROW1COLUMN2 | ROW2COLUMN1 | ROW2COLUMN2 | ROW3COLUMN1 | ROW3COLUMN2 | ROW4COLUMN1 | ROW4COLUMN2
-------------------------
 1|JOHN | Type1       | Cost1       | Type2       | Cost2       | Type3     | Cost3         | Type4       | Cost4

Upvotes: 0

Views: 73

Answers (3)

Daniel Brughera
Daniel Brughera

Reputation: 1651

You can use PIVOT 

WITH T2 AS (
    SELECT 
        ID, 
        HomeTypeRN = 'A' + CAST( ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HomeType) AS VARCHAR),
        HomeType, 
        HomeCostRN = 'B' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HomeType) AS VARCHAR), 
        HomeCost
    FROM #TABLE_2
)
SELECT 
    T1.ID, T1.Name, T1.LastName, [A1], [B1], [A2], [B2], [A3], [B3], [A4], [B4]
FROM #TABLE_1 T1
LEFT JOIN
(
    SELECT ID, 
    MAX(A1) AS A1, MAX(B1) AS B1, 
    MAX(A2) AS A2, MAX(B2) AS B2, 
    MAX(A3) AS A3, MAX(B3) AS B3, 
    MAX(A4) AS A4, MAX(B4) AS B4 
    FROM T2 
    PIVOT(
        MAX(HomeType) FOR HomeTypeRN IN ([A1],[A2],[A3],[A4])
    ) P1
    PIVOT(
        MAX(HomeCost) FOR HomeCostRN IN ([B1],[B2],[B3],[B4])
    ) P2
    GROUP BY ID
) P  ON P.ID = T1.ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you have the same four types, then you can use conditional aggregation like this:

select t1.id, t1.name, t1.lastname,
       max(case when t2.type = 'type1' then hometype end) as hometype_1,
       max(case when t2.type = 'type1' then homecost end) as homecost_1,
       max(case when t2.type = 'type2' then hometype end) as hometype_2,
       max(case when t2.type = 'type2' then homecost end) as homecost_2,
       max(case when t2.type = 'type3' then hometype end) as hometype_3,
       max(case when t2.type = 'type3' then homecost end) as homecost_3,
       max(case when t2.type = 'type4' then hometype end) as hometype_4,
       max(case when t2.type = 'type4' then homecost end) as homecost_4
from table1 t1 left join
     t2
     on t1.id = t2.id
group by t1.id, t1.name, t1.lastname;

Upvotes: 0

Daler
Daler

Reputation: 93

I didn't test this query, but it should work. Don't forget to do indexes for ID's, HomeType fields. If field 'HomeType' is dynamic you need to add additional field 'pos'. 'pos' field should unique for rows in TABLE_2 with the same IDs.

SELECT t.ID, t.NAME, t.LASTNAME, t1.HomeType AS Row1Column1, t1.HomeCost AS Row1Column2, t2.HomeType AS Row2Column1, t2.HomeCost AS Row2Column2, t3.HomeType AS Row3Column1, t3.HomeCost AS Row3Column2, t4.HomeType AS Row4Column1, t4.HomeCost AS Row4Column2
FROM TABLE_1 t
JOIN TABLE_2 t1 ON t.ID = t1.ID AND t1.HomeType = 'Type1'
JOIN TABLE_2 t2 ON t.ID = t2.ID AND t2.HomeType = 'Type2'
JOIN TABLE_2 t3 ON t.ID = t3.ID AND t3.HomeType = 'Type3'
JOIN TABLE_2 t4 ON t.ID = t4.ID AND t4.HomeType = 'Type4'

Upvotes: 1

Related Questions