Update partionned view

Question

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 insertdelete 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
SET STATISTICS IO ON

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

result:
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 ?

Answer 1

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?

Answer 2

0

Are you using date constraints on the three individual tables?

answered Sep 13 '10 at 07:13 AM

Tim
31.5k ● 20 ● 30 ● 116

Tables have all chk constraint on id +date clsutered, we are in SQL 208 r2 standard edition

Sep 13 '10 at 08:04 AM srob

(comments are locked)

Update partionned view

Follow this question

Related Questions