March 22, 2006 at 2:01 am
I don't want to post loads of code so:- I have partitioned view where the table key columns are
c1 varchar(40), c2 char(8) these columns make the PK for each table, c1 is unique and c2 is the partitioning column e.g. '20060303' etc.
Data loads fine and I have no errors - everything hunky dory - but if I do a select from QA which will return 1 row from the view e.g.
SELECT NULL FROM dbo.vw_view WHERE ( c1='xxxxxxxxyyy' and c2='20060313')
this works fine and goes to the base table
-----------
NULL
Table 'table_20060313'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
however, in a proc using any combination of parameters/ casting parameters to variables etc. the query hits all the tables in the view.
-----------
NULL
Table 'table_20060312'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'table_20060313'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'table_20060314'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'table_20060315'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'table_20060316'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'table_20060317'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'table_20060318'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
the plan is far too large and ugly to post!!
Anyone else reproduce this / found this ?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 23, 2006 at 1:24 pm
I've had the same problem in the past when I forgot to create any of the CHECK constraints on the partition columns using the WITH CHECK clause. That's very important.
Try dropping the view, then drop and re-create the CHECK contraint on the partition column in each of the tables, adding the WITH CHECK clause to the ALTER TABLE statement (or, if you're using the visual designer to add the constraint, then be sure to check the 'Check existing data on creation' checkbox). Then create the view.
-Eddie
Eddie Wuerch
MCM: SQL
March 24, 2006 at 1:46 am
Been there - the views are fine and everything works spot on when the queries are not parameterised - it appears to me that the optimiser is unable to select an optimal query plan when a parameterised select goes against a partitioned view within a stored procedure.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 13, 2006 at 7:41 am
I'm in a similar spot. I wouldn't expect the optimiser to look at any tables except the one(s) that I specify in the where clause on the partitioning column, but when I do a query plan, it's showing a "touch" (0% of plan time) on all the tables excluded. WTF?
I think this is causing a lot of overhead. I'm quickly becoming less keen on the whole idea. Someone please defend partitioned views and tell me what I'm doing wrong...
Greg
June 13, 2006 at 10:43 am
That 'touch' is part of normal query resolution: the table schema is validated during parsing - which occurs before the WHERE clause is hit. The table data is not queried. Thus the 0%. It's a query against dbo.syscolumns. Not a lot of overhead, IMHO.
The schema resolution for Distributed Partiioned Views can be switched off if those views are implemented on Enterprise Edition. That will prevent the connection to any server that does not explicitly hold data necessary for the particular query as defined by the partitioning key. The setting is called Lazy Schema Validation. Flipping that bit off requires changing a value in the system table row that holds the definition for the linked server. Check BOL for full details and caveats.
It works great. A demo I've run several times while justifying DPV to various clients: I set up DPVs between multiple servers. Then, sitting at one of the servers, I would execute queries that hit all the servers involved, and show the query plans that displayed all the servers were hit. I would then reach down and pull out the network cable going into the server, and hand it to the leading executive or loudest naysayer, with a comment like "That was our connection to Atlanta, and it went down again. Up here, we still need to see our data." Then I pop off a few queries that hit only the local server, which run just fine. The distributed queries lock up, of course, but the app would continue to run for most users when a connection dies.
-Eddie
Eddie Wuerch
MCM: SQL
June 14, 2006 at 3:23 am
hmm, interesting, I've moved on to another contract now but I'm told the pv's are working a dream. I wasn't too concerned but it was the fact that the plans changed when I dropped the queries into a procedure .. you'd sort of expect them to work the same but hey ho !!
So Gregory, yup they work very well .. I will check out the tip on my test systems.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 14, 2006 at 8:50 am
I'm going to start a new thread for this, but wanted to post something here as well...
Our problems are a little different as I'm not having trouble ONLY when I reference the view in a sproc, rather, all the time.
In this string, someone mentions that the tables are "touched" for schema validation and what not, but that the touch wouldn't amount to any significant overhead. I'm not seeing this. In my estimated execution plan, I'm getting 1% overhead on all of 60+ tables that comprise the partitioned view when the WHERE clause should be pointing it to only 6 of those 60.
Here's a quick piece of code for you to run to see what I mean.
create table ul1 (
mon int not null,
tue int not null,
sumpin varchar(32),
constraint [pk_ul1] primary key
(mon, tue),
constraint [ck_ul1] check (mon = 1)
)
go
create table ul2 (
mon int not null,
tue int not null,
sumpin varchar(32),
constraint [pk_ul2] primary key
(mon, tue),
constraint [ck_ul2] check (mon = 2)
)
go
create table ul3 (
mon int not null,
tue int not null,
sumpin varchar(32),
constraint [pk_ul3] primary key
(mon, tue),
constraint [ck_ul3] check (mon = 3)
)
go
create view vw_ul as
select mon, tue, sumpin from ul1
union all
select mon, tue, sumpin from ul2
union all
select mon, tue, sumpin from ul3
go
insert into vw_ul (mon, tue, sumpin) values (1, 1, 'asdf')
go
insert into vw_ul (mon, tue, sumpin) values (1, 2, 'asdf')
go
insert into vw_ul (mon, tue, sumpin) values (1, 3, 'asdf')
go
insert into vw_ul (mon, tue, sumpin) values (2, 1, 'asdf')
go
insert into vw_ul (mon, tue, sumpin) values (2, 2, 'asdf')
go
insert into vw_ul (mon, tue, sumpin) values (2, 3, 'asdf')
go
insert into vw_ul (mon, tue, sumpin) values (3, 7, 'asdf')
go
insert into vw_ul (mon, tue, sumpin) values (3, 9, 'asdf')
go
select * from vw_ul where mon = 2 -- ctrl+L for execution plan
-------------CLEAN UP:------------------------
drop view vw_ul
go
drop table ul1
go
drop table ul2
go
drop table ul3
go
I thought I had this problem licked yesterday, but at second glance, my testing was flawed and today I'm right back where I started which is using a partitioned view that's acting like a regular old crappy view.
Thanks, Greg
June 15, 2006 at 8:42 am
Ensure that the constraints are being created using WITH CHECK instead of WITH NOCHECK.
You can see how this affects your sample code by doing the following:
1. Run your sample above to the SELECT statement, and check the plan (should be OK).
2. Drop and recreate one of the constraints using WITH NOCHECK (this is what happens if anybody fiddles with the table or its constraints using EM):
ALTER
TABLE ul3 DROP CONSTRAINT ck_ul3GO
ALTER
TABLE ul3 WITH NOCHECK ADD CONSTRAINT [ck_ul3] CHECK (mon = 3)GO
3. Check the plan and you'll see that, although your constraints are all in place, the view is treated like a plain old view.
4. Drop and recreate the same constraint explicitly stating WITH CHECK, then drop and recreate your view. The plan should then work properly.
ALTER
TABLE ul3 DROP CONSTRAINT ck_ul3GO
ALTER
TABLE ul3 WITH CHECK ADD CONSTRAINT [ck_ul3] CHECK (mon = 3)GO
DROP
VIEW vw_ulGO
CREATE
VIEW vw_ul .....
-Eddie
Eddie Wuerch
MCM: SQL
June 16, 2006 at 3:14 am
I have to be honest that I partitioned the partitioned views, rather than going for one big view I created a series of views with a hierachy for searching and handled the logic in the procs to optimise the searches ( if that makes sense ? ) I admit the downside is recreating several views every night instaed of one but it did optimise the performance - in fact searches were quicker from the views than against the native tables, so the users told me.
My PK was three columns wide with the check constraint the last column. I created a secondary index on the first two columns which was more useful - in most of my cases ( I had two sets of partitioned views across two related tables ) the partitioning column was not reqd in the searches so secondary indexes were much more efficient - less i/o etc. There was another thread at the time which discussed putting a secondary index on the clustered PK of a table to minimise i/o for searches. I found this to be the case in my pv's
Don't know if any of this helps?
I'm looking back at some of my test docs and I note that querying the view through a proc appears to visit each table - however if you examine the i/o it tells a slightly different story. I have a note that I consider the graphical plan results to be in error. My understanding is that the query needs to visit each table just to check the schema, in the case of secondary index scans/searches an i/o of 3 shows that the index of the index was searched but not the actual index leaves, if that makes sense?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply