Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
 Posted - 2005-07-28 : 01:47:11
|
| Has anybody come across the following situation with partitioned views on SQL 2000. The environment is configured as follows: Windows 2003 ServerSQL 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 DataThe 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. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-28 : 02:14:07
|
| Dumbo question - you've got CONSTRAINTs on the PKs of the underlying Tables, right?Kristen |
 |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-07-28 : 02:18:43
|
| Very dumb - question ofcourse!ooops - thanx for reminding me.Feel bad now :(Duane. |
|
|
|
|
|
|
|
|
Partitioned View Index Selection problem.