Menu
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?
988
I won't leave you in suspense, between Joins and Subqueries, joins tend to execute faster. In fact, query retrieval time using joins will almost always outperform one that employs a subquery. The reason is that joins mitigate the processing burden on the database by replacing multiple queries with one join query.
When a query is included inside another query, the Outer query is known as Main Query, and Inner query is known as Subquery. In Nested Query, Inner query runs first, and only once. Outer query is executed with result from Inner query. Hence, Inner query is used in execution of Outer query.
Inner Join will execute sub query only once.
The performance of CTEs and subqueries should, in theory, be the same since both provide the same information to the query optimizer. One difference is that a CTE used more than once could be easily identified and calculated once. The results could then be stored and read multiple times.
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.
127
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 !
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
