SQL Server Performance

Table variable

Discussion in 'General Developer Questions' started by nitingulati7, Apr 25, 2005.

  1. nitingulati7 New Member


    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.
  2. ranjitjain New Member

    u can use creating temporary table or can use self join.
  3. ranjitjain New Member

    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
  4. ranjitjain New Member

    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.
  5. Adriaan New Member

    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.
  6. mmarovic Active Member

    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.
  7. nitingulati7 New Member

    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?
  8. ranjitjain New Member

    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.
  9. Adriaan New Member

    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)
  10. mmarovic Active Member

    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.
  11. mmarovic Active Member

    nitingulati7: Can you please post the execution plan?
  12. nitingulati7 New Member

    How do i load the attachment?
  13. ranjitjain New Member

    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.
  14. mmarovic Active Member

    ranjitjain: Ok, cool, sorry for misunderstanding.
  15. nitingulati7 New Member

    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
  16. mmarovic Active Member

    run:
    Set ShowPlanText on
    go
    <your query>

Share This Page