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?

Answer 1

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

Answer 2

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
        );

Answer 3

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