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

Have you tried running your queries with the joins through the analyzer to see if there's anything you do do to optimize them?

Answer 2

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 3

I recommend you look at the actual query plan and understand why the responses differ. Perhaps it will highlight a table scan suggestion adding an index perhaps?

Doing joins are more expensive. That doesn't mean you shouldn't use them though.

Would help to see the SQL you are writing... (produced by LINQ to SQL)

Answer 4

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.

Answer 5

if you're running these just to get data in query analyzer then multiple queries is fine but if you're running them from a web app or console app then you'll want to optimize to 1 query. Even if it takes a bit longer you'll see gains in performance since you're not hitting your database more than once. The less you hit your db the better your front end performance will be. I would work to re-do your queries to be only one. It looks like your tables are normalized and indexed enough that you should be able to get it down to one query.