我们现在有一些客户,他们的查询需要很长时间才能从分区视图中获得结果。经过仔细检查,我们发现执行计划扫描每个表,而不仅仅是那些具有相关数据的表。
https://technet.microsoft.com/en-us/library/ms190019(v=sql.105).aspx
在本例中,我们有12个表(1998年每个月):
CREATE TABLE Jan1998sales
(
OrderID INT,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL CHECK(DATEPART(yy, OrderDate) = 1998),
OrderMonth INT CHECK (OrderMonth = 1),
DeliveryDate DATETIME NULL CHECK(DATEPART(MM, DeliveryDate) = 1),
CONSTRAINT Jan1998sales_OrderIDMonth PRIMARY KEY (OrderID, OrderMonth)
)
我们看到的视图如下所示:
CREATE VIEW Year1998Sales
AS
(
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
[...]
UNION ALL
SELECT * FROM Dec1998Sales
)
现在我们有一个查询可以很好地工作(只扫描必要的表):
SELECT * FROM Year1998Sales
WHERE (OrderMonth = 5 OR OrderMonth = 6) AND CustomerID = 64892
但如果我们用参数过滤,它会突然扫描所有表:
DECLARE @MonthA int = 5
DECLARE @MonthB int = 6
SELECT * FROM Year1998Sales
WHERE (OrderMonth = @MonthA OR OrderMonth = @MonthB) AND CustomerID = 64892
有人知道如何让SQL Server只扫描必要的表,而仍然使用参数化过滤器吗?
有关完整代码,请查看以下SQL代码:
http://sqlfiddle.com/#!6/e1f33/1
