我有一个查询优化问题。假设有一个包含所有发票的表格。使用TVP(表值参数),我想通过提供1..n id来选择几条记录,或者通过提供一个值为-1的单个id来返回所有记录。
DECLARE @InvoiceIdSet AS dbo.TBIGINT;
INSERT INTO @InvoiceIdSet VALUES (1),(2),(3),(4)
--INSERT INTO @InvoiceIdSet VALUES (-1)
SELECT TOP 100
I.Id ,
Number ,
DueDate ,
IssuedDate ,
Amount ,
Test3
FROM dbo.Invoices I
--WHERE EXISTS ( SELECT NULL
-- FROM @InvoiceIdSet
-- WHERE I.Id = ID
-- OR ID = -1 )
--CROSS APPLY @InvoiceIdSet s WHERE i.Id = s.ID OR s.ID = -1
JOIN @InvoiceIdSet S ON S.ID = I.Id OR S.ID=-1
不管我使用哪种选择方法,查询都将非常有效地执行,直到我开始使用OR运算符为止,这时它开始需要很长时间才能返回几条记录,但是所有记录都将很快地返回。
任何指示和建议将不胜感激。
第一个计划没有OR,第二个计划带有OR。
更新:摆弄了不同的选项后,无论参数数量如何,我都认为该解决方案是性能最快的解决方案。
首先将UserDefinedTableType更改为包括主键索引:
CREATE TYPE [dbo].[TBIGINT] AS TABLE(
[ID] [bigint] NOT NULL PRIMARY KEY CLUSTERED
)
现在,select语句如下所示:
SELECT TOP 100
I.Id ,
Number ,
DueDate ,
IssuedDate ,
Amount ,
Test3
FROM dbo.Invoices I
WHERE I.ID IN ( SELECT S.ID
FROM @InvoiceIdSet S
WHERE S.ID <> -1
UNION ALL
SELECT S.ID
FROM dbo.Invoices S
WHERE EXISTS ( SELECT NULL
FROM @InvoiceIdSet
WHERE ID = -1 ) )
计划变得更大了,但是性能几乎是不变的,介于很少(第一个计划)和所有(第二个计划)记录之间。
如您所见,这些计划现在是相同的,并且在不到一秒的时间内从1M行返回了所需的记录。
我很想听听社区对此解决方案的看法。
感谢大家的帮助。
我将在这里接受自己的回答:
DECLARE @InvoiceIdSet AS TBIGINT
--INSERT INTO @InvoiceIdSet
--VALUES ( 1 ),
-- ( 2 ),
-- ( 3 ),
-- ( 4 )
INSERT INTO @InvoiceIdSet VALUES ( -1 )
SELECT TOP 100
I.Id ,
Number ,
DueDate ,
IssuedDate ,
Amount ,
Test3
FROM dbo.Invoices I
WHERE I.ID IN ( SELECT S.ID
FROM @InvoiceIdSet S
WHERE NOT EXISTS ( SELECT NULL
FROM @InvoiceIdSet
WHERE ID = -1 )
UNION ALL
SELECT S.ID
FROM dbo.Invoices S
WHERE EXISTS ( SELECT NULL
FROM @InvoiceIdSet
WHERE ID = -1 ) )
它在所有和某些情况下都表现良好。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句