Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table variable:

DECLARE @to_process TABLE 
(
    [Id] [bigint] NOT NULL,
    [SequenceId] [bigint] NOT NULL,
...
)


INSERT INTO @to_process
   (  Id
    , SequenceId
...
   )
  SELECT
    TOP (@recordsToProcess) 
      Id
    , SequenceId
...

in my stored procedure. I have investigated that insert into it spends about 66% of total execution time. enter image description here

How can I improve or optimize my code to speed up my sp execution?

ADDED:

enter image description here

share|improve this question
    
From what I see this operation takes 66% or the current statement, not from the all batch. And this also doesn't mean it's 66% of time, but it's estimated as 66% of cost of the statement. The time could be spend elsewhere (IO, compilation..etc), it's not necessarily that biggest cost is also biggest duration. Can you show us the complete plan? –  Marian Jul 20 '11 at 10:58
    
@Marian i have added plan –  garik Jul 20 '11 at 11:10
    
@garik: I personally see nothing wrong in this statement plan. Are you sure this one is taking most of the time in your procedure? Try using [set statistics IO on] and [set statistics TIME on] when executing the procedure and see if this insert is actually the statement that takes most time. –  Marian Jul 20 '11 at 11:38
2  
I see that you're using TOP but I don't see a sort operator. Does your TOP have an ORDER BY? If Id from the source is a primary key, have you considered declaring a primary key on the @table? –  Aaron Bertrand Jul 20 '11 at 13:23

1 Answer 1

up vote 7 down vote accepted

First thought...

If you have sufficient data to take this much % of the batch, use a #temptable.

When the table variable is used later, it is always assumed to have one row: there are no statistics on this table variable. So if you have several 1000, subsequent plans won't be optimal.

Temporary tables have statistics (and indexes if required etc) and can perform better for larger datasets

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.