1

I am working on a project with lots of large SQL requests, I want to know:

what provides a better performance in term of speed between:

  • Join Queries
  • Sub-Queries

For example 

SELECT artistName FROM artist
JOIN group 
On group.location = artist.location
AND group.available = 'true'

SELECT artistName FROM artist,
(SELECT group.available = true) AS groupAvailable
WHERE groupAvailable.location = artist.location

What can provide me with better performance for big queries.

william.eyidi
  • 2,315
  • 4
  • 27
  • 39
  • 2
    Does the 2nd query even run? In what DBMS? – ypercubeᵀᴹ Aug 28 '14 at 17:20
  • 1
    My instinct is the later would be faster because you're subquery eliminates rows where `group.aavailable != true` and so the join isn't on as many records. That said, time it and see. Although, you do have a syntax error in your sub query. – J. A. Streich Aug 28 '14 at 17:20
  • Joins apparenty are generally better. Already asked and answered. https://stackoverflow.com/questions/2577174/join-vs-sub-query – J. A. Streich Aug 28 '14 at 17:26
  • @Streich that is whammy first tough as well cause less row means better performance... – william.eyidi Aug 28 '14 at 18:23

3 Answers3

1

I'm not quite sure your second query is valid SQL, but the answer to your question fortunately isn't related to this.

Performance is dependant on many, many factors, so the best answer you can get for this kind of question is "It depends".

First, make sure you need to optimize your queries. If the current runtime is acceptable, do not touch anything (don't fix what's not broken).

If you do need to optimize your queries, try it out for yourself. Depending on the DBMS, the server load, the data distribution and potentially other variables, your performance might change a lot, so the only way you can know is to test for your actual situation and determine if the result is what you want.

Jonathan M
  • 1,891
  • 13
  • 21
1

As you have not mentioned that you need to know the answer for any specific query, I am assuming that you asked the question in general whether join queries are faster than subqueries or not. The answer is in most cases join queries are faster than subqueries. I too didn't know the answer, so after I saw your question I researched and I am quoting from what I found -

In JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.

Actually you are not the first one who asked this question. If I got you right, another similar question is here too.

Community
  • 1
  • 1
taufique
  • 2,701
  • 1
  • 26
  • 40
1

As several others have mentioned, it really DOES just depend. The database running your query doesn't always follow your code exactly as it is written. It will often run quite differently than what you expect. In fact, both queries you wrote might run exactly the same way. Try turning on stats and run both queries to check (assuming you are on MS SQL Server)

SET STATISTICS IO ON
SET STATISTICS TIME ON

When you run your query with these two settings on you can see how many logical reads and how much cpu time is taken. It can be really helpful in identifying performance bottlenecks.