Quicker Cursor or Table Variable

16 Dec 2005 9:35 AM

Ben

Hi

I have a large update batch to make on our database which will run overnight
when no users are logged in.

I always use a Table Variable instead of a Cursor to conserve resources, but
in this case resources are not a problem but speed is.

Which would be quicker: cursor or table variable, also would I get a
performance benefit from running the batch within a Stored Procedure rather
than Query Analyser.

Thanks
B

16 Dec 2005 9:43 AM

Hilary Cotter

Have you looked at the execution plan used in your batch update. This should
pinpoint where the problem is.

Use a binary approach to this. In your batch write print statements which
will display datediff statements throughout the batch. This way you will
know which portion takes the longest.

I think you will find that local table variables with indexes (primary key
constraint) offer the best performance. You will probably also find that
using one or more stored procedures offers better performance as well.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Show quote

"Ben" <B**@Newsgroups.microsoft.com> wrote in message
news:uz1niRiAGHA.916@TK2MSFTNGP10.phx.gbl...
> Hi
>
> I have a large update batch to make on our database which will run
> overnight
> when no users are logged in.
>
> I always use a Table Variable instead of a Cursor to conserve resources,
> but
> in this case resources are not a problem but speed is.
>
> Which would be quicker: cursor or table variable, also would I get a
> performance benefit from running the batch within a Stored Procedure
> rather
> than Query Analyser.
>
> Thanks
> B
>
>

16 Dec 2005 9:46 AM

Uri Dimant

Ben

I'd understan you if you ask what is a difference between a table variable
and a temporary table?
How does it relate to the cursors?

Try to avoid using cursors because it may hurt a performance , insead use
SET BASED process to update a table

If you show us what you are trying to accomplish , we van suggest something
more useful.

Show quote

"Ben" <B**@Newsgroups.microsoft.com> wrote in message
news:uz1niRiAGHA.916@TK2MSFTNGP10.phx.gbl...
> Hi
>
> I have a large update batch to make on our database which will run
> overnight
> when no users are logged in.
>
> I always use a Table Variable instead of a Cursor to conserve resources,
> but
> in this case resources are not a problem but speed is.
>
> Which would be quicker: cursor or table variable, also would I get a
> performance benefit from running the batch within a Stored Procedure
> rather
> than Query Analyser.
>
> Thanks
> B
>
>