6

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

4
  • 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, 2011 at 10:58
  • @Marian i have added plan
    – garik
    Jul 20, 2011 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, 2011 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? Jul 20, 2011 at 13:23

1 Answer 1

7

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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