使用CTE和PIVOT进行SQL优化 [英] Sql optimization with CTE and PIVOT

问题描述

任何人都可以帮助我如何在下面优化我的查询。

显示50K记录只需要一分多钟。

TIA。



我的尝试：



这是我的查询。

Can anyone help me on how can i optimize my query below.
It takes more than a minute just to show 50K records.
TIA.

What I have tried:

This is my query.

with cte as
(
 select batch,lcoDt,runID,erpIO,style,article,leadtm,cfm_tag,EETA, asIO,planDt, class, pono,mainFab,matLeadTm,ExMillDt--, confirm_tag,special_range, order_qty, lpd_dt,req_del_cst
 ,ROW_NUMBER() over (partition by pono order by class) as seq
	from  (
			select distinct batch,lcoDt,runID,erpIO,style,article,leadtm,cfm_tag, 
				(case when isnull(EETA,'')<>'' and isnull(ETA,'')<>'' then ETA else EETA end) 'EETA'
				,asIO,planDt, class,pono,mainFab,matLeadTm,ExMillDt,-- confirm_tag,special_range, order_qty, lpd_dt,req_del_cst,
				 ROW_NUMBER() over (partition by poNo,class order by eeta desc) as rowNumber
			from TableMatTrack
			where class != 'TRI'
		  ) as maxDateResult
	where maxDateResult.rowNumber = 1
)
select batch 'Batch',
        lcoDt 'LCO Date', 
		runID 'Run ID', 
		asio 'AS400 IO#', 
		erpIO 'ERP IO#', 
		style 'Style ID', 
		article 'Article',
		leadtm 'Gmt LT', 
		pono 'Cust PONo',
		confirm_tag,
		special_range,
		order_qty,
		lpd_dt,
		req_del_cst,
		(dateadd(d,3,planDt)) 'PSDD Dt',
		class1_process, class1, class1_mainfab, class1_mat,class1_exMill,class1_eeta,
		class2_process, class2, class2_mainfab, class2_mat, class2_exMill,class2_eeta,
		class3_process, class3, class3_mainfab, class3_mat, class3_exMill,class3_eeta
  from
(
  select t.batch,t.lcoDt,t.runID,t.asIO,t.erpIO,t.style,t.article,t.leadtm, t.pono,t.cfm_tag, t.planDt,poDet.confirm_tag,
		poDet.special_range,
		poDet.order_qty,
		poDet.lpd_dt,
		poDet.req_del_cst,
    col = case 
     when c.col = 'class' then col+cast(seq as varchar(10))
      else 'class'+cast(seq as varchar(10))+'_'+col
    end,

    value
  from cte t left join 
				(select distinct confirm_tag,special_range, order_qty, lpd_dt,req_del_cst,order_no,IO_no
				 from TableBulkPO
				 where Approve_tag=1 and upload_tag=1) as poDet
						on poDet.order_no= t.pono and poDet.IO_no= t.asIO
  cross apply
 (
    select 'process',  case when class = 'ACC' then 'SEW' 
							when class = 'FAB' then 'CUT'
							else 'FN/PK' end 'Process' union all

	select 'class', class union all

	select 'mainfab',  case when isnull(mainFab,'') <> '' then mainFab else 'N' end union all

	select 'mat', matLeadTm union all

	select 'exMill', case when isnull(ExMillDt,'') <> '' then convert(varchar(20),ExMillDt,101) else '' end  as  ExMillDt union all

	select 'eeta', case when isnull(EETA,'') <> '' then convert(varchar(20),EETA,101) else '' end  as  EETA
	
  ) c (col, value)
) d  
pivot
(
  max(value)
  for col in (class1_process, class1, class1_mainfab, class1_mat,class1_exMill,class1_eeta,
		class2_process, class2, class2_mainfab, class2_mat, class2_exMill,class2_eeta,
		class3_process, class3, class3_mainfab, class3_mat, class3_exMill,class3_eeta)
) piv;

推荐答案

没有整个架构和数据提供确切的解决方案是不可能的，但有一些建议：

- 使用Management工作室查看执行计划并调查是否使用了正确的索引

- 另外看看是否缺少索引列出

- 你有很多内联视图。 SQL Server在优化它们时遇到一些困难，因此请尝试重新排列SQL

- 在查询中为所有列使用别名

- 确保TableBulkPO中的Approve_tag和upload_tag被编入索引值1表示表格的一小部分

- 如果TableMatTrack中的大多数记录具有值'TRI'

Without having the whole schema and the data it's impossible to give exact solutions, but some advice:
- Use the Management studio to see the execution plan and investigate if proper indexes are used
- Also have a look if missing indexes are listed
- You have quite a lot of inline view. SQL Server has some difficulties optimizing them so try rearranging the SQL
- Use aliases in the query for all columns
- Ensure that Approve_tag and upload_tag in TableBulkPO are indexed if values of 1 represent small portion of the table
- Ensure that class is indexed if majority of the records in TableMatTrack have a value 'TRI'

这篇关于使用CTE和PIVOT进行SQL优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！