sql - 填充 tempdb 的大型 SQL 查询需要优化

Question

我有一个相当大的 SQL 查询，其中大部分来自 Business Objects SQL 导出，必须在 SQL Server 上复制以用于报告目的。

该 View 现在已完成并正在运行，但是由于大量子句很快填满了 tempdb 并崩溃了。

据我所知，我需要 WHERE 子句中的所有内容，但是我觉得这可以优化为不填充 tempdb。

where子句如下:

WHERE

dbo.VIEW1.ABSENCE_TYPE  IN  ('leavetype1', 'leavetype2', 'leavetype3', 'leavetype4', 'leavetype5')

AND
  ( 
  (ALIAS1.link_type_id='PN_RP_UNIT' or ALIAS1.link_type_id IS NULL)  )
  AND  ( ALIAS2.OBJECT_TYPE='POSITION'  )
  AND  ( ALIAS3.object_type='UNIT'  )
  AND  ( CONTRACT_NAME.object_type='CONT'  )
  AND  ( (POST_LNK.link_type_id='POSITION' OR POST_LNK.link_type_id IS NULL)  )
  AND  ( dbo.table1.link_type_id = 'CONT'  )
  AND  ( dbo.table1.team_party_id IS NULL  )
  AND  ( EMP_TPERSON.non_emp_s='0'  )
  --AND  (( ( dbo.tleaver.starting_d ) <= @Prompt('Report To Date','D',,Mono,Free) AND (( dbo.tleaver.leaving_d ) >= @Prompt('Report From Date','D',,Mono,Free) OR ( dbo.tleaver.leaving_d ) is null)  )
  AND  dbo.VIEW4.UNIT_NM_01  =  'Orgainisation Name'

  AND  ALIAS2.PARTY_NM  NOT LIKE  '%occ1%'
  AND  ALIAS2.PARTY_NM  NOT LIKE  '%occ2%'
  AND  dbo.VIEW2.CONTRACTUAL_HOURS  !=  0
  AND  dbo.VIEW6.FTE_HOURS  !=  0
 AND  ( (ALIAS1.PARTY_LNK_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR ALIAS1.PARTY_LNK_D IS NULL) AND (ALIAS1.PARTY_LNK_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR ALIAS1.PARTY_LNK_ED IS NULL)  )
  AND  ( (( dbo.table1.PARTY_LNK_D ) <= ( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.table1.PARTY_LNK_D ) is null) AND (( dbo.table1.PARTY_LNK_ED ) >= ( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.table1.PARTY_LNK_ED ) is null)  )
  --AND  dbo.VIEW1.ABSENCE_START_DATE  <=  @variable('report to date')
  --AND  (dbo.VIEW1.ABSENCE_END_DATE  >=  @variable('report from date')
 -- OR   dbo.VIEW1.ABSENCE_END_DATE  IS NULL  )
  --AND  dbo.VIEW3.ABS_DAY_DATE  >=  @variable('report from date')
  --AND  dbo.VIEW3.ABS_DAY_DATE  <=  @variable('report to date')
  AND  ( (POST_LNK.PARTY_LNK_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR POST_LNK.PARTY_LNK_D is null) AND (POST_LNK.PARTY_LNK_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR POST_LNK.PARTY_LNK_ED is null)  )
  AND  ( (dbo.VIEW2.INHERITED_FROM_DATE<=( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW2.INHERITED_FROM_DATE IS NULL) AND (dbo.VIEW2.INHERITED_TO_DATE>=( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW2.INHERITED_TO_DATE IS NULL) AND (dbo.VIEW2.HOURS_START_DATE<=( dbo.VIEW3.ABS_DAY_DATE )  OR dbo.VIEW2.HOURS_START_DATE IS NULL) AND (dbo.VIEW2.HOURS_END_DATE>=( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW2.HOURS_END_DATE IS NULL)  )
  AND  ( (dbo.VIEW6.INHERITED_FROM_DATE<=( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW6.INHERITED_FROM_DATE IS NULL) AND (dbo.VIEW6.INHERITED_TO_DATE>=( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW6.INHERITED_TO_DATE IS NULL) AND (dbo.VIEW6.HOURS_START_DATE<=( dbo.VIEW3.ABS_DAY_DATE )  OR dbo.VIEW6.HOURS_START_DATE IS NULL) AND (dbo.VIEW6.HOURS_END_DATE>=( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW6.HOURS_END_DATE IS NULL)  )
  AND  ( (dbo.VIEW4.LNK_01_02_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_01_02_D IS NULL)  AND (dbo.VIEW4.LNK_02_03_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_02_03_D IS NULL)  AND (dbo.VIEW4.LNK_03_04_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_03_04_D IS NULL)  AND (dbo.VIEW4.LNK_04_05_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_04_05_D IS NULL)  AND (dbo.VIEW4.LNK_05_06_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_05_06_D IS NULL)  AND (dbo.VIEW4.LNK_06_07_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_06_07_D IS NULL)  AND (dbo.VIEW4.LNK_07_08_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_07_08_D IS NULL)  AND (dbo.VIEW4.LNK_08_09_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_08_09_D IS NULL)  AND (dbo.VIEW4.LNK_09_10_D <= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_09_10_D IS NULL)  AND (dbo.VIEW4.LNK_01_02_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_01_02_ED IS NULL)  AND (dbo.VIEW4.LNK_02_03_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_02_03_ED IS NULL)  AND (dbo.VIEW4.LNK_03_04_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_03_04_ED IS NULL)  AND (dbo.VIEW4.LNK_04_05_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_04_05_ED IS NULL)  AND (dbo.VIEW4.LNK_05_06_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_05_06_ED IS NULL)  AND (dbo.VIEW4.LNK_06_07_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_06_07_ED IS NULL)  AND (dbo.VIEW4.LNK_07_08_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_07_08_ED IS NULL)  AND (dbo.VIEW4.LNK_08_09_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_08_09_ED IS NULL)  AND (dbo.VIEW4.LNK_09_10_ED >= ( dbo.VIEW3.ABS_DAY_DATE ) OR dbo.VIEW4.LNK_09_10_ED IS NULL)  )

  AND  ALIAS2.PARTY_NM  NOT LIKE  'pos1'
  AND  dbo.VIEW5.contract_category  IN  ('pos2', 'pos3', 'pos4', 'pos4')
  AND  ( (( dbo.VIEW5.inherited_from_date )<=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW5.inherited_from_date ) IS NULL) AND (( dbo.VIEW5.inherited_to_date )>=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW5.inherited_to_date ) IS NULL) AND (( dbo.VIEW5.contract_category_d )<=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW5.contract_category_d ) IS NULL) AND (( dbo.VIEW5.contract_category_ed )>=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW5.contract_category_ed ) IS NULL)  )
  AND  ALIAS2.PARTY_NM  NOT LIKE  'role1'
  AND  ALIAS2.PARTY_NM  NOT LIKE  'role2'
  AND  ALIAS2.PARTY_NM  NOT LIKE  '%role3%'

  AND  ( (( dbo.VIEW4.inherited_from_date )<=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW4.inherited_from_date ) IS NULL) AND (( dbo.VIEW4.inherited_to_date )>=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW4.inherited_to_date ) IS NULL) AND (( dbo.VIEW4.contract_basis_d )<=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW4.contract_basis_d ) IS NULL) AND (( dbo.VIEW4.contract_basis_ed )>=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW4.contract_basis_ed ) IS NULL)  )
  AND  dbo.VIEW5.contract_category  IN  ('pos1', 'pos2', 'pos3', 'pos4')
  AND  ( (( dbo.VIEW5.inherited_from_date )<=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW5.inherited_from_date ) IS NULL) AND (( dbo.VIEW5.inherited_to_date )>=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW5.inherited_to_date ) IS NULL) AND (( dbo.VIEW5.contract_category_d )<=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW5.contract_category_d ) IS NULL) AND (( dbo.VIEW5.contract_category_ed )>=( dbo.VIEW3.ABS_DAY_DATE ) OR ( dbo.VIEW5.contract_category_ed ) IS NULL)

Onece 运行这应该带回大约 50,000 行数据，任何人都可以就如何停止对 tempdb 施加如此大的压力并运行此查询提出任何建议。

Answer 1

所有的结构都是这样的:

(POST_LNK.link_type_id='POSITION' OR POST_LNK.link_type_id IS NULL)

可以重构为

ISNULL(POST_LNK.link_type_id,'POSITION') = 'POSITION'

仅仅因为 where 子句是“减速”并不意味着没有可能发生在查询的其余部分的重构。通常可以修改连接和子查询以具有约束并大大提高速度，因为外部查询检查的记录较少。

另外，如果我要使用 WAG，我会说您的问题在于这样的 where 元素:

AND  ALIAS2.PARTY_NM  NOT LIKE  '%occ1%'
AND  ALIAS2.PARTY_NM  NOT LIKE  '%occ2%'
AND  ALIAS2.PARTY_NM  NOT LIKE  '%role3%'

如果是问题所在，您可以考虑重构以删除这部分(例如添加 occ bool 列并在插入和修改时更新它。)

我还注意到您正在加入许多 View ，如果 View 正在做一些愚蠢的事情，比如进行函数调用，这可能是问题所在。如果未使用列，SQL 可能会优化这些减慢速度，但如果您在 where 中使用该元素，请重新添加它们。