Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?

TL;DR: If my joined query takes longer than running individual queries, is this my fault or is this to be expected?

First of, I am not very database savvy, so it may be just me, but I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.

I have tried to put one extremely simple example together:

SQL Fiddle

Schema Setup:

CREATE TABLE MASTER 
( ID INT NOT NULL
, NAME VARCHAR2(42 CHAR) NOT NULL
, CONSTRAINT PK_MASTER PRIMARY KEY (ID)
);

CREATE TABLE DATA
( ID INT NOT NULL
, MASTER_ID INT NOT NULL
, VALUE NUMBER
, CONSTRAINT PK_DATA PRIMARY KEY (ID)
, CONSTRAINT FK_DATA_MASTER FOREIGN KEY (MASTER_ID) REFERENCES MASTER (ID)
);

INSERT INTO MASTER values (1, 'One');
INSERT INTO MASTER values (2, 'Two');
INSERT INTO MASTER values (3, 'Three');

CREATE SEQUENCE SEQ_DATA_ID;

INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.5);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 1, 1.7);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 2, 2.3);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.14);
INSERT INTO DATA values (SEQ_DATA_ID.NEXTVAL, 3, 3.7);

Query A:

select NAME from MASTER
where ID = 1

Results:

| NAME |
--------
|  One |

Query B:

select ID, VALUE from DATA
where MASTER_ID = 1

Results:

| ID | VALUE |
--------------
|  1 |   1.3 |
|  2 |   1.5 |
|  3 |   1.7 |

Query C:

select M.NAME, D.ID, D.VALUE 
from MASTER M INNER JOIN DATA D ON M.ID=D.MASTER_ID
where M.ID = 1

Results:

| NAME | ID | VALUE |
---------------------
|  One |  1 |   1.3 |
|  One |  2 |   1.5 |
|  One |  3 |   1.7 |

Of course, I didn't measure any performance with these, but one may observe:

  • Query A+B returns the same amount of usable information as Query C.
  • A+B has to return 1+2x3==7 "Data Cells" to the client
  • C has to return 3x3==9 "Data Cells" to the client, because with the join I naturally include some redundancy in the result set.

Generalizing from this (as far fetched as it is):

A joined query always has to return more data than the individual queries that receive the same amount of information. Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.

Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?

share|improve this question
    
Ah, this may be a duplicate or not. Seems a bit more specific. –  Martin May 24 '13 at 14:26
5  
"Of course, I didn't measure any performance with these ..." -- I may have found the flaw in your approach. Performance is very measurable, and I'd urge you to follow that path rather than the speculative one. –  David Aldridge May 24 '13 at 16:27
2  
Your queries are labeled 1, 2, and 3. But you refer to them a A, B, and C. Sorry, but I'm a stickler about data. There are a lot more of us in here. –  Walter Mitty May 25 '13 at 19:32
    
@DavidAldridge: Ah, but see my last phrase: Would it follow from this, ... that when performance of a joined query is crap, should I go spend a day trying to find out what's wrong with the database or should I just spend two hours splitting the query and fixing performance that way. –  Martin May 27 '13 at 4:58
1  
One thing I'd recommend is trying out with realistic data sets instead of ideal cases. In your example ALL the rows are involved in the joined result, whereas in practice, that may not always be the case to do so. So for example, out of 5 rows returned in query 1, only 2 are actually involved in the join, in which case, the join operation would be much more efficient. –  kicker86 Jun 17 at 15:40

3 Answers 3

up vote 22 down vote accepted

Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient?

In any performance scenario, you have to test and measure the solutions to see which is faster.

That said, it's almost always the case that a joined result set from a properly tuned database will be faster and scale better than returning the source rows to the client and then joining them there. In particular, if the input sets are large and the result set is small -- think about the following query in the context of both strategies: join together two tables that are 5 GB each, with a result set of 100 rows. That's an extreme, but you see my point.

I have noticed that when I have to get information from multiple tables, it is "often" faster to get this information via multiple queries on individual tables (maybe containing a simple inner join) and patch the data together on the client side that to try to write a (complex) joined query where I can get all the data in one query.

It's highly likely that the database schema or indexes could be improved to better serve the queries you're throwing at it.

A joined query always has to return more data than the individual queries that receive the same amount of information.

Usually this is not the case. Most of the time even if the input sets are large, the result set will be much smaller than the sum of the inputs.

Depending on the application, very large query result sets being returned to the client are an immediate red flag: what is the client doing with such a large set of data that can't be done closer to the database? Displaying 1,000,000 rows to a user is highly suspect to say the least. Network bandwidth is also a finite resource.

Since the database has to cobble together the data, for large datasets one can assume that the database has to do more work on a single joined query than on the individual ones, since (at least) it has to return more data to the client.

Not necessarily. If the data is indexed correctly, the join operation is more likely to be done more efficiently at the database without needing to scan a large quantity of data. Moreover, relational database engines are specially optimized at a low level for joining; client stacks are not.

Would it follow from this, that when I observe that splitting a client side query into multiple queries yield better performance, this is just the way to go, or would it rather mean that I messed up the joined query?

Since you said you're inexperienced when it comes to databases, I would suggest learning more about database design and performance tuning. I'm pretty sure that's where the problem lies here. Inefficiently-written SQL queries are possible, too, but with a simple schema that's less likely to be a problem.

Now, that's not to say there aren't other ways to improve performance. There are scenarios where you might choose to scan a medium-to-large set of data and return it to the client if the intention is to use some sort of caching mechanism. Caching can be great, but it introduces complexity in your design. Caching may not even be appropriate for your application.

One thing that hasn't been mentioned anywhere is maintaining consistency in the data that's returned from the database. If separate queries are used, it's more likely (due to many factors) to have inconsistent data returned, unless a form of snapshot isolation is used for every set of queries.

share|improve this answer
    
Your answer is so much better than mine, that I deleted mine. This is what I wanted to say but didn't manage to actually say. –  HLGEM May 24 '13 at 18:02
    
@HLGEM: Wow, thanks. –  Jon Seigel May 24 '13 at 18:07
1  
You misget the point - typical for a dba and not a programmer. Most of the time it is stuff like "get invoice x and customer data for invoice x". do you join invoice and customer, or make select invoice, then select customer - and bad new: VERY often it is faster to make two selects. Sometimes the client side join is even providing more data. Get ivoice, get invoice states for dropdown first, then dont join but do lookup on the UI - so with the same approach I already have ALL States for the change UI, too. –  TomTom May 24 '13 at 18:47
1  
THis is not about "pulling the table then filtering client side", it is about the fact that if you need to do a 5 a table join on SMALL Data, it is often FASTER to make 5 selects than to wait for the query optimizer to figure out what to do. –  TomTom May 24 '13 at 18:48
2  
@TomTom: For small data sets, you could be right; again, it would have to be tested. However, when the n SELECTs type of logic is implemented, it's a very slippery slope to reuse the same code to load a set of things, which, in general, performs horribly. Lazy-loading mechanisms in particular are notorious for this type of behaviour. By the way, the question did make at least passing mention of large data sets. And also, I come from a programming background, so I understand very well this type of antipattern. –  Jon Seigel May 24 '13 at 19:06

Of course, I didn't measure any performance with these

You put together some good sample code. Did you look at the timing in SQL Fiddle? Even some brief unscientific performance testing will show that query three in your demonstration takes about the same amount of time to run as either query one or two separately. Combined one and two take about twice as long as three and that is before any client side join is performed.

As you increase the data, the speed of query one and two would diverge, but the database join would still be faster.

You should also consider what would happen if the inner join is eliminating data.

share|improve this answer
    
The timing for trivial examples is irrelevant, IMHO. –  Martin May 27 '13 at 4:55
    
@Martin - Normally I would agree, but in this case, I think it answers the question with requiring a more rigorous, real-world test. –  Leigh Riffel May 28 '13 at 13:17

Multiple queries IS the way to go. If you handle simple scenarios like that - the cost overhead of the query optimizer is a factor. With more data, the network inefficiency of the join (redundant rows) comes in. Only with a lot more data is there efficiency.

At the end, what you experience is something many developers see. The DBAs always say "no, make a join" but reality is: it IS faster to make multiple simple selects in this case.

share|improve this answer
4  
There's no "network inefficiency" in a join - it all happens on the database server, so there's no network involved (unless you're joining over a db link!) –  Chris Saxon May 24 '13 at 15:11
1  
Ah, so if you pull 4x the network traffic that all happens on the database? Nice, ever heard of people actually getting data OUT of the database, to another computer? –  TomTom May 24 '13 at 15:12
2  
You might like to consider whether the network layer has compression or not. Oracle's SQL*Net does, in that values repeating in the same column are efficiently compressed. –  David Aldridge May 24 '13 at 16:24
2  
@TomTom you may have a point or not (as David Aldridge points, compression matters) but your wording is confusing. "network inefficiency of the join"? Really, fix that so it is obvious what you mean. –  ypercube May 24 '13 at 16:39
    
TomTom - I do appreciate your insight here. Good to have some opposing view :-) Maybe you could try to work your comments to @JonSeigel s answer into this answer here. –  Martin May 27 '13 at 4:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.