CODEWITHSUNDEEP
sqlsql-server
L-Four
L-Four

Reputation: 13541

Replace INNER JOINS with multiple SELECTS?

I have a a Screening table, which holds a number of screenings, primary key is an int. I also have 6 other tables (LengthMeasurement, WeightMeasurement etc), which are linked with a foreign key constraint to the Screening table.

In order to get the screening and all of its related information, I first used an INNER join like:

SELECT *
FROM [user].[Screening] scr                
INNER JOIN [user].[LengthMeasurement] lm ON scr.Id = lm.ScreeningId
INNER JOIN [user].[WeightMeasurement] wm ON scr.Id = wm.ScreeningId
INNER JOIN [user].[Note] n ON scr.Id = n.ScreeningId
INNER JOIN [user].[IntakeRegistration] ir ON scr.Id = ir.ScreeningId
INNER JOIN [user].[RiskProfile] rfr ON scr.Id = rfr.ScreeningId
INNER JOIN [user].[OncologyTasteAnamnesis] ota ON scr.Id = ota.ScreeningId
WHERE scr.UserId = 2

However, even with a small amount of data, this already takes like 2 seconds and gets more than 30.000 rows (cartesian product).

If I do the same, but this time only with simple SELECTS, like:

SELECT * FROM [user].[Screening] scr WHERE UserId = 2  
SELECT * FROM [user].[LengthMeasurement] WHERE [ScreeningId] IN
(
    SELECT Id FROM [user].[Screening]
    WHERE UserId = 2
)
SELECT * FROM [user].[WeightMeasurement] WHERE [ScreeningId] IN
(
    SELECT Id FROM [user].[Screening]
    WHERE UserId = 2
)
SELECT * FROM [user].[Note] WHERE [ScreeningId] IN
(
    SELECT Id FROM [user].[Screening]
    WHERE UserId = 2
)
SELECT * FROM [user].[IntakeRegistration] WHERE [ScreeningId] IN
(
    SELECT Id FROM [user].[Screening]
    WHERE UserId = 2
)
SELECT * FROM [user].[RiskProfile] WHERE [ScreeningId] IN
(
    SELECT Id FROM [user].[Screening]
    WHERE UserId = 2
)
SELECT * FROM [user].[OncologyTasteAnamnesis] WHERE [ScreeningId] IN
(
    SELECT Id FROM [user].[Screening]
    WHERE UserId = 2
)

Like this it's a few milliseconds, and I retrieve only the rows needed (35).

So obviously the second way is fasted, but I was wondering if this is really a good idea, of maybe there are other solutions to make this simple query fast?

Upvotes: 1

Views: 196

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You are joining along different dimensions, which is resulting in the Cartesian product for a single user.

Clearly, the INNER JOIN does not return a reasonable result set, because you are getting way more rows than you need.

If the separate queries meet your needs than that is definitely a feasible alternative. You cannot readily combine them into a single query using UNION ALL, because the columns are (in all likelihood) quite different.

Otherwise, you need to decide on the result set that you actually want. Then, you can ask specifically about how to return that.

Upvotes: 2

Related Questions