unpartitioned view faster than partitioned?
-
here's the sitch (names simplified):
create view vw_revenue_CURRENT as
select col1, col2, ... col20 from revenue_200605
go
create view vw_revenue as
select col1, col2, ... col20 from revenue_20020101
union all
select col1, col2, ... col20 from revenue_20020201
...
union all
select col1, col2, ... col20 from revenue_20060605
go
I'm trying to move away from the "_current" model because it involves monthly maintenance. I know the VW_REVENUE table is properly partitioned, because I can insert into it. There are about 5 million rows in each of the underlying monthly tables.
I have a query that I'm trying to ween off the _current model. I ran it a few times to see how fast it was going. 0:27 just after a dbcc freeproccache, and 0:02 if it's fresh in memory. When I started using the partitioned view "vw_revenue", I figured I would get similar times, but I didn't. 0:37 just after a dbcc freeproccache and 0:05 if it's fresh.
I don't understand why the times would differ AT ALL. SQL Server will only be looking at the most recent revenue table in either case. Is there a lot of overhead involved with validating the partitioned view or something?
Frustrating.
Greg
-
Here's an update... I checked out the estimated query plan for one of the queries that I'm trying to get to use the VW_REVENUE view and it's not considering the fact that I've got the partitioning column in the where clause.
IOW, the partitioning column (the one that has the check constraint on it in all the underlying tables) appears as the first statement in the where clause (WHERE fiscal_period_id = 64) which should tell the query optimiser to only look at the table that has 2006/04 data in it. When I run the estimated execution plan though, it's looking at ALL the underlying tables back to 2002. This has to be why it's taking so much longer than just querying the table itself...
So now my question is... Why isn't the query optimiser only looking at the one underlying table in this situation? It's like SQL isn't recignizing VW_REVENUE as a partitioned view. Do I have to refresh something?
Thanks,
Greg
-
Greg,
Obvious question but is the CHECK constraint on the partitioning column disabled? Just thought I'd as just in case.
I can't really explain why it would ignore the partitioning column otherwise. On the otherhand, if SQL Server was really performing its query across every single table, all the way back to 2002, I'd expect it to take even longer than your posted times.
It seems that the performance is only 0.1 seconds slower than when you query the tables directly. Initially I thought that there might be a small (one-time) overhead for the optimizer to examine the check constraint to determine which table it has to query from.
Might be worth updating the statistics.
Sorry can't help much with this problem.
-
Thanks Karl.
My times are written in min:sec format, so the difference is 10 seconds, not .1 sec.
All Check constraints on the table are ENabled.
Yeah, if it was scanning all tables, I think it would take a LOT longer as well. Like I said though, the 2002 tables are showing up in the query plan, but only with a "touch" (0% towards the query total). Not sure why they'd show up at all.
-
I did wonder why you were worried about a 0.1 second difference
.Even so, the 10 seconds difference shouldn't imply that it is scanning all tables. That would take a lot longer.
Perhaps, it has to check the CHECK constraints on each table in order to determine whether to select from that table. But that shouldn't take 10 seconds either.
I can't think of anything specific at the moment but I'll have a think about this and talk to a couple of colleagues - see if they have any thoughts on it.
In the mean time, it might be worth checking fragmentation on the table indexes, update statistics, and such.
-
Not sure which change did the trick...
I dropped all check constraints on the underlying tables.
I dropped the view.
Recreated all the check constraints.
Recreated the view.
Updated statistics on underlying tables.
I'm now getting similar times when referencing the part.view and the underlying table itself.
Thanks for the suggestions! Call off the dogs.
Greg
-
Thanks for the update Gregory, that one was confusing for me. I'm guessing you had some bad plan cached.
One thing to try next time is a DBCC FREEPROCCACHE to flush all plans and be sure you're getting a new one the first time.
-
The key part is that when the CHECK constraints are created, they perform the 'check existing data on creation' step. If you build them with EM, that isn't done by default.
You can test this yourself by dropping the view, then dropping and recreating the constraints using WITH NOCHECK (or use EM and leave the 'Check Existing Data on Creation' box cleared), then creating the view. When you check your plans, all tables will be search regardless of WHERE clause. Repeat the process using WITH CHECK (EM: check the box), and the partitioned view will work properly.
-Eddie
Eddie Wuerch
MCM: SQL -
Yes I read somewhere that you musn't attempt to modify or maintain partitioned views through EM. I sort of wish I'd had some of this dialogue a few months ago when I was scratching my head over pv's.
[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