I have a view that pulls batch properties for on-hand inventory. The view joins InventSum to InventDim on InventDimId and joins InventBatch on ItemId = InventSum.ItemId and InventBatchId = InventDim.InventBatchId.
If the relation on ItemId is removed, the query performance is good, but could potentially return inaccurate data on batches that are not unique to an item Id.
With the ItemId relation included, the view takes 2-3 minutes to open, which is horrible.
SQL performance analyzer recommends this index be created:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[INVENTSUM] ([PHYSICALINVENT])
INCLUDE ([ITEMID],[INVENTDIMID],[DATAAREAID],[PARTITION],[RECID])
AX already has an index on ItemId and InventDimId, but it is a clustered alternate key index, which I why I assume SQL is not using it.
If I try to create the index, AX complains that the new one overlaps the existing index. Also, AX will not allow both ItemId and InventDimId be included.
Any suggestions on how to modify the view so the existing index will be utilized?
or.. suggestions on how to create an index that SQL will recognized and use?
Thanks,
- Brad
