|
ditch
Flowing Fount of Yak Knowledge
South Africa
1417 Posts |
 Posted - 07/28/2005 : 01:47:11
|
Has anybody come across the following situation with partitioned views on SQL 2000. The environment is configured as follows:
Windows 2003 Server
SQL Server 2000 Enterprise with SP3 + Hot Fixes (Build 859)
A View Database with the security for the user’s application.
A Production Database with the all the Data
The main transaction table has been federated into 6 tables all in there own database on the same server. The Federated view is housed in the production database. The federated tables make up about 300GB with about 660 million records.
The problem that we are having is when we query the federated tables via the partitioned view the optimiser selects the incorrect index to apply to the table. When the query is changed to directly reference the source table in the federation then the correct (Cluster key index) is used. This problem results in the queries execute time increasing from the normal 1 second to 14 minutes. The cost goes from 1.4 to 1463.
The following steps have been tried with no luck:
Recompile the View
Update the Statistics all the tables
Change the Use that is used to Union instead of Union all
Query hints
Below is the show plan for the long running query:
|--Compute Scalar(DEFINE:([Expr1157]=substring([Stock].[StockCode], 1, 4), [Expr1158]=rtrim(Convert([StockLedger].[DocketCode])), [Expr1159]=[StockLedger].[LedgerQty]*Convert([StockLedger].[AverageCostPrice]), [Expr1160]=[StockLedger].[LedgerQty]*Convert
|--Compute Scalar(DEFINE:([StockLedger].[LedgerQty]=[StockLedger].[LedgerQty], [StockLedger].[DocketCode]=[StockLedger].[DocketCode], [StockLedger].[DocketDate]=[StockLedger].[DocketDate], [StockLedger].[TradingDate]=[StockLedger].[TradingDate], [St
|--Nested Loops(Inner Join)
|--Clustered Index Seek(OBJECT:([Stock8].[dbo].[Item].[PK_Item]), SEEK:([Item].[DivisionId]=1 AND [Item].[DeptGroupId]=1 AND [Item].[DeptSubGroupId]=6 AND [Item].[DepartmentId]=12 AND [Item].[ItemId]=268) ORDERED FORWARD)
|--Parallelism(Gather Streams, ORDER BY:([StockLedger].[TradingDate] ASC))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Stock].[SizeId], [Stock].[SizeTypeId]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([StockLedger].[StockId]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([StockLedger].[TransactionTypeId]))
| | |--Sort(ORDER BY:([StockLedger].[TradingDate] ASC))
| | | |--Filter(WHERE:([StockLedger].[TradingDate]>='Jul 17 2005 12:00AM' AND [StockLedger].[TradingDate]<='Jul 27 2005 11:59PM'))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1008]), OBJECT:([FederationA].[dbo].[StockLedger]))
| | | |--Index Scan(OBJECT:([FederationMRP].[dbo].[StockLedger].[X_StockLedger_GLPeriodId]), WHERE:((((([StockLedger].[StoreId]=1032 AND [StockLedger].[ItemId]=268) AND [StockLedger].[DeptSubGroupId]=6) AND [S
| | |--Clustered Index Seek(OBJECT:([Stock8].[dbo].[TransactionType].[PK_TransactionType]), SEEK:([TransactionType].[TransactionTypeId]=[StockLedger].[TransactionTypeId]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([Stock8].[dbo].[Stock].[PK_Stock]), SEEK:([Stock].[DivisionId]=1 AND [Stock].[DeptGroupId]=1 AND [Stock].[DeptSubGroupId]=6 AND [Stock].[DepartmentId]=12 AND [Stock].[ItemId]=268 AND [Stock].[
|--Clustered Index Seek(OBJECT:([Stock8].[dbo].[Size].[PK_Size]), SEEK:([Size].[SizeTypeId]=[Stock].[SizeTypeId] AND [Size].[SizeId]=[Stock].[SizeId]) ORDERED FORWARD)
This is the result from the query that reference the table directly
|--Compute Scalar(DEFINE:([Expr1011]=substring([Stock].[StockCode], 1, 4), [Expr1012]=rtrim(Convert([SL].[DocketCode])), [Expr1013]=[SL].[LedgerQty]*Convert([SL].[AverageCostPrice]), [Expr1014]=[SL].[LedgerQty]*Convert([SL].[CurrentPrice]), [Expr1015]=[S
|--Nested Loops(Inner Join, OUTER REFERENCES:([Stock].[SizeId], [Stock].[SizeTypeId]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([SL].[TransactionTypeId]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([SL].[StockId], [Item].[DivisionId]))
| | |--Sort(ORDER BY:([SL].[TradingDate] ASC))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Item].[DivisionId]))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1003]), OBJECT:([Stock8].[dbo].[Item]))
| | | | |--Index Scan(OBJECT:([Stock8].[dbo].[Item].[X_OldCode]), WHERE:((([Item].[DeptGroupId]=1 AND [Item].[DeptSubGroupId]=6) AND [Item].[DepartmentId]=12) AND [Item].[ItemId]=268))
| | | |--Filter(WHERE:(STARTUP EXPR([Item].[DivisionId]=1)))
| | | |--Clustered Index Seek(OBJECT:([FederationMRP].[dbo].[StockLedger].[PK_StockLedger] AS [SL]), SEEK:([SL].[DivisionId]=[Item].[DivisionId] AND [SL].[CountryId]=2 AND [SL].[StoreId]=1032 AND [SL].[DeptGroupId]=1 AND [SL]
| | |--Clustered Index Seek(OBJECT:([Stock8].[dbo].[Stock].[PK_Stock]), SEEK:([Stock].[DivisionId]=[Item].[DivisionId] AND [Stock].[DeptGroupId]=1 AND [Stock].[DeptSubGroupId]=6 AND [Stock].[DepartmentId]=12 AND [Stock].[ItemId]=268 AND [
| |--Clustered Index Seek(OBJECT:([Stock8].[dbo].[TransactionType].[PK_TransactionType]), SEEK:([TransactionType].[TransactionTypeId]=[SL].[TransactionTypeId]) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Stock8].[dbo].[Size].[PK_Size]), SEEK:([Size].[SizeTypeId]=[Stock].[SizeTypeId] AND [Size].[SizeId]=[Stock].[SizeId]) ORDERED FORWARD)
Anybody seen this before or got any ideas?
Duane. |
|
Partitioned View Index Selection problem.
