Subquery v/s inner join in sql server

Question

I have following queries

First one using inner join

SELECT item_ID,item_Code,item_Name 
FROM [Pharmacy].[tblitemHdr] I 
    INNER JOIN  EMR.tblFavourites F ON I.item_ID=F.itemID
WHERE F.doctorID = @doctorId AND F.favType = 'I'

second one using sub query like

SELECT item_ID,item_Code,item_Name from [Pharmacy].[tblitemHdr]
WHERE item_ID IN
(SELECT itemID FROM EMR.tblFavourites
WHERE doctorID = @doctorId AND favType = 'I'
)

In this item table [Pharmacy].[tblitemHdr] Contains 15 columns and 2000 records. And [Pharmacy].[tblitemHdr] contains 5 columns and around 100 records. in this scenario which query gives me better performance?

Solution 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.

Here and here some links to help.

Solution 2

In Sql Server Management Studio you can enable "Client Statistics" and also Include Actual Execution Plan. This will give you the ability to know precisely the execution time and load of each request.

Also between each request clean the cache to avoid cache side effect on performance

USE <YOURDATABASENAME>;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

I think it's always best to see with our own eyes than relying on theory !

Solution 3

Sub-query Vs Join

Table one 20 rows,2 cols

Table two 20 rows,2 cols

sub-query 20*20

join 20*2

logical, rectify

Detailed

The scan count indicates multiplication effect as the system will have to go through again and again to fetch data, for your performance measure, just look at the time

Solution 4

join is faster than subquery.

subquery makes for busy disk access, think of hard disk's read-write needle(head?) that goes back and forth when it access: User, SearchExpression, PageSize, DrilldownPageSize, User, SearchExpression, PageSize, DrilldownPageSize, User... and so on.

join works by concentrating the operation on the result of the first two tables, any subsequent joins would concentrate joining on the in-memory(or cached to disk) result of the first joined tables, and so on. less read-write needle movement, thus faster

Source: Here

Solution 5

First query is better than second query.. because first query we are joining both table. and also check the explain plan for both queries...

View more solutions

Comments

[Ibrahim ULUDAG]

This is always better. Sometimes the performance may depend on the data in the tables or other factors.

[Trisped]

I have found using the CPU, Reads, Writes, and Duration columns in SQL Server Profiler to be more accurate than even the actual performance planes. Still, this is a good option for most use cases.

[MGOwen]

In all my years of SQL I've never used a subquery in the select line like in your second example. Not sure if that would have the same kind of performance as the commonly-used subquery in the where clause (like this question is about).

[afruzan]

I think it depends on execution plan.