CODEWITHSUNDEEP
sqlsql-server
Mausimo
Mausimo

Reputation: 8168

Is it better to do multiple selects from multiple tables or 1 select of all your data from all the tables?

I have multiple tables that data can be queried from with joins.

In regards to database performance:

Should I run multiple selects from multiple tables for the required data?

or

Should I write 1 select that uses a bunch of Joins to select the required data from all the tables at once?

EDIT:

The where clause I will be using for the select contains Indexed fields of the tables. It sounds like because of this, it will be faster to use 1 select statement with many joins. I will however still test the performance difference between the 2.

Thanks for all the great answers.

Upvotes: 4

Views: 1438

Answers (5)

hmmftg
hmmftg

Reputation: 1754

Depends on your Table designs.

Most of times one large query is better but be sure to

  • Use primary keys in where clause as much as you can for joins.

  • use indexed fields or make indexes for fields which are used in where clauses.

Upvotes: 1

Ray
Ray

Reputation: 41508

This can be one of those, well-gee-it-depends, but generally if you're writing straight SQL do one query--especially since the joins might limit some of the data you get back.

There is a good chance if you do multiple point queries for one record in each table, if you're using the primary key of the table for lookup, the connection cost for each query will be more costly than the actual query.

Upvotes: 4

Mike S.
Mike S.

Reputation: 4879

If you have proper indexes on your tables, you might be better off with the JOINs but they are often the cause of bottlenecks. Instead of multiple selects, you might look at ways to de-normalize your data. It is far less "expensive" when a user performs an operation to update a count or timestamp in multiple tables which prevents you from having to join those tables.

The best tool I find for performance tuning of queries is using EXPLAIN. You type EXPLAIN before the query and you can see how many rows are scanned. Your goal is the lower the number the better, which means your indexes are working. The other thing is when creating indexes, use compound indexes on multiple fields and order them left to right in the order they appear in the WHERE clause.

For example you have 10,000 rows in sometable:

SELECT id, name, description, status FROM sometable WHERE name LIKE '%someName%' AND status = 'Active';

You could type EXPLAIN before the query and it might return 10,000 as number of rows scanned to match. You then create a compound index:

ALTER TABLE sometable ADD INDEX idx_st_search (name, status);

You then perform the EXPLAIN on table again and it might return 1 as number of rows scanned and performance significantly improved.

Upvotes: 3

Abe Miessler
Abe Miessler

Reputation: 85126

Just write one query with joins. If you are concerned about performance there are a number of options including:

  • Creating indexes that will help the performance of your selects
  • Create a persisted denormalized form of the data you want so you can query one table. This would most likely be an indexed view or another table.

Upvotes: 4

ewein
ewein

Reputation: 2735

It depends on how the tables are joined. If you do a cross-product of all tables than it would be better to do individual selects. However if your tables are properly indexed and well thought out one query with multiple selects would be more efficient.

Upvotes: 3

Related Questions