CODEWITHSUNDEEP
sqlsql-serversql-server-2008sqlperformance
Rob Lao
Rob Lao

Reputation: 1623

Why inner join with a filtered table more slowly than that without filtering?

SQL server 2008 on WINDOWS 2008

Please compare following sqls:

1.

select count(*) 
from Trades t 
inner join UserAccount ua on ua.AccID = t.AccID

2.

select count(*) 
from Trades t 
inner join (
    select * 
    from UserAccount ua 
    where ua.UserID = 1126
) as theua on theua.AccID = t.AccID

3.

select count(*) 
from Trades t 
inner join UserAccount ua on ua.AccID = t.AccID 
where ua.UserID=1126

Given Trades has millions of rows and UserAccount is a quite small table. And AccID can be duplicative.

Execution result:

  1. 234734792
  2. 8806144
  3. 8806144

I expect No.2 can be at least as fast as No.1, but actually it's much slower even slower than No.3 Time consumption:

  1. 2 secs
  2. 10 secs
  3. 8 secs

Could someone explain the reason? And is it possible to make it faster when I need a filter like UserID=1126?

Upvotes: 3

Views: 400

Answers (1)

Timeout
Timeout

Reputation: 7909

  1. is the fastest since it has the least amount of where conditions.
    (The missing UserID)

  2. is the slowest because it has an inner select which has to execute for each join
    (btw: never do this)

  3. is slower than #1 because of the extra where condition (UserID). This is the query you want to use.
    (You could also swap the "where" for an "and" directly after the join on)

Do you have foreign keys set up?

Also make sure you have the appropriate Indexes (IE: AccID & UserID).

From SSMS, run the query with the Execution Plan on and it will show you potential inefficiencies in the query / indexes you should create.

In the execution plan you should look out for things like tables scans. What you want to see are seeks.

Upvotes: 3

Related Questions