gpt4 book ai didi

sql-server - 一个额外的内连接导致逻辑读取数量激增

转载 作者:行者123 更新时间:2023-12-03 00:54:44 24 4
gpt4 key购买 nike

我有两张看起来几乎相同的 table 。当我选择其中任何一个时,我的逻辑读取次数约为 8.000-10.000 次读取。但是当我内部加入它们时,我得到了大约 380.000 次逻辑读取。

我使用 MS SQL 2012。

我遇到问题的查询如下所示:

SELECT ac.ID AS AccountID
,ab.Balance
,abc.BalanceInAccountCurrency
FROM dbo.Dates d
INNER JOIN dbo.Accounts ac ON d.[Date] BETWEEN ac.CreationDate AND ac.ClosureDate
INNER JOIN dbo.AccountBalances ab ON ab.AccountID = ac.ID AND d.[Date] BETWEEN ab.CreationDate AND ab.ClosureDate
INNER JOIN dbo.AccountBalancesInAccountCurrency abc ON abc.AccountID = ac.ID AND d.[Date] BETWEEN abc.CreationDate AND abc.ClosureDate
WHERE d.[Date] = DATEFROMPARTS(2017,06,20);

enter image description here

当我只像这样加入 AccountBalances 时:

SELECT ac.ID AS AccountID
,ab.Balance
FROM dbo.Dates d
INNER JOIN dbo.Accounts ac ON d.[Date] BETWEEN ac.CreationDate AND ac.ClosureDate
INNER JOIN dbo.AccountBalances ab ON ab.AccountID = ac.ID AND d.[Date] BETWEEN ab.CreationDate AND ab.ClosureDate
WHERE d.[Date] = DATEFROMPARTS(2017,06,20);

enter image description here

当我仅加入 AccountBalancesInAccountCurrency 时,我会得到类似的结果。

我的主键/聚集索引在两个表上都是这样的:

ALTER TABLE [dbo].[AccountBalances] ADD  CONSTRAINT [PK_AccountBalances] PRIMARY KEY CLUSTERED 
(
ClosureDate DESC,
CreationDate DESC,
AccountID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

我做错了什么?

执行计划

Both Tables enter image description here

AccountBalancesInAccountCurrency Only enter image description here

AccountBalances Only enter image description here

额外信息

查询结果为 170460 行,总逻辑读取次数为 376.000。但如果我在查询中使用“top 170000”,则总逻辑读取次数仅为 44.000。统计 IO 如下所示: enter image description here

And the execution plan looks like this enter image description here

最佳答案

我找到了解决问题的方法。

1.AccountBalances 包含以银行记帐货币(丹麦克朗)表示的余额,AccountBalancesInAccountCurrency 包含以账户货币和丹麦克朗表示的余额。大多数货币为丹麦克朗,并且存在于两个表中。因此,我从 AccountBalancesInAccountCurrency 中删除了所有 DKK 余额,并对该表进行了左连接。

2.我还更改了 AccountBalancesInAccountCurrency 上的主键:

ALTER TABLE [dbo].[AccountBalances] ADD  CONSTRAINT [PK_AccountBalances] 
PRIMARY KEY CLUSTERED
(
ClosureDate DESC,
CreationDate DESC,
AccountID ASC
)

至:

ALTER TABLE [dbo].[AccountBalancesInAccountCurrency] ADD  CONSTRAINT 
[PK_AccountBalancesInAccountCurrency] PRIMARY KEY CLUSTERED
(
[AccountID] ASC,
[ClosureDate] DESC
)

现在我的 IO 看起来像这样: enter image description here

and my execution plan like this .

感谢所有引导我前进的评论。

节日快乐!

关于sql-server - 一个额外的内连接导致逻辑读取数量激增,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47833715/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com