Update partionned view

I have a partitionned view with 3 big tables for 1 day each (10,11,12/09/2010) with a date check , (key clustered id, date), When i insert\delete date, the STATISTICS IO show that only the target table for the date is used. When we update the view :

update vw1
set col1=col1+ t.col1
from vw1 v
   inner join #tmp t on t.id=v.id

.. the statistics show that all the tables are used ! Same thing if i delimit the date axe with variables

declare @dtbegin datetime
declare @dtend as datetime

select @dtbegin ='2010-09-12',@dtend='2010-09-12 23:59:59'
update vw1
set col1=col1+ t.col1
from vw1 v
   inner join #tmp t on t.id=v.id
where v.date between @dtbegin  and @dtend

Table 'vw_20100911tmp'. Scan count 0, logical reads 0, physica...
Table '#tmp____________________________________________________________________________
Table 'vw_20100912tmp'. Scan count 1, logical reads 0, physica...
Table 'vw_20100913tmp'. Scan count 0, logical reads 0, physica...

Is it possible to update a partionned view with a join on only the target table of the where clause ?

more ▼

asked Sep 13, 2010 at 07:03 AM in Default

srob gravatar image

1 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

srob, first of all, is there any physical or logical io on the partitions that are not supposed to be touched? I can't see that in the information you provided. It seems that only the table vw_20100912tmp has had anything happen to it (scan count 1) whereas the other seem to have no operations spent on them.

If I understand it correctly, you are doing the "old-school" style of partitioning, namely manually partitioning by creating physical tables instead of using "real" partitions as offered by SQL 2005/2008 Enterprise Edition. This means that the partition elimination abilities of "real" paritioning would not be on offer to you.

What you are seeing is SQL Server taking a quick look at the two tables it doesn't need to access along with the third table it does need to access. It cannot know which of your "partitions" is the right one until access time. With "real" partitioning this would be the case, as the partition function would tell the storage engine that it only needs to access the middle partition in your example.

From what I can see from your example, table "vw_20100911tmp" and "vw_20100913tmp" are not seeing any I/O to speak of, other than a single hit to see if the supplied filter can be satisfied by the clustered index. It then skips them and scans the third table, and only costs you significant I/O for that one.

What edition & version are you on?
more ▼

answered Sep 13, 2010 at 07:23 AM

WilliamD gravatar image

25.9k 17 19 41

OK Standard Edition will not allow you to use the partitioning feature of SQL Server. So the system is basically behaving as it should as you have designed it.

I cannot imagine you will have too many problems with it though - please correct me if I am wrong on that!
Sep 13, 2010 at 08:19 AM WilliamD
Yes we can only use sql 2k8r2 std edition, the select and insert statement have no problem the performance are ok , we have just poor performance with update due to the request on the other tables in the structure... We try then to extend the number of tables to 3000 .. so the performance are critical.
Sep 14, 2010 at 06:54 AM srob

As mentioned, I cannot see there are any problems at present provided by the information supplied.

Please let us know if you are really experiencing problems, and what exactly. If it is blocking as you alluded, please let us know how that is showing itself.
Sep 16, 2010 at 01:11 PM WilliamD
(comments are locked)
10|1200 characters needed characters left
Are you using date constraints on the three individual tables?
more ▼

answered Sep 13, 2010 at 07:13 AM

Tim gravatar image

36.4k 39 41 139

Tables have all chk constraint on id +date clsutered, we are in SQL 208 r2 standard edition
Sep 13, 2010 at 08:04 AM srob
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 13, 2010 at 07:03 AM

Seen: 914 times

Last Updated: Sep 13, 2010 at 07:11 AM