Querying partition views with local variables

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Querying partition views with local variables

Author

Topic

dal
Starting Member

1 Post

Posted - 2002-05-21 : 06:15:04

Hi team

I have a partition (union) view in SQL7 consisting of a number of tables. Each table has a check constraint on the partition column.
When querying the view with a hard coded integer value against the partition column, the execution plan shows that only the table with the relevant data is queried (i.e. the required behaviour). However, when I use a local variable rather than a hard coded value in my query, all tables in the partition view are queried, and the performance suffers as a result. I also note that the check constraint is not used if the partition column is returned from a subselect in a view on the partition view.

I would really like to have the check constraint used in both these situations. Does anyone have any suggestions?

Thanks.

Dal Sandhu.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-21 : 07:39:45

The local variable must include the entire view, because there is no specific value that the optimizer can use in the query plan. Running the query twice with two different values in the variable could very easily involve two different tables. This is normal, and there's no way to use a partitioned view like this without that overhead.

From Books Online:

The SQL Server 2000 query processor can also build dynamic logic into query execution plans for SQL statements where the key values are not known when the plan must be built. For example, consider this stored procedure:

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT
AS
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter

SQL Server 2000 cannot predict what key value will be supplied by the @CustomerIDParameter parameter each time the procedure is executed. Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed.

There are some optimizations that the optimizer can use that alleviate this problem, but it's not guaranteed to improve performance.

The same thing applies to the constraint: it belongs to a table, not the whole view, so the entire view must be involved if you are inserting or modifying it.

The only way to work around these issues is to work with the base table(s) whenever possible.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources