SQL Server Performance Forum – Threads Archive

Partitioned View Performance Problem

I have a partitioned view for a group of 10 tables. The partitioning works for the most part. I can update the view, when I run a static query the execution plan only hits the one table that meets the check constraint, etc. The problem is when I put a query into a stored procedure, or even just declare a variable for the where clause part that references the partitioning column, the query engine hits every table in the partitioned view. Here is the basis of my DB schema: CREATE TABLE Clients0 (
ClientID CHAR(11) NOT NULL,
State CHAR(2) NOT NULL,
Agent CHAR(4) NOT NULL,
… extra columns here …
CONSTRAINT PK_Clients0 PRIMARY KEY (ClientID, State, Code)
CONSTRAINT cst_State CHECK (State = ’10’)
)
… etc through 9 CREATE VIEW Clients AS
SELECT *
FROM Clients0
UNION ALL
SELECT *
FROM Clients1
… etc through 9 …
Now, when I run the following query the execution plan only hits the Clients0 table: SELECT *
FROM Clients
WHERE State = ’10’
AND Code = ‘1111’ But when I run the next query, it hits all the tables in the view: DECLARE @State CHAR(2)
SET @State = ’10’
SELECT *
FROM Clients
WHERE State = @State
AND Code = ‘1111’
Does anyone have any ideas? I am about at my wits end with this one.

---

OK, I figured it out. From the link below, it appears that it is not really a performance problem at all, just how the execution plan is displayed. I will test it once I get all the data loaded, but it appears it should work just fine. http://groups.google.com/groups?hl=…oned&meta=group%3Dcomp.databases.ms-sqlserver

---

]]>