updating partitioned views using a subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

updating partitioned views using a subquery

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-05 : 07:14:07

I have the following tables:

create table item1
(tranid int not null, id int not null, partitionid int not null, date datetime, scangap int)
alter table item1 add constraint pk_item1 primary key (tranID, id, partitionid)
alter table item1 add constraint ck_item1 check (PartitionID = 1)

create table item2
(tranid int not null, id int not null, partitionid int not null, date datetime, scangap int)
alter table item2 add constraint pk_item2 primary key (tranID, id, partitionid)
alter table item2 add constraint ck_item2 check (PartitionID = 2)

And the following view:

create view vwItem
as
select * from item1
union all 
select * from item2

To populate the tables, run these statements:

declare @ItemID int, @tranID int
set @tranID = 1
set @ItemID = 1

while @ItemID < 4
begin
	waitfor delay '00:00:02'
	insert into vwItem
	select @tranID, @ItemID, @tranID, current_timestamp, 0

	set @ItemID = @ItemID+1
end

set @tranID = 2
set @ItemID = 1

while @ItemID < 4
begin
	waitfor delay '00:00:04'
	insert into vwItem
	select @tranID, @ItemID, @tranID, current_timestamp, 0

	set @ItemID = @ItemID+1
end

Aim: To get the difference in time between when one record was inserted to the next(datediff(ss, a.date,b.date):



declare @partition int
set @partition = 1

select i.tranID, i.ID, datediff(ss,a.date,i.date)
from vwitem i
inner join (select tranID, id, date, partitionID from vwitem where partitionID= @partition) as a 
	on a.tranid = i.tranid
	and i.id = a.id + 1
and a.PartitionID = i.partitionID


Results:
tranID      ID          Time Diff
----------- ----------- -----------
1           2           2
1           3           4
1           4           4
1           5           4

I created the following query:


declare @partition int
set @partition = 1

update vwitem
set scangap = datediff(ss,a.date,i.date)
from vwitem i
inner join (select tranID, id, date, partitionID from vwitem where partitionID= @partition) as a 
	on a.tranid = i.tranid
	and i.id = a.id + 1
and a.PartitionID = i.partitionID

But get the following error:

Msg 4439, Level 16, State 6, Line 4
Partitioned view 'CRDM_Test Prototype.dbo.vwItem' is not updatable because the source query contains references to partition table '[CRDM_Test Prototype].[dbo].[item1]'.

I understand that this rule applies to partitioned views (from BOL):

'INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.'

But I thought a self-join is more something like so:

select * from a inner join a on a.employeeid = a.managerid

Whereas I am doing a subselect? Also, if this is not possible, then how can I achieve my goal without doing a self join? Also, the size of each partitioned table is about 7/8million rows

Thanks in advance

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-05 : 07:26:35

I have thought of creating a temp table, but was not sure of the performance hit. But that said, SQL Server would create it in memory would it not (just like a subselect, and if enough memory is available)??, therefore, would there be a difference in performance (other than declarations of the temp table)???

Or am I way off with that theory?

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-01-05 : 07:59:37

Hmm, I've tried creating a temp table and referenced that in the update statement, but the statistics show that all the tables are scanned as opposed to only the relevant partition.

select * into #test from vwitem where partitionID = @partition 

update vwitem
set scangap = datediff(ss,a.date,i.date)
from vwitem i
inner join #test as a
	on a.tranid = i.tranid
	and i.id = a.id + 1
	and i.partitionid = a.partitionid
where i.partitionid = @partition

statistics:
Table 'item2'. Scan count 5, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'item1'. Scan count 5, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources