- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我之前发布过类似的内容,但我现在从不同的方向接近这个问题,所以我提出了一个新问题。我希望这没问题。
我一直在与 CTE 合作,该 CTE 会根据父收费创建一笔收费。可以在此处查看 SQL 和详细信息:
CTE Index recommendations on multiple keyed table
我不认为我在 CTE 上遗漏了任何东西,但是当我将它与大数据表(350 万行)一起使用时,我遇到了问题。
表tblChargeShare
包含一些我需要的其他信息,例如 InvoiceID
,所以我将我的 CTE 放在 View 中 vwChargeShareSubCharges
并把它放到 table 上。
查询:
Select t.* from vwChargeShareSubCharges t
inner join
tblChargeShare s
on t.CustomerID = s.CustomerID
and t.MasterChargeID = s.ChargeID
Where s.ChargeID = 1291094
Select ChargeID from tblChargeShare Where InvoiceID = 1045854
1291094
Select t.* from vwChargeShareSubCharges t
inner join
tblChargeShare s
on t.CustomerID = s.CustomerID
and t.MasterChargeID = s.ChargeID
Where InvoiceID = 1045854
WITH RCTE AS
(
SELECT ParentChargeId, s.ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount, ISNULL(s.TaxAmount, 0) as TaxAmount,
ISNULL(s.DiscountAmount, 0) as DiscountAmount, s.CustomerID, c.ChargeID as MasterChargeID
from tblCharge c inner join tblChargeShare s
on c.ChargeID = s.ChargeID Where s.ChargeShareStatusID < 3 and ParentChargeID is NULL
UNION ALL
SELECT c.ParentChargeID, c.ChargeID, Lvl+1 AS Lvl, ISNULL(s.TotalAmount, 0), ISNULL(s.TaxAmount, 0), ISNULL(s.DiscountAmount, 0) , s.CustomerID
, rc.MasterChargeID
from tblCharge c inner join tblChargeShare s
on c.ChargeID = s.ChargeID
INNER JOIN RCTE rc ON c.PArentChargeID = rc.ChargeID and s.CustomerID = rc.CustomerID Where s.ChargeShareStatusID < 3
)
Select MasterChargeID as ChargeID, rcte.CustomerID, Sum(rcte.TotalAmount) as TotalCharged, Sum(rcte.TaxAmount) as TotalTax, Sum(rcte.DiscountAmount) as TotalDiscount
from RCTE inner join tblChargeShare s on rcte.ChargeID = s.ChargeID and RCTE.CustomerID = s.CustomerID
Where InvoiceID = 1045854
Group by MasterChargeID, rcte.CustomerID
GO
Select t.* from
vwChargeShareSubCharges t
Where t.MasterChargeID = 1291094
DECLARE @ChargeID int = 1291094
Select t.* from
vwChargeShareSubCharges t
Where t.MasterChargeID = @ChargeID
Where t.MasterChargeID in (1291090, 1291091, 1291092, 1291093, 1291094, 1291095, 1291096, 1291097, 1291098, 1291099, 129109)
CREATE TABLE [tblChargeTest](
[ChargeID] [int] IDENTITY(1,1) NOT NULL,
[ParentChargeID] [int] NULL,
[TotalAmount] [money] NULL,
[TaxAmount] [money] NULL,
[DiscountAmount] [money] NULL,
[InvoiceID] [int] NULL,
CONSTRAINT [PK_tblChargeTest] PRIMARY KEY CLUSTERED
(
[ChargeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
Insert into tblChargeTest
(discountAmount, TotalAmount, TaxAmount)
Select ABS(CHECKSUM(NewId())) % 10, ABS(CHECKSUM(NewId())) % 100, ABS(CHECKSUM(NewId())) % 10
GO 100000
Update tblChargeTest
Set ParentChargeID = (ABS(CHECKSUM(NewId())) % 60000) + 20000
Where ChargeID = (ABS(CHECKSUM(NewId())) % 20000)
GO 5000
CREATE VIEW [vwChargeShareSubCharges] AS
WITH RCTE AS
(
SELECT ParentChargeId, ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount, ISNULL(TaxAmount, 0) as TaxAmount,
ISNULL(DiscountAmount, 0) as DiscountAmount, ChargeID as MasterChargeID
FROM tblChargeTest Where ParentChargeID is NULL
UNION ALL
SELECT rh.ParentChargeID, rh.ChargeID, Lvl+1 AS Lvl, ISNULL(rh.TotalAmount, 0), ISNULL(rh.TaxAmount, 0), ISNULL(rh.DiscountAmount, 0)
, rc.MasterChargeID
FROM tblChargeTest rh
INNER JOIN RCTE rc ON rh.PArentChargeID = rc.ChargeID --and rh.CustomerID = rc.CustomerID
)
Select MasterChargeID, ParentChargeID, ChargeID, TotalAmount, TaxAmount, DiscountAmount , Lvl
FROM RCTE r
GO
--Slow Query:
Declare @ChargeID int = 60900
Select *
from [vwChargeShareSubCharges]
Where MasterChargeID = @ChargeID
--Fast Query:
Select *
from [vwChargeShareSubCharges]
Where MasterChargeID = 60900
最佳答案
在这里,SQL Server 可以为您做的最好的事情是将过滤器推送到 ChargeID
向下进入 View 内递归 CTE 的 anchor 部分。这允许查找您需要从中构建层次结构的唯一行。当您将参数作为常量值提供时,SQL Server 可以进行优化(使用名为 SelOnIterator
的规则,对于那些对此类事情感兴趣的人):
当你使用局部变量时它不能这样做,所以 ChargeID
上的谓词卡在 View 之外(从所有 NULL
id 开始构建完整的层次结构):
使用变量时获得最佳计划的一种方法是强制优化器在每次执行时编译新计划。然后在执行时根据变量中的特定值定制生成的计划。这是通过添加 OPTION (RECOMPILE)
来实现的。查询提示:
Declare @ChargeID int = 60900;
-- Produces a fast execution plan, at the cost of a compile on every execution
Select *
from [vwChargeShareSubCharges]
Where MasterChargeID = @ChargeID
OPTION (RECOMPILE);
CREATE FUNCTION [dbo].[udfChargeShareSubCharges]
(
@ChargeID int
)
RETURNS TABLE AS RETURN
(
WITH RCTE AS
(
SELECT ParentChargeID, ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount, ISNULL(TaxAmount, 0) as TaxAmount,
ISNULL(DiscountAmount, 0) as DiscountAmount, ChargeID as MasterChargeID
FROM tblChargeTest
Where ParentChargeID is NULL
AND ChargeID = @ChargeID -- Filter placed here explicitly
UNION ALL
SELECT rh.ParentChargeID, rh.ChargeID, Lvl+1 AS Lvl, ISNULL(rh.TotalAmount, 0), ISNULL(rh.TaxAmount, 0), ISNULL(rh.DiscountAmount, 0)
, rc.MasterChargeID
FROM tblChargeTest rh
INNER JOIN RCTE rc ON rh.ParentChargeID = rc.ChargeID --and rh.CustomerID = rc.CustomerID
)
Select MasterChargeID, ParentChargeID, ChargeID, TotalAmount, TaxAmount, DiscountAmount , Lvl
FROM RCTE r
)
Declare @ChargeID int = 60900
select *
from dbo.udfChargeShareSubCharges(@ChargeID)
ParentChargeID
上的索引中受益。 .
create index ix_ParentChargeID on tblChargeTest(ParentChargeID)
关于sql - 加入时 CTE 非常慢,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/22807213/
SQL、PL-SQL 和 T-SQL 之间有什么区别? 谁能解释一下这三者之间的区别,并提供每一个的相关使用场景? 最佳答案 SQL 是一种对集合进行操作的查询语言。 它或多或少是标准化的,几乎所有关
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。 我对 SQL 的了解足以完成我的
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列,该列有一个默认约束为 GETDATE()。 使用 LINQ 时,我没有为 RegistrationDate 列提供任
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈,无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开,visit the help center . 9年前关闭
我有一个存储过程(称为 sprocGetArticles),它从文章表中返回文章列表。这个存储过程没有任何参数。 用户可以对每篇文章发表评论,我将这些评论存储在由文章 ID 链接的评论表中。 有什么方
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。 我自己做了一些数据库工作
SQL Server 中 SQL 语句的最大长度是多少?这个长度是否取决于 SQL Server 的版本? 例如,在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
预先感谢您对此提供的任何帮助。 假设我有一个查询,可以比较跨年的数据,从某个任意年份开始,永无止境(进入 future ),每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列,该列的默认约束为 GETDATE()。 使用 LINQ 时,我没有为 RegistrationDate 列提供任何数
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快,所以我经常看到 DSQL 到处都是。除了明显的缺点,比如在运行之前无法检测到错误并且更难阅读,这是否准确?当我问他为什么一直使用
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ? 最佳答案 sp_executes
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
我正在调试一些构建成功运行的 SQL 命令的代码。 然而,在查询结束时,查询结果似乎被写入了一个文本文件。 完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
我写了下面的 SQL 存储过程,它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表,如下所示 Fruit Colour Apple Red Orange
我是一名优秀的程序员,十分优秀!