Table variable

Hi,

I am trying to use 'Table' variable to delete some records from another table.

Following are the steps:

1. Declare @t1 as Table
2. Insert records @t1
3. Delete records from a Table TbResource
using the following query:

Delete TbResource
From TbResource AS tr
Inner Join @t1 AS t1
on tr.c1 = t1.c

But this delete query takes lot of time, even if there are just 20 records in @t1.

Question :
1. I am unable to figure out the problem. Can someone help me by suggesting the better way.
2. How does 'Table' variable store the data.

u can use creating temporary table or can use self join.

using temporary table option:

create table #t2
(itemid varchar(50))
insert into #t2(itemid) values('rowdel')
DELETE from TbResource from TbResource a inner join #t2 on a.itemid=#t2.itemid
drop table #t2
go

from BOl :

table data type is like temporary tables and can be used to store a result set for later processing. This data type can only be used to define local variables of type table
The format of the rows stored in a table variable or returned by a user-defined function must be defined when the variable is declared or the function is created. The syntax is based on the CREATE TABLE syntax


i also checked creating table datatype variable and its running perfectly.
So Nitin could u post the script which u r running so that we can trap the cause taking extra time to execute the delete statement for 20 rows.

It might help if you add a primary key to the table variable (see BOL "table data type" for the syntax) that covers the column on which you're joining. I would also guess that the user table doesn't have the right indexing for join your using.

As Adrian mentioned, check if you have an index on tbResource.c column.

I don't think the problem is lack of pk on table variable, because the same problem occurs even when there are just 20 rows to delete. Even if it would be much bigger number of rows, execution would scan table variable and most probably use loop join with tbResource.

Temp table doesn't have any advantage over table variable in situation described, unless you need to delete huge number of rows.

Here is the sample script

Declare @t1 as Table
(
@var as Uniqueidentifier
)

Insert into @t1
Select @id from TbResource

Delete TbResource
From TbResource AS tr
Inner Join @t1 AS t1
on tr.id = t1.var

Lets say if i do the selection instead of delete it works perfectly fine.

Does table variable lock the records?

Hi mmarovic,
I just gave the temporary table option to check whether the time issue gets solved in this option or not.
I gave the option irrespective of the no. of rows to be deleted.

You could also try an EXISTS or IN clause, which should take less time to prepare than a JOIN, hence less chance of locking issues (usual reason for slow deletions).

DELETE TbResource
FROM TbResource
WHERE EXISTS (SELECT @t1.c FROM @t1 WHERE TbResource.c1 = @t1.c)

DELETE TbResource
FROM TbResource
WHERE TbResource.c1 IN (SELECT @t1.c FROM @t1)

ranjitjain: Cool, I am discussing the solution. I didn't mean you should not offer alternative.

nitingulati7: That's strange. I have no experience with uniqueIdentifier data type. I don't know if there is an issue related to that data type. However, delete can be slowed down because of:

1. Blocking issues (waiting selects to release shared locks)
2. Too many indexes (each index has to be updated when row is deleted)

plus maybe something related to UniqueIdentifier data type I don't know about.

nitingulati7: Can you please post the execution plan?

How do i load the attachment?

hi nitin u cant attach but just copy the output and paste it here
Mmarovic, Plz dont mind my post as i was not at all hot. u all r experts, i learn from u so why i ll be harsh.
i just gave explanation and nothing else.

ranjitjain: Ok, cool, sorry for misunderstanding.

Hey I didn't get any solution to my problem..

1. I cannot post the execution plan as cannot attach any image, is there a way in which i can get the execution plan in a text format.

2. My Question was does Table variable hold some locks on certain records that cause the problem.

3. In the query below "Delete" is causing the problem.

Declare @t1 as Table
(
var1 as Uniqueidentifier
)

Insert into @t1
Select @id from TbResource

--Problem begins here....
Delete TbResource
From TbResource AS tr
Inner Join @t1 AS t1
on tr.id = t1.var1

run:
Set ShowPlanText on
go
<your query>