SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Partitioned View Index Selection problem.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 07/28/2005 :  01:47:11  Show Profile  Visit ditch's Homepage  Reply with Quote

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.

Kristen
Test

United Kingdom
22431 Posts

Posted - 07/28/2005 :  02:14:07  Show Profile  Reply with Quote
Dumbo question - you've got CONSTRAINTs on the PKs of the underlying Tables, right?

Kristen
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 07/28/2005 :  02:18:43  Show Profile  Visit ditch's Homepage  Reply with Quote
Very dumb - question ofcourse!

ooops - thanx for reminding me.
Feel bad now :(


Duane.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000