Quicker Cursor or Table Variable

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
BHave 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
"Ben" <Ben@.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
>|||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.
"Ben" <Ben@.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
>