set rowcount and cursor, which one performs better?

set rowcount and cursor, which one performs better?

Post by L. Tse » Wed, 14 May 1997 04:00:00



Using an example, to list top 5 richest people. This can be handles in three ways.

1. SET ROWCOUNT 5

   select name,sum(cash+stock+property) from table
   group by name
   oder by 2 DESC

2. use cursor to fetech the to 5

3. use sql , something similiar to the following

   select name,sum(a.cash+a.stock+a.property) money from table a
   where 5 > (select count(*) from table b where a.money < sum(b.cash+b.stock+b.property))
   group by name
   oder by money DESC

My question is which on performs better? specially comparing the first two.

Thanks,

Leslie

 
 
 

set rowcount and cursor, which one performs better?

Post by Brian Mora » Wed, 14 May 1997 04:00:00


In general cursors are very slow and should be avoided whenever a set based
solution (ie sql command) exisits.
--
Brian Moran
Spectrum Technology Group NTP
MCSE, MCSD, MCT, SQL Server MVP
President: Capital Area SQL Server UG



Quote:> Using an example, to list top 5 richest people. This can be handles in
three ways.

> 1. SET ROWCOUNT 5

>    select name,sum(cash+stock+property) from table
>    group by name
>    oder by 2 DESC

> 2. use cursor to fetech the to 5

> 3. use sql , something similiar to the following

>    select name,sum(a.cash+a.stock+a.property) money from table a
>    where 5 > (select count(*) from table b where a.money <

sum(b.cash+b.stock+b.property))
Quote:>    group by name
>    oder by money DESC

> My question is which on performs better? specially comparing the first
two.

> Thanks,

> Leslie


 
 
 

set rowcount and cursor, which one performs better?

Post by m » Thu, 15 May 1997 04:00:00



Quote:>Using an example, to list top 5 richest people. This can be handles in three ways.

>1. SET ROWCOUNT 5

>   select name,sum(cash+stock+property) from table
>   group by name
>   oder by 2 DESC

>2. use cursor to fetech the to 5

>3. use sql , something similiar to the following

>   select name,sum(a.cash+a.stock+a.property) money from table a
>   where 5 > (select count(*) from table b where a.money < sum(b.cash+b.stock+b.property))
>   group by name
>   oder by money DESC

>My question is which on performs better? specially comparing the first two.

>Thanks,

>Leslie

My *guess* is that (1) will be fastest followed by (2).  (3) should be slow
because of the imbedded select.  (2) has the added (not insignificant)
overhead of setting up a cursor whereas (1) basically does the same thing
without this overhead.  Of course you won't know for sure unless you
actually try it.

Regards,
Mike

 
 
 

set rowcount and cursor, which one performs better?

Post by Alvin Sylvai » Thu, 15 May 1997 04:00:00


Someone who knows for sure should answer, but you =may= have a
problem using method 1, depending on how and when Sybase sorts
the rows.

I know the equivalent in Oracle will NOT work.  It will pull the
first 5 rows it finds, THEN sort them.  All you'll get is the
descending ordering of 5 arbitrarily selected rows.

Obviously, what you need is to get ALL the rows, SORT them, THEN
stop after printing the first 5.  I'm not sure whether Sybase
acts in this way or not.  I suspect it does, but I'm not sure.


> Using an example, to list top 5 richest people. This can be handles in three ways.

> 1. SET ROWCOUNT 5

>    select name,sum(cash+stock+property) from table
>    group by name
>    oder by 2 DESC

> 2. use cursor to fetech the to 5

> 3. use sql , something similiar to the following

>    select name,sum(a.cash+a.stock+a.property) money from table a
>    where 5 > (select count(*) from table b where a.money < sum(b.cash+b.stock+b.property))
>    group by name
>    oder by money DESC

> My question is which on performs better? specially comparing the first two.

> Thanks,

> Leslie

--
+----------------------+---------------------------------------------+
| Alvin Sylvain        | Your freedom of speech doesn't mean an      |
|                      | obligation to listen.  Mine, however, does! |
| Apologies for anti-spam address: see Org line for domain info.     |
+--------------------------------------------------------------------+
 
 
 

set rowcount and cursor, which one performs better?

Post by David Cro » Fri, 16 May 1997 04:00:00



> Someone who knows for sure should answer, but you =may= have a
> problem using method 1, depending on how and when Sybase sorts
> the rows.

> I know the equivalent in Oracle will NOT work.  It will pull the
> first 5 rows it finds, THEN sort them.  All you'll get is the
> descending ordering of 5 arbitrarily selected rows.

> Obviously, what you need is to get ALL the rows, SORT them, THEN
> stop after printing the first 5.  I'm not sure whether Sybase
> acts in this way or not.  I suspect it does, but I'm not sure.

Sybase will carry out the select (including the sort) as written
without any reference to the ROWCOUNT setting. Once it's got the
result set, it returns only the first five rows to the user. This
gives the results as expected.

Score one for Sybase over Oracle :-)

Dave...

--
The Sybase Contractors Resource Page
http://www.mag-sol.demon.co.uk/Sybase

 
 
 

set rowcount and cursor, which one performs better?

Post by fowell_tre.. » Fri, 16 May 1997 04:00:00


Sybase gets all the results first (i.e. executes the statment
completely) and then applies any rowcount.

Use method 1 - any other method will effectively have to execute the
"whole of" the SQL anyway but will then have the additional overhead of
a cursor /
subquery processing etc.

Trevor


> > Using an example, to list top 5 richest people. This can be handles in three ways.

> > 1. SET ROWCOUNT 5

> >    select name,sum(cash+stock+property) from table
> >    group by name
> >    oder by 2 DESC

> > 2. use cursor to fetech the to 5

> > 3. use sql , something similiar to the following

> >    select name,sum(a.cash+a.stock+a.property) money from table a
> >    where 5 > (select count(*) from table b where a.money < sum(b.cash+b.stock+b.property))
> >    group by name
> >    oder by money DESC

> > My question is which on performs better? specially comparing the first two.

 
 
 

set rowcount and cursor, which one performs better?

Post by LOGICA PLC » Sat, 17 May 1997 04:00:00


Forgive me, but I would be grateful if you could explain how your innermost
query works as I am unfamiliar with this type of syntax. On first reading
the SQL I would have thought that none of the rows in my example data below
would be returned i.e the money for each name is less than the sum(of money
for the table) and thus the no of rows returned is always greater than 5
e.g. for Name 'A' below the money is less than sum(of the money for the
table '3700') and thus select count(*) returns 7 and since 5 is not > 7 no
data would be returned. Have I misunderstood the SQL ??

My sample data :

Name - Money
A - 600
B - 600
C - 600
D - 700
E - 700
F- 300
G - 200


Quote:>Using an example, to list top 5 richest people. This can be handles in
three ways.

>1. SET ROWCOUNT 5

>   select name,sum(cash+stock+property) from table
>   group by name
>   oder by 2 DESC

>2. use cursor to fetech the to 5

>3. use sql , something similiar to the following

>   select name,sum(a.cash+a.stock+a.property) money from table a
>   where 5 > (select count(*) from table b where a.money < sum(b.cash+b.sto
ck+b.property))
>   group by name
>   oder by money DESC

>My question is which on performs better? specially comparing the first
two.

>Thanks,

>Leslie

 
 
 

1. set rowcount and cursor, which one performs better?

Using an example, to list top 5 richest people. This can be handles in three ways.

1. SET ROWCOUNT 5

   select name,sum(cash+stock+property) from table
   group by name
   oder by 2 DESC

2. use cursor to fetech the to 5

3. use sql , something similiar to the following

   select name,sum(a.cash+a.stock+a.property) money from table a
   where 5 > (select count(*) from table b where a.money < sum(b.cash+b.stock+b.property))
   group by name
   oder by money DESC

My question is which on performs better? specially comparing the first two.

Thanks,

Leslie

2. Can't retrieve identity column value on AddNew

3. Set @@rowcount to a local var in one SP from another SP

4. sql

5. Which one would perform better?

6. Q: Deleting rows from Excel W/sheet

7. How can one rewrite this to perform better

8. SET ROWCOUNT set by ODBC...

9. @@rowcount with cursor

10. Output Parameter @@ROWCOUNT NOT Returned with Firehosed Cursor?

11. return multiple cursor to VB and rowcount

12. cursors and sql%rowcount