December 1, 2008 at 7:05 am
I have split a fairly large table into a partitioned view (364,288,232 rows)on a SQL2005 Standard Edition database server.
I am happy that the view is set up correctly, it is updateable, the check constraint was set up 'WITH CHECK' etc.
When querying the View with TSQL
e.g.
SELECT col1
FROM MyTable
WHERE col2 = 10
I get a great execution plan which only hits the expected underlying table from the partitioned view, however when running the following query
DECLARE @myVar INT
SET @myVar = 10
SELECT col1
FROM MyTable
WHERE col2 = @myVar
The execution plan shows that it is looking through all the underlying tables to the view.
I have tried adding the RECOMPILE hint, tried it as a stored proc, but cannot get the same execution plan as when I am not using variables.
Is it possible to get the optimiser realise it only needs to look in one underlying table, or is this not one of the benefits I can get from partitioned views?
Thanks in advance for any help
December 1, 2008 at 12:31 pm
Silly question, but does your variable data type match col2? Are you using CHECK constraints?
It'd be helpful if you could post a code sample of your table and view.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
December 1, 2008 at 3:54 pm
OK, here's a simple example that demonstrates my issue, despite the fact that this is such a small data set, it is giving similar executioon plans as my real data.
Although the subtree cost for each of the queries is the same with the small dataset, there is quite a difference when using the larger dataset.
[font="Courier New"]
CREATE TABLE Test1(
Age INT NOT NULL
,Name VARCHAR(50)
,CONSTRAINT PK_Test1 PRIMARY KEY CLUSTERED (Age)
)
CREATE TABLE Test2(
Age INT NOT NULL
,Name VARCHAR(50)
,CONSTRAINT PK_Test2 PRIMARY KEY CLUSTERED (Age)
)
ALTER TABLE Test1 WITH CHECK
ADD CONSTRAINT CK_Age1 CHECK (Age <= 50)
ALTER TABLE Test2 WITH CHECK
ADD CONSTRAINT CK_Age2 CHECK (Age > 50)
GO
CREATE VIEW Test
AS
SELECT Age,Name FROM dbo.Test1
UNION ALL
SELECT Age,Name FROM dbo.Test2
GO
INSERT Test VALUES(37,'Jerry')
INSERT Test VALUES(48,'Bill')
INSERT Test VALUES(22,'Sue')
INSERT Test VALUES(11,'Angie')
INSERT Test VALUES(15,'Kim')
INSERT Test VALUES(32,'Steve')
INSERT Test VALUES(82,'Bertha')
INSERT Test VALUES(59,'Ethel')
INSERT Test VALUES(99,'Rudolph')
INSERT Test VALUES(73,'Zelda')
INSERT Test VALUES(68,'Britney')
INSERT Test VALUES(51,'Rob')
INSERT Test VALUES(88,'Jane')
GO
DECLARE @age INT
SET @age = 88
-- Bad plan
SELECT Name
FROM dbo.Test
WHERE age = @age
-- Good plan
SELECT Name
FROM dbo.Test
WHERE age = 88
[/font]
December 30, 2008 at 9:07 am
Is there really no one else in this community who has experienced this problem and found a suitable solution?
December 30, 2008 at 10:41 am
The focus in recent years has been on Partitioned tables instead of Partitioned Views, so I suspect that there jst are not that many people using them and even fewer who are testing the query plans.
The RECOMPILE hint does not work right in 2005, but is supposed to be fixed in 2008, so you could try it there. But failing that all I can really suggest is that you try to take to Microsoft as a bug.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 11:19 am
I have seen this in the past and the problem is that you are missing info just by looking at the execution plan.
When you use the variable version and the "recompile" option you should see "number of executes" = 0 for one of the Clustered index Seek operators. Unfortunately MS removed "number of executes" from the UI on 2005.
* Noel
December 30, 2008 at 11:52 am
For what it's worth, I tried it on SQL Server 2008 and saw no difference.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 12:20 pm
We also have same issue, the work around what we do is , this is really working for us.
DECLARE @age INT,@sqltext nvarchar( )
SET @age = 88
set @sqltext ='SELECT Name
FROM dbo.Test
WHERE age = ' + convert(varchar(3),@age)
exec sp_executesql @sqltext
December 30, 2008 at 12:53 pm
Thank you all for your feed back, I had resorted to building a dynamic sql string, it looks like that is going to be the best solution for now
December 30, 2008 at 12:56 pm
Richard Norris (12/30/2008)
Thank you all for your feed back, I had resorted to building a dynamic sql string, it looks like that is going to be the best solution for now
You may want to start thinking about using partitioned tables in your future 🙂
* Noel
December 30, 2008 at 1:09 pm
As soon as I can convince management that we need Enterprise Edition, I'm sure I will 🙂
December 30, 2008 at 1:40 pm
Richard Norris (12/30/2008)
As soon as I can convince management that we need Enterprise Edition, I'm sure I will 🙂
Good Point! Sounds like ... "it is not gonna happen"
* Noel
December 30, 2008 at 2:09 pm
noeld (12/30/2008)
I have seen this in the past and the problem is that you are missing info just by looking at the execution plan.When you use the variable version and the "recompile" option you should see "number of executes" = 0 for one of the Clustered index Seek operators. Unfortunately MS removed "number of executes" from the UI on 2005.
You can also see this with statistics io on :
Table 'Test2'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The real question is whether having a bad plan is that terrible if it only executes the parts you expect it to ... in this case the only overhead is the filter and the concatenation operators, which each execute once for minimal cost. I'm not sure if dynamic SQL is justified here (although I'm sure it could be with more complex queries).
December 30, 2008 at 8:27 pm
It's gotta "touch" the index to find out what the partition is... and that's all it does... it doesn't actually seek any rows in table 1 but it get's listed as an index seek. It's all part of why the partitioning column has to be on the PK of the tables. Why the hell they made it look different from the hardcoded version is anyone's guess...
Don't forget... % of batch lies like a rug.
--Jeff Moden
December 30, 2008 at 8:34 pm
By the way... all that hooie about the view having to have explicit column names?... it's just that... hooie... a myth... a BOL error... That's IF the base tables are truly identical...
drop table Test1,Test2
go
CREATE TABLE Test1(
Age INT NOT NULL
,Name VARCHAR(50) NOT NULL
,PRIMARY KEY CLUSTERED (Age)
)
CREATE TABLE Test2(
Age INT NOT NULL
,Name VARCHAR(50) NOT NULL
,PRIMARY KEY CLUSTERED (Age)
)
ALTER TABLE Test1
ADD CONSTRAINT CK_Age1 CHECK (Age <= 50)
ALTER TABLE Test2
ADD CONSTRAINT CK_Age2 CHECK (Age >= 51)
GO
drop view test
go
CREATE VIEW Test
AS
SELECT * FROM dbo.Test1
UNION ALL
SELECT * FROM dbo.Test2
GO
INSERT Test VALUES(1,'Jerry')
INSERT Test VALUES(2,'Jerry')
INSERT Test VALUES(3,'Jerry')
INSERT Test VALUES(4,'Jerry')
INSERT Test VALUES(5,'Jerry')
INSERT Test VALUES(6,'Jerry')
INSERT Test VALUES(7,'Jerry')
INSERT Test VALUES(8,'Jerry')
INSERT Test VALUES(9,'Jerry')
INSERT Test VALUES(10,'Jerry')
INSERT Test VALUES(11,'Jerry')
INSERT Test VALUES(12,'Jerry')
INSERT Test VALUES(13,'Jerry')
INSERT Test VALUES(14,'Jerry')
INSERT Test VALUES(15,'Jerry')
INSERT Test VALUES(16,'Jerry')
INSERT Test VALUES(17,'Jerry')
INSERT Test VALUES(18,'Jerry')
INSERT Test VALUES(19,'Jerry')
INSERT Test VALUES(20,'Jerry')
INSERT Test VALUES(23,'Angie')
INSERT Test VALUES(25,'Kim')
INSERT Test VALUES(37,'Jerry')
INSERT Test VALUES(48,'Bill')
INSERT Test VALUES(22,'Sue')
INSERT Test VALUES(32,'Steve')
INSERT Test VALUES(82,'Bertha')
INSERT Test VALUES(59,'Ethel')
INSERT Test VALUES(99,'Rudolph')
INSERT Test VALUES(73,'Zelda')
INSERT Test VALUES(68,'Britney')
INSERT Test VALUES(51,'Rob')
INSERT Test VALUES(88,'Jane')
GO
DECLARE @age INT
DECLARE @Age2 INT
SET @aGE2 = 88
set @age = 88
SELECT @age2 = @age2
declare @SQL VARCHAR(8000) --Not here either
SET @SQL = 'SELECT Name
FROM dbo.Test
WHERE age = '+str(@Age)
-- Bad plan
set statistics io on
SELECT Name
FROM dbo.Test with (nolock)
WHERE age = @age
-- Good plan (oh really?)
SELECT Name
FROM dbo.Test
WHERE age = 88
EXEC (@SQL )
set statistics io OFF
--Jeff Moden
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply