文章/答案/技术大牛

发布

社区首页 >问答首页 >视图不执行，但直接运行相同的查询

问视图不执行，但直接运行相同的查询
EN

Stack Overflow用户

提问于 2020-01-08 13:30:57

回答 2查看 111关注 0票数 0

对于PowerBI的使用，我们在最大的表上有一个视图。但是，它将不会运行，使用PAGEIOLATCH_SH进行超时。查询非常简单，如：

select * from view where ProgramId = 'ParticularProgram'

但是，如果我们在视图中运行查询，它可以正常工作，不会出现任何问题或超时。你知道是什么导致的吗？视图中的原始查询是：

select column 1, column 2 etc from table where ProgramId = 'ParticularProgram'

使用分区指定where子句不会改变行为并使其保持缓慢。当两者一起运行时，第一个查询的代价是99%。ProgramId在表中进行分区，而这个特定的程序是该分区的一部分。

事先非常感谢

请在附件中找到正在工作的查询的实际查询计划：DirectQueryOnTable、XML DirextQueryExecutionPlanXML和相应的ddl：

/****** Object:  Table [dm].[AggProgramPeriodLoyalty]    Script Date: 1/8/2020 3:47:03 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dm].[AggProgramPeriodLoyalty](
    [AggProgramPeriodLoyaltyKey] [int] IDENTITY(1,1) NOT NULL,
    [ProgramId] [nvarchar](100) NOT NULL,
    [ProgramTitle] [nvarchar](255) NOT NULL,
    [ProgramCurrency] [nvarchar](3) NULL,
    [ProgramPeriodType] [nvarchar](50) NOT NULL,
    [ProgramPeriodTypeLabel] [nvarchar](50) NULL,
    [IsCurrentProgramPeriod] [tinyint] NOT NULL,
    [IsLastCompletedProgramPeriod] [tinyint] NOT NULL,
    [LoyaltyId] [nvarchar](100) NOT NULL,
    [IsRegistered] [int] NOT NULL,
    [IsRegisteredCml] [int] NOT NULL,
    [UsedPlatforms] [nvarchar](100) NULL,
    [DigitalStampsBalanceMutation] [int] NOT NULL,
    [DigitalStampsBalance] [int] NOT NULL,
    [DigitalRedeemPotential] [int] NOT NULL,
    [DigitalParticipationBonusInteractions] [int] NOT NULL,
    [DigitalParticipationBonusInteractionsCml] [int] NOT NULL,
    [DigitalParticipationBonusStamps] [int] NOT NULL,
    [DigitalParticipationBonusStampsCml] [int] NOT NULL,
    [DigitalCollectInteractions] [int] NOT NULL,
    [DigitalCollectInteractionsCml] [int] NOT NULL,
    [IsNewDigitalCollector] [int] NOT NULL,
    [IsDigitalCollector] [int] NOT NULL,
    [IsDigitalCollectorCml] [int] NOT NULL,
    [DigitalCollectedStamps] [int] NOT NULL,
    [DigitalCollectedStampsCml] [int] NOT NULL,
    [TurnoverByDigitalCollectors] [decimal](18, 5) NOT NULL,
    [TurnoverByDigitalCollectorsCml] [decimal](18, 5) NOT NULL,
    [DigitalRedeemInteractions] [int] NOT NULL,
    [DigitalRedeemInteractionsCml] [int] NOT NULL,
    [IsNewDigitalRedeemer] [int] NOT NULL,
    [IsDigitalRedeemer] [int] NOT NULL,
    [IsDigitalRedeemerCml] [int] NOT NULL,
    [DigitalRedeemedStamps] [int] NOT NULL,
    [DigitalRedeemedStampsCml] [int] NOT NULL,
    [DigitalRewards] [int] NOT NULL,
    [DigitalRewardsCml] [int] NOT NULL,
    [TurnoverByDigitalRedeemers] [decimal](18, 5) NOT NULL,
    [TurnoverByDigitalRedeemersCml] [decimal](18, 5) NOT NULL,
    [AdditionalPaymentForDigitalRewards] [decimal](18, 5) NOT NULL,
    [AdditionalPaymentForDigitalRewardsCml] [decimal](18, 5) NOT NULL,
    [DigitalCounterRedeemInteractions] [int] NOT NULL,
    [DigitalCounterRedeemInteractionsCml] [int] NOT NULL,
    [DigitalConvertInteractions] [int] NOT NULL,
    [DigitalConvertInteractionsCml] [int] NOT NULL,
    [DigitalConvertedStamps] [int] NOT NULL,
    [DigitalConvertedStampsCml] [int] NOT NULL,
    [DigitalAddInteractions] [int] NOT NULL,
    [DigitalAddInteractionsCml] [int] NOT NULL,
    [DigitalAddedStamps] [int] NOT NULL,
    [DigitalAddedStampsCml] [int] NOT NULL,
    [DigitalDeductInteractions] [int] NOT NULL,
    [DigitalDeductInteractionsCml] [int] NOT NULL,
    [DigitalDeductedStamps] [int] NOT NULL,
    [DigitalDeductedStampsCml] [int] NOT NULL,
    [DigitalTransferToInteractions] [int] NOT NULL,
    [DigitalTransferToInteractionsCml] [int] NOT NULL,
    [DigitalTransferedToStamps] [int] NOT NULL,
    [DigitalTransferedToStampsCml] [int] NOT NULL,
    [DigitalTransferFromInteractions] [int] NOT NULL,
    [DigitalTransferFromInteractionsCml] [int] NOT NULL,
    [DigitalTransferedFromStamps] [int] NOT NULL,
    [DigitalTransferedFromStampsCml] [int] NOT NULL,
    [DigitalGiftCreatedInteractions] [int] NOT NULL,
    [DigitalGiftCreatedInteractionsCml] [int] NOT NULL,
    [DigitalGiftCreatedStamps] [int] NOT NULL,
    [DigitalGiftCreatedStampsCml] [int] NOT NULL,
    [DigitalGiftRedeemedInteractions] [int] NOT NULL,
    [DigitalGiftRedeemedInteractionsCml] [int] NOT NULL,
    [DigitalGiftRedeemedStamps] [int] NOT NULL,
    [DigitalGiftRedeemedStampsCml] [int] NOT NULL,
    [DigitalGiftExpiredInteractions] [int] NOT NULL,
    [DigitalGiftExpiredInteractionsCml] [int] NOT NULL,
    [DigitalGiftExpiredStamps] [int] NOT NULL,
    [DigitalGiftExpiredStampsCml] [int] NOT NULL,
    [DigitalGiftCancelledInteractions] [int] NOT NULL,
    [DigitalGiftCancelledInteractionsCml] [int] NOT NULL,
    [DigitalGiftCancelledStamps] [int] NOT NULL,
    [DigitalGiftCancelledStampsCml] [int] NOT NULL,
    [CreateDTS] [datetime2](3) NOT NULL,
    [CreateExnId] [int] NOT NULL,
    [UpdateDTS] [datetime2](3) NULL,
    [UpdateExnId] [int] NULL,
    [IsProfileCompleted] [int] NULL,
    [IsProfileCompletedCml] [int] NULL,
    [DigitalRedeemBonusInteractions] [int] NULL,
    [DigitalRedeemBonusInteractionsCml] [int] NULL,
    [DigitalRedeemBonusStamps] [int] NULL,
    [DigitalRedeemBonusStampsCml] [int] NULL,
    [DigitalCounterCollectInteractions] [int] NULL,
    [DigitalCounterCollectInteractionsCml] [int] NULL,
    [DigitalCounterRedeemBonusInteractions] [int] NULL,
    [DigitalCounterRedeemBonusInteractionsCml] [int] NULL,
    [ProgramWeekCombination] [nvarchar](100) NULL,
    [TotalUsedPlatforms] [nvarchar](100) NULL,
 CONSTRAINT [PK_AggProgramPeriodLoyalty] PRIMARY KEY CLUSTERED 
(
    [AggProgramPeriodLoyaltyKey] ASC,
    [ProgramId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PS_ByProgramId]([ProgramId]),
 CONSTRAINT [UK_AggProgramPeriodLoyalty] UNIQUE NONCLUSTERED 
(
    [ProgramId] ASC,
    [ProgramPeriodType] ASC,
    [LoyaltyId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PS_ByProgramId]([ProgramId])
) ON [PS_ByProgramId]([ProgramId])
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCreatedInteractions]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCreatedInteractionsCml]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCreatedStamps]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCreatedStampsCml]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftRedeemedInteractions]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftRedeemedInteractionsCml]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftRedeemedStamps]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftRedeemedStampsCml]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftExpiredInteractions]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftExpiredInteractionsCml]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftExpiredStamps]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftExpiredStampsCml]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCancelledInteractions]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCancelledInteractionsCml]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCancelledStamps]
GO

ALTER TABLE [dm].[AggProgramPeriodLoyalty] ADD  DEFAULT ((0)) FOR [DigitalGiftCancelledStampsCml]
GO

和视图上select语句的估计查询计划，它根本不执行：QueryOnView，XML ViewExecutionPlanXML

而DDL视图：

CREATE view [dm].[vwAggProgramPeriodLoyalty]
as
select [AggProgramPeriodLoyaltyKey] as [Agg Program Period Loyalty Key]
     , upper([ProgramId]) as [Program Id]
     , [ProgramTitle] as [Program Title]
     , [ProgramCurrency] as [Program Currency]
     , [ProgramPeriodType] as [Program Period Type]
     , [ProgramPeriodTypeLabel] as [Program Period Type Label]
     , upper([ProgramWeekCombination]) as [Program Week Combination] 
     , [IsCurrentProgramPeriod] as [Is Current Program Period]
     , [IsLastCompletedProgramPeriod] as [Is Last Completed Program Period]
     , [LoyaltyId] as [Loyalty Id]
     , [IsRegistered] as [Is Registered]
     , [IsRegisteredCml] as [Is Registered Cumulative]
     , [UsedPlatforms] as [Used Platforms]
     , [DigitalStampsBalanceMutation] as [Digital Stamps Balance Mutation]
     , [DigitalStampsBalance] as [Digital Stamps Balance]
     , [DigitalRedeemPotential] as [Digital Redeem Potential]
     , [DigitalParticipationBonusInteractions] as [Digital Participation Bonus Interactions]
     , [DigitalParticipationBonusInteractionsCml] as [Digital Participation Bonus Interactions Cumulative]
     , [DigitalParticipationBonusStamps] as [Digital Participation Bonus Stamps]
     , [DigitalParticipationBonusStampsCml] as [Digital Participation Bonus Stamps Cumulative]
     , [DigitalCollectInteractions] as [Digital Collect Interactions]
     , [DigitalCollectInteractionsCml] as [Digital Collect Interactions Cumulative]
     , [IsNewDigitalCollector] as [Is First Digital Collect]
     , [IsDigitalCollector] as [Is Digital Collector]
     , [IsDigitalCollectorCml] as [Is Digital Collector Cumulative]
     , [DigitalCollectedStamps] as [Digital Collected Stamps]
     , [DigitalCollectedStampsCml] as [Digital Collected Stamps Cumulative]
     , [TurnoverByDigitalCollectors] as [Turnover By Digital Collectors]
     , [TurnoverByDigitalCollectorsCml] as [Turnover By Digital Collectors Cumulative]
     , [DigitalRedeemInteractions] as [Digital Redeem Interactions]
     , [DigitalRedeemInteractionsCml] as [Digital Redeem Interactions Cumulative]
     , [IsNewDigitalRedeemer] as [Is First Digital Redeem]
     , [IsDigitalRedeemer] as [Is Digital Redeemer]
     , [IsDigitalRedeemerCml] as [Is Digital Redeemer Cumulative]
     , [DigitalRedeemedStamps] as [Digital Redeemed Stamps]
     , [DigitalRedeemedStampsCml] as [Digital Redeemed Stamps Cumulative]
     , [DigitalRewards] as [Digital Rewards]
     , [DigitalRewardsCml] as [Digital Rewards Cumulative]
     , [TurnoverByDigitalRedeemers] as [Turnover By Digital Redeemers]
     , [TurnoverByDigitalRedeemersCml] as [Turnover By Digital Redeemers Cumulative]
     , [AdditionalPaymentForDigitalRewards] as [Additional Payment For Digital Rewards]
     , [AdditionalPaymentForDigitalRewardsCml] as [Additional Payment For Digital Rewards Cumulative]
     , [DigitalConvertInteractions] as [Digital Convert Interactions]
     , [DigitalConvertInteractionsCml] as [Digital Convert Interactions Cumulative]
     , [DigitalConvertedStamps] as [Digital Converted Stamps]
     , [DigitalConvertedStampsCml] as [Digital Converted Stamps Cumulative]
     , [DigitalAddInteractions] as [Digital Add Interactions]
     , [DigitalAddInteractionsCml] as [Digital Add Interactions Cumulative]
     , [DigitalAddedStamps] as [Digital Added Stamps]
     , [DigitalAddedStampsCml] as [Digital Added Stamps Cumulative]
     , [DigitalDeductInteractions] as [Digital Deduct Interactions]
     , [DigitalDeductInteractionsCml] as [Digital Deduct Interactions Cumulative]
     , [DigitalDeductedStamps] as [Digital Deducted Stamps]
     , [DigitalDeductedStampsCml] as [Digital Deducted Stamps Cumulative]
     , [DigitalTransferToInteractions] as [Digital Transfer To Interactions]
     , [DigitalTransferToInteractionsCml] as [Digital Transfer To Interactions Cumulative]
     , [DigitalTransferedToStamps] as [Digital Transfered To Stamps]
     , [DigitalTransferedToStampsCml] as [Digital Transfered To Stamps Cumulative]
     , [DigitalTransferFromInteractions] as [Digital Transfer From Interactions]
     , [DigitalTransferFromInteractionsCml] as [Digital Transfer From Interactions Cumulative]
     , [DigitalTransferedFromStamps] as [Digital Transfered From Stamps]
     , [DigitalTransferedFromStampsCml] as [Digital Transfered From Stamps Cumulative]
     , [DigitalGiftCreatedInteractions] as [Digital Gift Created Interactions]            
     , [DigitalGiftCreatedInteractionsCml] as [Digital Gift Created Interactions Cumulative]    
     , [DigitalGiftCreatedStamps] as [Digital Gift Created Stamps]             
     , [DigitalGiftCreatedStampsCml] as [Digital Gift Created Stamps Cumulative]            
     , [DigitalGiftRedeemedInteractions] as [Digital Gift Redeemed Interactions]      
     , [DigitalGiftRedeemedInteractionsCml]  as [Digital Gift Redeemed Interactions Cumulative]     
     , [DigitalGiftRedeemedStamps] as [Digital Gift Redeemed Stamps]            
     , [DigitalGiftRedeemedStampsCml] as [Digital Gift Redeemed Stamps Cumulative]      
     , [DigitalGiftExpiredInteractions] as [Digital Gift Expired Interactions]          
     , [DigitalGiftExpiredInteractionsCml] as [Digital Gift Expired Interactions Cumulative]  
     , [DigitalGiftExpiredStamps] as [Digital Gift Expired Stamps]              
     , [DigitalGiftExpiredStampsCml] as [Digital Gift Expired Stamps Cumulative]        
     , [DigitalGiftCancelledInteractions] as [Digital Gift Cancelled Interactions]     
     , [DigitalGiftCancelledInteractionsCml] as [Digital Gift Cancelled Interactions Cumulative]    
     , [DigitalGiftCancelledStamps] as [Digital Gift Cancelled Stamps]           
     , [DigitalGiftCancelledStampsCml] as [Digital Gift Cancelled Stamps Cumulative]
  from [dm].[AggProgramPeriodLoyalty];

此外，我还添加了超时的概述：LogOfWaitType。

sql-server

performance

azure-sql-database

sql

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-01-09 14:26:11

视图首先扫描基表，然后应用由分区键组成的过滤器。因此，极致的性能差异。执行计划完全不同。由于您没有在视图中执行任何操作，并且只选择了一些基本表字段，所以我建议直接删除该视图并使用该表。另一种选择是TVF：

    CREATE FUNCTION dbo.fn_MyWorkaround(@ProgramId VARCHAR(50))
    RETURNS TABLE
    RETURN(
        select [AggProgramPeriodLoyaltyKey] as [Agg Program Period Loyalty Key]
     , upper([ProgramId]) as [Program Id]
     , [ProgramTitle] as [Program Title]
     , [ProgramCurrency] as [Program Currency]
     , [ProgramPeriodType] as [Program Period Type]
     , [ProgramPeriodTypeLabel] as [Program Period Type Label]
     , upper([ProgramWeekCombination]) as [Program Week Combination] 
     , [IsCurrentProgramPeriod] as [Is Current Program Period]
     , [IsLastCompletedProgramPeriod] as [Is Last Completed Program Period]
     , [LoyaltyId] as [Loyalty Id]
     , [IsRegistered] as [Is Registered]
     , [IsRegisteredCml] as [Is Registered Cumulative]
     , [UsedPlatforms] as [Used Platforms]
     , [DigitalStampsBalanceMutation] as [Digital Stamps Balance Mutation]
     , [DigitalStampsBalance] as [Digital Stamps Balance]
     , [DigitalRedeemPotential] as [Digital Redeem Potential]
     , [DigitalParticipationBonusInteractions] as [Digital Participation Bonus Interactions]
     , [DigitalParticipationBonusInteractionsCml] as [Digital Participation Bonus Interactions Cumulative]
     , [DigitalParticipationBonusStamps] as [Digital Participation Bonus Stamps]
     , [DigitalParticipationBonusStampsCml] as [Digital Participation Bonus Stamps Cumulative]
     , [DigitalCollectInteractions] as [Digital Collect Interactions]
     , [DigitalCollectInteractionsCml] as [Digital Collect Interactions Cumulative]
     , [IsNewDigitalCollector] as [Is First Digital Collect]
     , [IsDigitalCollector] as [Is Digital Collector]
     , [IsDigitalCollectorCml] as [Is Digital Collector Cumulative]
     , [DigitalCollectedStamps] as [Digital Collected Stamps]
     , [DigitalCollectedStampsCml] as [Digital Collected Stamps Cumulative]
     , [TurnoverByDigitalCollectors] as [Turnover By Digital Collectors]
     , [TurnoverByDigitalCollectorsCml] as [Turnover By Digital Collectors Cumulative]
     , [DigitalRedeemInteractions] as [Digital Redeem Interactions]
     , [DigitalRedeemInteractionsCml] as [Digital Redeem Interactions Cumulative]
     , [IsNewDigitalRedeemer] as [Is First Digital Redeem]
     , [IsDigitalRedeemer] as [Is Digital Redeemer]
     , [IsDigitalRedeemerCml] as [Is Digital Redeemer Cumulative]
     , [DigitalRedeemedStamps] as [Digital Redeemed Stamps]
     , [DigitalRedeemedStampsCml] as [Digital Redeemed Stamps Cumulative]
     , [DigitalRewards] as [Digital Rewards]
     , [DigitalRewardsCml] as [Digital Rewards Cumulative]
     , [TurnoverByDigitalRedeemers] as [Turnover By Digital Redeemers]
     , [TurnoverByDigitalRedeemersCml] as [Turnover By Digital Redeemers Cumulative]
     , [AdditionalPaymentForDigitalRewards] as [Additional Payment For Digital Rewards]
     , [AdditionalPaymentForDigitalRewardsCml] as [Additional Payment For Digital Rewards Cumulative]
     , [DigitalConvertInteractions] as [Digital Convert Interactions]
     , [DigitalConvertInteractionsCml] as [Digital Convert Interactions Cumulative]
     , [DigitalConvertedStamps] as [Digital Converted Stamps]
     , [DigitalConvertedStampsCml] as [Digital Converted Stamps Cumulative]
     , [DigitalAddInteractions] as [Digital Add Interactions]
     , [DigitalAddInteractionsCml] as [Digital Add Interactions Cumulative]
     , [DigitalAddedStamps] as [Digital Added Stamps]
     , [DigitalAddedStampsCml] as [Digital Added Stamps Cumulative]
     , [DigitalDeductInteractions] as [Digital Deduct Interactions]
     , [DigitalDeductInteractionsCml] as [Digital Deduct Interactions Cumulative]
     , [DigitalDeductedStamps] as [Digital Deducted Stamps]
     , [DigitalDeductedStampsCml] as [Digital Deducted Stamps Cumulative]
     , [DigitalTransferToInteractions] as [Digital Transfer To Interactions]
     , [DigitalTransferToInteractionsCml] as [Digital Transfer To Interactions Cumulative]
     , [DigitalTransferedToStamps] as [Digital Transfered To Stamps]
     , [DigitalTransferedToStampsCml] as [Digital Transfered To Stamps Cumulative]
     , [DigitalTransferFromInteractions] as [Digital Transfer From Interactions]
     , [DigitalTransferFromInteractionsCml] as [Digital Transfer From Interactions Cumulative]
     , [DigitalTransferedFromStamps] as [Digital Transfered From Stamps]
     , [DigitalTransferedFromStampsCml] as [Digital Transfered From Stamps Cumulative]
     , [DigitalGiftCreatedInteractions] as [Digital Gift Created Interactions]            
     , [DigitalGiftCreatedInteractionsCml] as [Digital Gift Created Interactions Cumulative]    
     , [DigitalGiftCreatedStamps] as [Digital Gift Created Stamps]             
     , [DigitalGiftCreatedStampsCml] as [Digital Gift Created Stamps Cumulative]            
     , [DigitalGiftRedeemedInteractions] as [Digital Gift Redeemed Interactions]      
     , [DigitalGiftRedeemedInteractionsCml]  as [Digital Gift Redeemed Interactions Cumulative]     
     , [DigitalGiftRedeemedStamps] as [Digital Gift Redeemed Stamps]            
     , [DigitalGiftRedeemedStampsCml] as [Digital Gift Redeemed Stamps Cumulative]      
     , [DigitalGiftExpiredInteractions] as [Digital Gift Expired Interactions]          
     , [DigitalGiftExpiredInteractionsCml] as [Digital Gift Expired Interactions Cumulative]  
     , [DigitalGiftExpiredStamps] as [Digital Gift Expired Stamps]              
     , [DigitalGiftExpiredStampsCml] as [Digital Gift Expired Stamps Cumulative]        
     , [DigitalGiftCancelledInteractions] as [Digital Gift Cancelled Interactions]     
     , [DigitalGiftCancelledInteractionsCml] as [Digital Gift Cancelled Interactions Cumulative]    
     , [DigitalGiftCancelledStamps] as [Digital Gift Cancelled Stamps]           
     , [DigitalGiftCancelledStampsCml] as [Digital Gift Cancelled Stamps Cumulative]
  from [dm].[AggProgramPeriodLoyalty]
        WHERE ProgramId = @ProgramId
    )
    GO

然后使用以下方法运行它：

SELECT *
FROM dbo.fn_MyWorkaround('ParticularProgram')

这将给您相同的执行时间。

票数 1

Stack Overflow用户

发布于 2020-01-09 15:00:19

我能看到两个问题：

在ProgramId上没有索引，所以即使是表查询也很慢。
视图查询实际上等同于...where upper(ProgramId) = 'ParticularProgram'，因此即使存在ProgramId上的索引，它也不能直接使用。另见：易懂。

建议：

索引ProgramId (这也会加快表查询的速度)。
修改视图以包含ProgramId AS [Program Id Original]，然后对其进行查询(假设在使用区分大小写的排序规则时实际上不需要区分大小写)： select * from view where [Program Id Original] = 'ParticularProgram'

或者，在upper(ProgramId)上创建一个函数索引。

另外，研究如何使用不区分大小写的排序规则，这样就不需要在WHERE子句中使用upper了。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/59646980

复制

相似问题

问视图不执行，但直接运行相同的查询
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问视图不执行，但直接运行相同的查询EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问视图不执行，但直接运行相同的查询
EN