Partitioned Views and Parameters

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 2005 Forums

Transact-SQL (2005)

Partitioned Views and Parameters

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-10-21 : 07:25:09

We have a very large database which is split across multiple tables. The tables are joined using a partitioned view. However, we are encountering performance issues around parition elimination when using parameterised queries.

Other than using dynamic sql, is there a way to avoid this? Here is some sample code which mimics the issue. As you will be able to see, the query will go direct to the required tables when the value is hardcoded, yet as soon as we parameterise it, it scans ALL the table partitions:

SET STATISTICS PROFILE OFF
GO
USE [master]
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE [name] = 'Test')
BEGIN DROP DATABASE Test END
GO
CREATE DATABASE Test
go
USE Test
GO
CREATE TABLE Test1 (ID INT)
GO
CREATE TABLE Test2 (ID INT)
GO
CREATE TABLE Test3 (ID INT)
GO
ALTER TABLE Test1 ADD CONSTRAINT CK_Test1 CHECK (ID >=1 AND ID < 6)
ALTER TABLE Test2 ADD CONSTRAINT CK_Test2 CHECK (ID >=6 AND ID < 11)
ALTER TABLE Test3 ADD CONSTRAINT CK_Test3 CHECK (ID >=10 AND ID < 16)
GO
INSERT INTO Test1
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
GO
INSERT INTO Test2
SELECT 6
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9
UNION
SELECT 10
GO
INSERT INTO Test3
SELECT 11
UNION
SELECT 12
UNION
SELECT 13
UNION
SELECT 14
UNION
SELECT 15
GO
-- Check the constraint is working. This should throw an error
--INSERT INTO Test3
--SELECT 1
GO
CREATE VIEW TestPartition
AS
SELECT ID FROM Test1
UNION ALL
SELECT ID FROM Test2
UNION ALL
SELECT ID FROM Test3
GO
-- Test whether SQL Server will eliminate the irrelevant partitions
SET STATISTICS PROFILE ON
GO
DECLARE @ID INT
SET @ID = 2
SELECT * FROM TestPartition WHERE ID = @ID
GO
SELECT * FROM TestPartition WHERE ID = 2
GO
DECLARE @ID INT
SET @ID = 8
SELECT * FROM TestPartition WHERE ID < @ID
GO
SELECT * FROM TestPartition WHERE ID < 8
GO
SET STATISTICS PROFILE OFF
GO
USE [master]
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE [name] = 'Test')
BEGIN DROP DATABASE Test END

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-10-21 : 08:50:32

Having done some reading around the subject, it appears that when the optimiser creates the query plan, it does not know the values of the parameters (since the whole point of using parameterized queries is to encourage re-use of query plans). Therefore, without knowing the values, it cannot predict which partition it will need to access, hence creating conditional logic across ALL the partitions.

I have no idea what to do, as our application uses LINQ which parameterises all the queries???

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources