Multiple queries to avoid joins?

Question

I've noticed as soon as I added joins to some of my queries the time it took to execute these was more than just completing multiple queries.

Times include page load and averaged over 20 page loads.

7-9 queries with no joins
159ms

3 queries with 2 joins
235ms

Should I just go ahead with multiple queries instead of the joins considering they seem to have such a significant impact on performance? I can probably even optimize the multiple query method since I was even lazy loading during those tests.

EDIT

I'll create some false information for sake of the question.

Table Objects
ID (int, identity, PK, clustered index)
UserID (int, nonclustered index)
CategoryID (int, nonclustered index)

Table Users
ID (int, identity, PK, clustered index)

Table Categories
ID (int, identity, PK, clustered index)

Pretty simple. It's a double inner-join query onto the Objects table. Querying all 3 separately seems to be faster than the join.

The query plan for the join shows 42% done for 2 clustered index seeks and 23% is a clustered index scan and the rest is a Top N Sort.

Answer 1

If you're doing joins for no reason, sure. Usually the reason you're joining tables is to be able to pull related data and deal with it. Your question above doesn't also take into account the programmatic time you're going to need to pull that data back together (probably via a loop structure or something like that).

Query analysis should be the first step here. I'm not very familiar with your particular flavor of SQL, but it would likely be something like EXPLAIN.

If I had to give a likely culprit based on the limited information I have here, it would be missing indexes. Are the fields you're joining on indexed properly? This can garner HUGE performance gains. Second, are you joining on proper fields? For instance, if you're joining two strings together, your performance is going to be much worse than joining an integer or other optimized field.

Answer 2

No, you should actually try to go the other way instead. You should try to have as few queries as possible. When done correctly, that is the fastest.

Check that you have proper indexes on the table. For example, for a query like this:

select a.Some, b.Other
from TableA a
inner join TableB b on b.Id = a.Id

You should make sure that there is an index on the TableB.Id field. The primary key of a table normally gets an index by default, but other indexes you have to create yourself.