Click here to Skip to main content
15,885,244 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
subquery :

SQL
Declare @count int=0;
;with CTE(RM_PART_NO ,FG_PART_NO,LEVEL)
as
(
select RM_PART_NO ,FG_PART_NO,0 as LEVEL from BOM_MASTER (nolock) where FG_PART_NO='29049260' and WH_CODE='0021'
union all
select BOM.RM_PART_NO, CTE.RM_PART_NO,LEVEL+1 from CTE as CTE inner join BOM_MASTER BOM  (nolock) on BOM.FG_PART_NO=CTE.RM_PART_NO where WH_CODE='0021'
)
select @count=count(RM_PART_NO) From CTE where RM_PART_NO=( SELECT SEWS_PART_NO FROM PRODUCT_MASTER with(nolock) WHERE ITEM_NO = ( SELECT ITEM_NO
FROM INV_SERIAL with(nolock) WHERE SERIAL_NO = 'BA00586147' )) group by CTE.LEVEL  order by LEVEL  option(maxrecursion 0) 
if (@count=0)
begin
select 'FG & RM PART MISMATCH '
end


Join :


SQL
Declare @count int=0;
;with CTE(RM_PART_NO ,FG_PART_NO,LEVEL)
as
(
select RM_PART_NO ,FG_PART_NO,0 as LEVEL from BOM_MASTER (nolock) where FG_PART_NO='29049260' and WH_CODE='0021'
union all
select BOM.RM_PART_NO, CTE.RM_PART_NO,LEVEL+1 from CTE as CTE inner join BOM_MASTER BOM  (nolock) on BOM.FG_PART_NO=CTE.RM_PART_NO where WH_CODE='0021'
)
select @count=count(RM_PART_NO) From CTE CTE inner join PRODUCT_MASTER pm (nolock) on CTE.RM_PART_NO=pm.SEWS_PART_NO inner join INV_SERIAL iv (nolock) on pm.ITEM_NO=iv.ITEM_NO where iv.serial_NO='BA00586147'  group by CTE.LEVEL  order by LEVEL  option(maxrecursion 0) 
if (@count=0)
begin
select 'FG & RM PART MISMATCH '
end




How do i find the performance of these two queries.

What I have tried:

When i execute the query execution plan,both query cost is 100% .
Posted
Updated 3-Mar-16 10:54am
Comments
dan!sh 29-Feb-16 0:23am    
Do you want to find the less performing query?
King Fisher 29-Feb-16 0:39am    
I want to find which performance is good,which i can use ?
Herman<T>.Instance 29-Feb-16 5:45am    
What is your definition of GOOD performance?
King Fisher 29-Feb-16 6:01am    
As Im thinking ,Which is taking less time to execute is good performance.
ZurdoDev 29-Feb-16 9:50am    
Looking at execution plan will show you the cost of your selects and joins so you need to look at the details of your plan.

Also, you can use the client statistics in query analyzer.

1 solution

Dear King,

In general: joins are always faster than subqueries.
A usage of CPU, HDD, RAM, etc. depends on many factors. Take a look here:
Chapter 14 - Improving SQL Server Performance[^]
7 performance tips for faster SQL queries | InfoWorld[^]

As i wrote in the comment to the question, there's much, much more...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900