inner join vs where clause subquery sql server

Question

I am trying to speed up my stored procedure, so I test my stored procedure in two formats using statistics io like below

Method 1: using join

set statistics io on 

select top 2000   
    p.Vehicleno, 
    dbo.GetVehicleStatusIcon1(p.Direction, StatusCode, 0) as 'Status',
    location,
    Convert(varchar(13), p.TrackTime, 102) + ' ' + Convert(varchar(13), p.TrackTime, 108) AS 'TrackTime',
    p.Speed, p.Ignition
from 
    pollingdata p 
inner join 
    assignvehicletouser asn on asn.vehicleno = p.vehicleno
where 
    asn.empid = 1

I get statistics result as

Table 'Worktable'. Scan count 943, logical reads 7671, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AssignVehicleToUser'. Scan count 1, logical reads 41, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PollingData'. Scan count 1, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Method 2: using where clause sub-query

set statistics io on 

select top 2000 
    Vehicleno,
    dbo.GetVehicleStatusIcon1(Direction,StatusCode, 0) as 'Status',
    location,
    Convert(varchar(13), TrackTime, 102) + ' ' + Convert(varchar(13), TrackTime, 108) AS 'TrackTime',
    Speed, Ignition
from 
    pollingdata 
where 
    vehicleno in (select vehicleno 
                  from assignvehicletouser 
                  where empid = 1)

I get statistics result as

Table 'PollingData'. Scan count 1, logical reads 50, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AssignVehicleToUser'. Scan count 1, logical reads 41, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Need to know which one is best to use?

Need explanation about how logical reads works here?

Always it is recommended to use Joins instead IN. SO first method will be best. — Sridhar DD, Jan 30 '15 at 10:25
@SridharDD It was "always recommended" ten years ago. Just stick with the meaning of the operation - is it a logical join or not? — Luaan, Jan 30 '15 at 10:35
Could you include the actual query plan? I guess it falls back on a semi join for the `IN` query. — Caramiriel, Jan 30 '15 at 10:41
Have you considered that the UDF `dbo.GetVehicleStatusIcon1` is the performance bottleneck? How complex is this function and what does it do? Can you write it inline in the SQL query? If it's just a case statement or a lookup, write it inline instead. — Nick.Mc, Jan 30 '15 at 11:31
A logical read is the number of 'pages' (a unit of storage 8Kb I think) that the query has to read. A physical read is when it actually has to read it from the disk instead of memory. Memory reads (logical - physical) are fast. Any of these that get turned into physical reads (because they aren't in memory) are slow. You can reduce the amount of physical reads by having lots of memory. To compare the queries on a level playing field you actually have to ignore physical reads. — Nick.Mc, Jan 30 '15 at 11:37

Evaldas Buinauskas · Answer 1 · 2016-01-13T09:23:55.967

7

If you don't need anything from assignvehicletouser table, I'd prefer EXISTS (It probably works same way as IN)

SELECT TOP (2000) p.Vehicleno
    , dbo.GetVehicleStatusIcon1(p.Direction, StatusCode, 0) AS 'Status'
    , location
    , CONVERT(VARCHAR(13), p.TrackTime, 102) + ' ' + CONVERT(VARCHAR(13), p.TrackTime, 108) AS 'TrackTime'
    , p.Speed
    , p.Ignition
FROM pollingdata p
WHERE EXISTS (
        SELECT 1
        FROM assignvehicletouser asn
        WHERE asn.vehicleno = p.vehicleno
            AND asn.empid = 1
        );

edited Jan 13 '16 at 09:23

answered Jan 30 '15 at 10:47

Evaldas Buinauskas

13,739
11
55
107

1

[No, `exists` is not the same as `in` in SQL Server](http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance) – potashin Jun 20 '15 at 18:16

score 1 · Answer 2 · answered Jan 30 '15 at 12:11

Based on the statistics results provided second one seems to better as the first one is having extra tempdb "worktable" operation which is used for holding temporary/intermediate operations/results like SORTING operations

Logical Read: A logical read occurs every time a page read from the SQL server buffer cache, which is good.

Physical Read: It occurs when the required data page is not available in buffer cache, system reads it from disk (Physical IO) and copied to buffer cache.

You can use CROSS APPLY to join UDF instead of calling it directly and in the first query WHERE condition (asn.emp=1) can directly applied after 'ON' condition like asn.vehicleno = p.vehicleno AND asn.empid = 1

score 0 · Answer 3 · answered Jan 30 '15 at 10:33

Usually joins will work faster than inner queries, but in reality it will depend on the execution plan generated by SQL Server.

No matter how you write your query, SQL Server will always transform it on an execution plan.

If it is "smart" enough to generate the same plan from both queries, you will get the same result.

You can read Subquery or Join and here

inner join vs where clause subquery sql server

3 Answers3