Best Performance

I wanna know. I have 2 ways to copy data.
way I
INSERT into A
SELECT * from B
Way II
DECLARE CURSOR ... For
SELECT * from B
WHILE ... BEGIN
/* insert one by one to A
END
...
...
Which the best, way I or II? How much its speed comparison?I. is better. How much faster depends on many things; it could
be 2, 10, or 50 times as fast. Experiment and find out.
Steve Kass
Drew University
Bpk. Adi Wira Kusuma wrote:

>I wanna know. I have 2 ways to copy data.
>way I
>INSERT into A
>SELECT * from B
>Way II
>DECLARE CURSOR ... For
>SELECT * from B
>WHILE ... BEGIN
> /* insert one by one to A
>END
>...
>...
>Which the best, way I or II? How much its speed comparison?
>
>|||Hi
" I - Way" is best
rely on cursor if u dont find any other way to do things
(but there will be always another way to achieve things easily)
refer:http://www.sql-server-performance.com/cursors.asp
Regards
Magesh
"Bpk. Adi Wira Kusuma" wrote:

> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>
>|||Absolutely you should avoid using cursor
So What would I use a Cursor for?
Solution:
In Favour
Two reasons would be when you need to do something for each row in a
result set (usually execute a stored procedure), or when you must
process rows in a specific order.
The first case is probably most common in DBA/admin scripts, for
example to write a script which GRANTs execute permission on all stored
procs in a database
Cursors are mainly used to traverse reach row in the result of a query.
If u would like to check each row and perform a certain operation on
that, u use a cursor. For example u can check for hierarchical queries
in BOL
Most of the time you *can* conditionally perform an operation for each
row using a WHERE clause or using some combination of a WHERE clause
and CASE
expressions. Most of the time that is a better option than using a
cursor. The situations where you cannot do it with set-based DML
statements (SELECT, UPDATE, DELETE, INSERT) are usually to do with
iterative operations that cannot feasibly be defined declaratively -
for example the problem of expanding an adjacency list hierarchy to an
unknown depth. In those cases you may find that a cursor or client-side
code are the optimum solutions.
The other sensible application for a cursor is the - administrative and
management processes that need to execute non-data
manipulation code for each row in a set - for example performing
backups, loading files, sending emails. For data manipulation
operations, however, 99% of the time there are better solutions that
don't require cursors.
Cursors are a bad idea to use as they hold locks on tables for their
duration. They are also not necessary as you can use set rowcount 1 to
loop through records individually. or Use corelated Subquery
With Warm Regards
Jatinder Singh|||A cursor typically takes twice as long as a set-based select, and that's
only for reading. Inserting within the fetch loop will be several
magnitudes slower, because each separate insert forces all indexes to be
updated. A set-based insert allows SQL Server to optimize all of the index
updates.
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:#23PK#LkFHA.1968@.TK2MSFTNGP14.phx.gbl...
> I wanna know. I have 2 ways to copy data.
> way I
> INSERT into A
> SELECT * from B
> Way II
> DECLARE CURSOR ... For
> SELECT * from B
> WHILE ... BEGIN
> /* insert one by one to A
> END
> ...
> ...
> Which the best, way I or II? How much its speed comparison?
>