CODEWITHSUNDEEP
sqlsql-server
Vineesh
Vineesh

Reputation: 3782

SQL Query performance with inner queries

I am not sure whether the title of this question is correct or not.

I have a table for example users which contains different types of users. Like user type 10, 20, 30 etc.

In a query I need to join the user table, but I want only user type 20. So which of the below query perform better.

 SELECT fields 
 FROM consumer c 
 INNER JOIN user u ON u.userid = c.userid
 WHERE u.type = 20

In another way, 

 SELECT fields 
 FROM consumer c 
 INNER JOIN (SELECT user_fields FROM  user WHERE type = 20) u ON u.userid = c.userid

Please advice.

Upvotes: 0

Views: 125

Answers (4)

user1529235
user1529235

Reputation:

Both your options are valid. Personally would code it like this;

SELECT fields 
FROM consumer c 
INNER JOIN user u ON u.userid = c.userid and u.type = 20

Run both queries in SQL Management Studio (query) and tick 'Include actual execution plan'. This will let you see the performance of your queries against each other. It will depend on your particular database.

Upvotes: 0

Hrabal
Hrabal

Reputation: 2523

It really depends on a various number of factors:

  • is "userid" on both table indexed?
  • is "type" on table "users" indexed?
  • how many rows in each table?

Usually a subquery produces slower performances, but depending on the conditions listed above and how your sql server installation is configured, both query can be resolved (and so, executed) as the same by the query analyzer.

SQLServer takes your query and tries to optimize it so it can happen that query B is "transformed" in query A.

Look at the QueryAnalyzer tool for both queries, and see if they have differences.

Generally speaking inner queries are better to be avoided, and you'll probably get the best performances doing query A.

Upvotes: 1

M.Hassan
M.Hassan

Reputation: 11092

Run the two queries in SSMS as a batch, and click "execution plan" , you will find that the execution plan of both queries, and the query cost (relative to the batch ): 50%

That means they are the same.

If they are different (in case of some optimization), you find the ratio different.

I simulated your query and find the query cost=50% ===> i.e they are the same.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Let's start with this query:

SELECT . . . 
FROM consumer c INNER JOIN 
     user u
      ON u.userid = c.userid
 WHERE u.type = 20;

Assuming that type is relatively rare, you want indexes on the tables. The best indexes are probably user(type, userid) and customer(userid). It is possible that an index on user(userid, type) would be better (and would be unnecessary if userid is a clustered primary key).

The second query . . . well, from the SQL Server perspective it is probably the same. Why? SQL Server has a good optimizer. You can check the execution plans if you like. Because of the optimizer:

  1. There is no benefit to having a subquery select only a handful of columns. For better or worse, SQL Server pushes that information down to the node that reads the data.
  2. The where clause is not necessarily going to be evaluated before the join. SQL Server is smart enough to re-arrange operations.

Not all optimizers are this smart. In a database such as MySQL, MS Access, or SQLite, I'm pretty sure the first version is much better than the second.

Upvotes: 5

Related Questions