代码之家 › 专栏 › 技术社区 › Zaynul Abadin Tuhin

查询执行计划中的查询优化

tsql sql-server sql

Zaynul Abadin Tuhin · 技术社区 · 7 年前

除了图像,我还没有找到任何合适的方式来显示查询计划,所以我添加了图像。在图中,我得到了执行计划,我想降低fullouter连接成本

,如果有人建议我降低成本的方法,那就太好了 for better query plan link

WITH cte AS
(
SELECT
 coalesce(fact_connect_hours.dimProviderId,fact_connect_hour_hum_shifts.dimProviderId,fact_connect_hour_clock_times.dimProviderId)
  as dimProviderId,
  coalesce(fact_connect_hours.dimScribeId,fact_connect_hour_hum_shifts.dimScribeId,fact_connect_hour_clock_times.dimScribeId)
  as dimScribeId
 ,coalesce(fact_connect_hours.dimDateId,fact_connect_hour_hum_shifts.dimDateId,fact_connect_hour_clock_times.dimDateId)
  as dimDateId
,factConnectHourId
,totalProviderLogTime
,providerFirstJoinTime
,providerLastEndTime
,scribeFirstLogin
,scribeLastLogout
,totalScribeLogTime
, totalScopeTime
, totalStreamTime
, firstScopeJoinTime
, lastScopeEndTime
, scopeLastActivityTime
, firstStreamJoinTime
, lastStreamEndTime
, streamLastActivityTime

,fact_connect_hour_hum_shifts.shiftStartTime
,fact_connect_hour_hum_shifts.shiftEndTime
,fact_connect_hour_hum_shifts.totalShiftTime
,fact_connect_hour_clock_times.ClockStartTimestamp
,fact_connect_hour_clock_times.ClockEndTimestamp
,fact_connect_hour_clock_times.totalClockTime
,fact_connect_hour_hum_shifts.shiftTitle
,fact_connect_hours.dimStatusId
,dim_status.status
FROM fact_connect_hours
INNER JOIN  dim_status on fact_connect_hours.dimStatusId=dim_status.dimStatusId
  full outer JOIN fact_connect_hour_hum_shifts
 ON ( fact_connect_hour_hum_shifts.dimDateId=fact_connect_hours.dimDateId 
  and fact_connect_hour_hum_shifts.dimProviderId=fact_connect_hours.dimProviderId 
  and fact_connect_hour_hum_shifts.dimScribeId=fact_connect_hours.dimScribeId)
full outer join fact_connect_hour_clock_times
    on (fact_connect_hours.dimDateId = fact_connect_hour_clock_times.dimDateId 
        and fact_connect_hours.dimProviderId= fact_connect_hour_clock_times.dimProviderId 
        and fact_connect_hours.dimScribeId = fact_connect_hour_clock_times.dimScribeId
       )
WHERE coalesce(fact_connect_hours.dimDateId,fact_connect_hour_hum_shifts.dimDateId,fact_connect_hour_clock_times.dimDateId)>=732
) SELECT cte.*
  ,dim_date.tranDate
,dim_date.tranMonth
,dim_date.tranMonthName
,dim_date.tranYear
,dim_date.tranWeek
,dim_scribe.scribeUId
,dim_scribe.scribeFirstname
,dim_scribe.scribeFullname
,dim_scribe.scribeLastname
,dim_scribe.location
,dim_scribe.partner
,dim_scribe.beta
,dim_scribe.currentStatus
,dim_scribe.scribeEmail
,dim_scribe.augmedixEmail
,dim_scribe.partner
,dim_provider.scribeManager
,dim_provider.clinicalAccountManagerName
,dim_provider.providerUId
,dim_provider.beta
,dim_provider.accountName
,dim_provider.accountGroup
,dim_provider.accountType
,dim_provider.goLiveDate
,dim_provider.siteName
,dim_provider.churnDate
,dim_provider.providerFullname
,dim_provider.providerEmail

  from cte

   INNER JOIN   dim_date on cte.dimDateId=dim_date.dimDateId
   inner JOIN  aug_bi_dw.dbo.dim_provider AS dim_provider on cte.dimProviderId=dim_provider.dimProviderId 
   inner join aug_bi_dw.dbo.dim_scribe AS dim_scribe on cte.dimScribeId=dim_scribe.dimScribeId 

where dim_date.dimDateId>=732

0 回复 | 直到 7 年前

Conor Cunningham MSFT 7 年前

基于表名(dim*和fact*),我假设您正在数据仓库模式上进行排序报告。假设是这种情况,那么您可以做的最好的事情是提高性能的是考虑使用CurnSt店索引(和批处理模式执行,这是隐式的,一旦您启用CyrnSt店)。这些索引被严重压缩,通常会在受IO限制的工作负载上显著提高性能。事实表通常是候选的,因为它们最大/通常不适合缓冲池。

SQL 2016以后的所有版本都支持Columnstores,而Enterprise Edition则更快(更并行,更快的内部操作,如使用SIMD指令等)。请注意,它们不直接支持主键,因此这可能会稍微影响表的布局。您可以创建键(在内部作为b-树二级索引),因此如果使用主键,会损失一些节省的空间。通常情况下,事实表+列存储还使用分区来获得另一层过滤,而无需二级索引。

请考虑使用CalnnSt店替换事实表(可能在数据库的副本上进行实验)再次尝试查询。当您查看生成的查询计划时,我建议您也查看操作符是否以批处理模式运行。批处理模式运算符与行模式运算符不同。批处理模式针对现代CPU的体系结构进行了优化,以最小化CPU内外的内存流量。粗略地说,columnstores+批处理模式可能会产生10倍到100倍的差异。

Stefanos Zilellis 7 年前

唯一能帮你的过滤器是“where dim_date”。dimDateId>=X' 这就产生了一个到cte的连接,cte字段由三个表组成,它们相互连接。为了获得最佳性能,我会选择一步一步地告诉sql应该做什么,否则按照最佳计划执行是非常危险的:

使用过滤器在表fact_connect_hours、fact_connect_hour_hum_shifts和fact_connect_hour_clock_times中使用3条语句,并将结果(仅主键或所需的所有列)分成3个时间段,如#fact_connect_hour hum_shifts和#fact_connect_hour clock_clock_times
按原样使用语句,但替换为temp,或者如果temp只有PK,则使用temp join real tables
将索引(如果尚未存在)添加到“事实连接”列。dimDateId,事实连接,小时轮班。dimDateId和事实连接时间。dimDateId

通过这种方式,您可以确保在尽可能简单的步骤中直接过滤所需的内容,然后复杂的查询将在预设的行数下工作,因此性能是有保证的,因为应用于几行的非常好或非常差的计划实际上并不重要。

较小的细节:注意“内部连接dim_status”——如果没有FK约束,基数估计器可能会错过估计的返回行,因为无法理解表之间的关系。

我还可以看到优化的尝试,因为过滤器已经上升到cte中。这是一个类似于我提议的计划,但限制较小。使用我的计划将强制对核心根源执行行搜索。