I want to speed up the access to a view and have put an index over it. Unfortunately this is not used, although it seems logical and obvious to me that this should be used. Can someone tell me what I am doing wrong?
CREATE VIEW dbo.PickingInfoTEST WITH SCHEMABINDING AS
SELECT
ccs.ID_ChargeCarrier,
ccs.ID_Storage,
sp.ID_PlantArea,
pa.ID_Plant,
p.CompanyNo,
CAST(s.Description1 AS INT) AS PickingStorage,
s.Description2 AS TargetCountry,
COUNT_BIG(*) AS Commonness
FROM dbo.ChargeCarrier_Storage ccs
INNER JOIN dbo.Storage s ON s.ID = ccs.ID_Storage
INNER JOIN dbo.StoragePlace sp ON sp.ID = s.ID
INNER JOIN dbo.PlantArea pa ON pa.ID = sp.ID_PlantArea
INNER JOIN dbo.Plant p ON p.ID = pa.ID_Plant
WHERE s.ID_StorageType = 1
GROUP BY
ccs.ID_ChargeCarrier,
ccs.ID_Storage,
sp.ID_PlantArea,
pa.ID_Plant,
p.CompanyNo,
CAST(s.Description1 AS INT),
s.Description2
GO
CREATE UNIQUE CLUSTERED INDEX UQX_OnePickingStoragePerCarrier
ON dbo.PickingInfoTEST (ID_ChargeCarrier)
GO
CREATE UNIQUE INDEX IX_ChargeCarrier_OriginalStorage
ON dbo.PickingInfoTEST (ID_ChargeCarrier DESC) INCLUDE (ID_Storage)
GO
SELECT ID_ChargeCarrier
,ID_Storage
FROM dbo.PickingInfoTEST
WHERE ID_ChargeCarrier between 1234 and 5678I would expect the SELECT statement at the end of my code block to load the cached values from IX_ChargeCarrier_OriginalStorage . It runs the code of the view instead.
2条答案
按热度按时间fzwojiic1#
The first index on a view has to be a unique, clustered, index. If you don't have another index on there, pretty sure you're seeing that index get defaulted to the cluster. The optimizer won't always spot the index that is best for the query because of row estimates and statistics and other factors. Since this is now a clustered index, you could write a query to select directly from that index.
vulvrdjw2#
The problem is that by default SQL Server will expand a view to its definition within your query. Then it will try to match indexes, which may include your indexed view.
This can sometimes fail, especially with a view as complex as this. It also only matches indexed views in Enterprise, Evaluation and Developer editions.
To prevent this happening , and for otherreasons too, you should almost always use
WITH (NOEXPAND)hint on indexed views.There are rare occasions when you might not want to use this hint, such as if you might remove the index, in which case the query will fail. See also Can there be situations where using (noexpand) on an indexed view is undesirable? . But in most cases you should use it.
As mentioned by others, the descending ordered index is almost completely pointless.
Also, you should remove the
CASTfrom theGROUP BY, as it can cause unnecessary sorting (the optimizer can work out that sorting is unnecessary if the primary key is already grouped).