47

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?

valex
  • 23,966
  • 7
  • 43
  • 60
Nithesh Narayanan
  • 11,481
  • 34
  • 98
  • 138

5 Answers5

37

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.

Diego
  • 34,802
  • 21
  • 91
  • 134
15

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 !

Arno 2501
  • 8,921
  • 8
  • 37
  • 55
  • This is always better. Sometimes the performance may depend on the data in the tables or other factors. – Ibrahim ULUDAG Dec 27 '12 at 10:02
  • 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. – Trisped Feb 12 '15 at 21:45
8

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

enter image description here

enter image description here

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

Pierre.Vriens
  • 2,117
  • 75
  • 29
  • 42
  • 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). – MGOwen Mar 24 '18 at 06:40
6

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

Community
  • 1
  • 1
Paz
  • 694
  • 1
  • 5
  • 14
2

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

murali krishna
  • 67
  • 1
  • 5