我如何分析查询性能？ [英] How do i analyse query performance ?

问题描述

子查询：

 声明  @ count  int = 0; 
;   CTE（RM_PART_NO，FG_PART_NO，LEVEL）
  as  
（
 选择 RM_PART_NO，FG_PART_NO， 0   as  LEVEL 来自 BOM_MASTER（ nolock ）其中 FG_PART_NO = '  29049260' 和 WH_CODE = '  0021' 
  union  所有 
 选择 BOM.RM_PART_NO，CTE.RM_PART_NO，LEVEL + 1 < span class =code-keyword> from  CTE  as  CTE  inner   join  BOM_MASTER BOM（ nolock ）  BOM.FG_PART_NO = CTE.RM_PART_NO 其中 WH_CODE = '  0021' 
）
  select  @count = count（RM_PART_NO）来自 CTE 其中 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 订单 按 LEVEL 选项（maxrecursion  0 ）
  if （@ count = 0）
 开始 
 选择 '  FG& RM PART MISMATCH' 
  end  

加入：

 声明 < span class =code-sdkkeyword> @ count  int = 0; 
;   CTE（RM_PART_NO，FG_PART_NO，LEVEL）
  as  
（
 选择 RM_PART_NO，FG_PART_NO， 0   as  LEVEL 来自 BOM_MASTER（ nolock ）其中 FG_PART_NO = '  29049260' 和 WH_CODE = '  0021' 
  union  所有 
 选择 BOM.RM_PART_NO，CTE.RM_PART_NO，LEVEL + 1 < span class =code-keyword> from  CTE  as  CTE  inner   join  BOM_MASTER BOM（ nolock ）  BOM.FG_PART_NO = CTE.RM_PART_NO 其中 WH_CODE = '  0021' 
）
  select  @count = count（RM_PART_NO）来自 CTE CTE 内部  join  PRODUCT_MASTER pm（ nolock ）  CTE.RM_PART_NO = pm.SEWS_PART_NO 内部  join  INV_SERIAL iv（ nolock ） on  pm.ITEM_NO = iv.ITEM_NO 其中 iv.serial_NO = '  BA00586147'  group   by  CTE.LEVEL  order   by  LEVEL 选项（maxrecursion  0 ）
  if （@ count = 0）
 开始 
 选择 '  FG& RM PART MISMATCH' 
  end  

我如何找到这两个查询的表现。



我试过的：



当我执行查询执行计划时，两个查询成本都是100％。

解决方案

亲爱的国王，



一般来说：连接总是比子查询更快。

CPU，HDD，RAM等的使用取决于很多因素。看看这里：

第14章 - 提高SQL Server性能 [ ^ ]

有关更快SQL查询的7个性能提示InfoWorld [ ^ ]



正如我在问题评论中写的那样，还有很多......

subquery :

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 :

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

解决方案

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

这篇关于我如何分析查询性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！