优化SQL Server查询速度
如何优化我的SQL Server查询?下面是我想优化优化SQL Server查询速度
CREATE TABLE #Temp
(
TransactionId int PRIMARY KEY,
TransactionStepId int
)
INSERT INTO #Temp(TransactionId, TransactionStepId)
SELECT
TransactionId, MAX(TransactionStepId) TransactionStepId
FROM
[WarehouseMgmt].[FactPaymentTrans] FPT
JOIN
WarehouseMgmt.DimTimeZone DTZ on FPT.[TimeId] = DTZ.TimeUTCId
WHERE
FactType = 'SOURCE'
AND (DTZ.TimeId BETWEEN @DimStartDate AND @DimEndDate)
GROUP BY
TransactionId
IF(UPPER(@ReportBy) = 'TRANSACTION')
BEGIN
SET @sql = '
INSERT INTO #Results (
[PaymentTypeId],
[TransactionDate],
[PaymentMethodId],
[3DSecureId],
[ProductId],
[ProductTypeId],
[TransactionStatusId],
[Amount],
[Currency],
[PlayerId],
[PlayerSourceOrigId],
[Username],
[FirstName],
[LastName],
[BrandId],
[VIPLevelId],
[MarketingChannelId],
[MarketingSourceId],
[CommentId],
[CommentRefId],
[AdminName],
[OriginalTransactionId],
[TransactionId],
[RelatedTransactionId],
[ProviderTransactionOrigId]
)
SELECT
DTST.[Id],
FPT.[StartTime],
FPT.[PaymentMethodId],
FPT.[3DSecureId],
FPT.[ProductId],
DPT.[Id],
FPT.[TransactionStatusId],
FPT.[Amount],
FPT.CurrencyId,
FPT.[PlayerId],
DPL.[SourceOrigId],
DPL.[Username],
DPL.[FirstName],
DPL.[LastName],
DPL.[BrandId],
DPL.[VIPLevelId],
DPL.[MarketingChannelId],
DPL.[MarketingSourceId],
FPT.[PaymentReasonTextId],
FPT_Ref.[PaymentReasonTextId],
FPT.CreatedByAdminId,
FPT.[OriginalTransactionId],
FPT.[TransactionId],
FPT_Ref.[OriginalTransactionId],
FPT.[ProviderTransactionOrigId]
FROM WarehouseMgmt.FactPaymentTrans AS FPT
JOIN #Temp T ON FPT.TransactionId = T.TransactionId AND FPT.TransactionStepId = T.TransactionStepId
JOIN WarehouseMgmt.DimTransactionStepType AS DTST ON FPT.[TransactionStepTypeId] = DTST.[Id]
JOIN WarehouseMgmt.DimPlayer AS DPL ON FPT.PlayerId = DPL.Id
JOIN WarehouseMgmt.DimProduct AS DP ON DP.Id = FPT.ProductId
JOIN WarehouseMgmt.DimProductType AS DPT ON DPT.Id = DP.ProductTypeId
--JOIN WarehouseMgmt.DimTimeZone DTZ on FPT.[TimeId] = DTZ.TimeUTCId
JOIN [WarehouseMgmt].[DimLoyaltyProgramLevel] DLPL ON DLPL.[Id]=DPL.VipLevelId
LEFT JOIN
(
SELECT FPT_Ref_1.TransactionId,FPT_Ref_1.FactType,FPT_Ref_1.OriginalTransactionId,FPT_Ref_1.[PaymentReasonTextId]
FROM WarehouseMgmt.FactPaymentTrans AS FPT_Ref_1
JOIN #Temp T ON T.TransactionId = FPT_Ref_1.OriginalTransactionId AND T.TransactionStepId = FPT_Ref_1.TransactionStepId
) AS FPT_Ref ON FPT_Ref.OriginalTransactionId = FPT.TransactionId AND FPT_Ref.FactType = ''SOURCE''
WHERE (FPT.FactType = ''SOURCE'') ' + @sqlFilters
我试图把这个
SELECT TransactionId,MAX(TransactionStepId) TransactionStepId
FROM [WarehouseMgmt].[FactPaymentTrans]
WHERE FactType = ''SOURCE''
GROUP BY TransactionId
在临时表的代码,但是这是最糟糕的,甚至如果没有临时表。我想选择由MAX(TransactionStepId取得最新TRANSACTIONID(那),并在左侧选择最后TRANSACTIONID JOIN
我的执行计划是:
你有没有考虑使用ROW_NUMBER而不是加入表格两次?请尝试此操作:
;WITH FactPaymentTrans_Last
AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY TransactionID ORDER BY TransactionStepID DESC) AS RN
FROM WarehouseMgmt.FactPaymentTrans
)
SELECT DTST.[Id]
, FPT.[StartTime]
, FPT.[PaymentMethodId]
, FPT.[3DSecureId]
, FPT.[ProductId]
, DPT.[Id]
, FPT.[TransactionStatusId]
, FPT.[Amount]
, FPT.CurrencyId
, FPT.[PlayerId]
, DPL.[SourceOrigId]
, DPL.[Username]
, DPL.[FirstName]
, DPL.[LastName]
, DPL.[BrandId]
, DPL.[VIPLevelId]
, DPL.[MarketingChannelId]
, DPL.[MarketingSourceId]
, FPT.[PaymentReasonTextId]
, FPT_Ref.[PaymentReasonTextId]
, FPT.CreatedByAdminId
, FPT.[OriginalTransactionId]
, FPT.[TransactionId]
, FPT_Ref.[OriginalTransactionId]
, FPT.[ProviderTransactionOrigId]
FROM FactPaymentTrans_Last AS FPT
INNER JOIN WarehouseMgmt.DimTransactionStepType AS DTST
ON FPT.[TransactionStepTypeId] = DTST.[Id]
INNER JOIN WarehouseMgmt.DimPlayer AS DPL
ON FPT.PlayerId = DPL.Id
INNER JOIN WarehouseMgmt.DimProduct AS DP
ON DP.Id = FPT.ProductId
WHERE FPT.RN = 1;
这只是一个片段,让你知道如何使用它来得到最新的TransactionIds
根据其TransactionStepId
。
如果这还不够 - 请发布这些:
- 你的表结构
- 他们
- 你执行计划
指数这将有助于给你的建议。
我在我的问题中添加了执行计划 – user2171512
有多种因素可以提高查询性能,其中一项可以完成,而不必知道有关索引,数据库模式,数据分布等的详细信息,这是查询中JOIN
的顺序。
我已经重构你的查询,我认为你应该像以前一样得到同样的结果,但有一个改进的执行时间:
SELECT DTST.[Id]
,FPT.[StartTime]
,FPT.[PaymentMethodId]
,FPT.[3DSecureId]
,FPT.[ProductId]
,DPT.[Id]
,FPT.[TransactionStatusId]
,FPT.[Amount]
,FPT.CurrencyId
,FPT.[PlayerId]
,DPL.[SourceOrigId]
,DPL.[Username]
,DPL.[FirstName]
,DPL.[LastName]
,DPL.[BrandId]
,DPL.[VIPLevelId]
,DPL.[MarketingChannelId]
,DPL.[MarketingSourceId]
,FPT.[PaymentReasonTextId]
,FPT_Ref.[PaymentReasonTextId]
,FPT.CreatedByAdminId
,FPT.[OriginalTransactionId]
,FPT.[TransactionId]
,FPT_Ref.[OriginalTransactionId]
,FPT.[ProviderTransactionOrigId]
FROM WarehouseMgmt.FactPaymentTrans AS FPT
INNER JOIN WarehouseMgmt.DimPlayer AS DPL
ON FPT.PlayerId = DPL.Id
INNER JOIN WarehouseMgmt.DimProduct AS DP
ON DP.Id = FPT.ProductId
INNER JOIN WarehouseMgmt.DimProductType AS DPT
ON DPT.Id = DP.ProductTypeId
INNER JOIN WarehouseMgmt.DimTransactionStepType AS DTST
ON FPT.[TransactionStepTypeId] = DTST.[Id]
WHERE (FPT.FactType = '' SOURCE '')
AND EXISTS (SELECT 1
FROM [WarehouseMgmt].[FactPaymentTrans]
WHERE FactType = '' SOURCE ''
AND FPT.TransactionId = TransactionId
AND FPT.TransactionStepId = TransactionStepId)
AND EXISTS (SELECT 1
FROM [WarehouseMgmt].[DimLoyaltyProgramLevel] DLPL
WHERE DLPL.[Id] = DPL.VipLevelId)
AND EXISTS (SELECT 1
FROM WarehouseMgmt.DimTimeZone DTZ
WHERE FPT.[TimeId] = DTZ.TimeUTCId
AND DTZ.TimeId BETWEEN @DimStartDate AND @DimEndDate)
首先你没有FPT_Ref表,第二个不能过滤时间,然后在该表上进行连接,它返回错误。请参阅我的代码并进行相同的更改 – user2171512
@ user2171512这个想法是它不应该是相同的(有些查询是多余的)。我已经对查询进行了更正,以便筛选器可以使用“进入时间”。请尝试新的查询。 –
我仍然有FPT_Ref [PaymentReasonTextId]和FPT_Ref [OriginalTransactionId]错误。那是我的参考表格(加入表格)。我该如何解决这个问题和以前一样? – user2171512
你能共享一个执行计划?有关表格索引的详细信息?此外,查询结尾还有一个小撇号。这有没有什么用处(*可能是一些缺少的代码?*)还是这是一个错误? –
你可以尝试我的查询,看看执行时间是否有任何改进? –