Help - performance problem with 'set rowcount' 
Author Message
 Help - performance problem with 'set rowcount'

I'm developing an application using perl 5.001, Sybperl 2b2 and Sybase version
10.  Users query on criteria they select; results are limited to 100 records,
though.  If a user supplies no criteria, or any that has more than 100
matches, I stop after 100 and show just those records.

The code retrieves the first 100 recs in a while loop with
$proc->dbnextrow(); the select, however, apparently has to process the entire
table if there is no 'where' clause.  This is very slow.

To avoid this, I added a 'set rowcount 100' command prior to the select
statement, thinking it would tell the server to quit after the select
processes 100 recs.  This works in the sense that it limits the rows
retrieved, but it doesn't speed up the processing at all (takes 20 seconds,
just like without a 'set rowcount').  In contrast, I can use a where clause
that matches on > 100 recs but substantially less than the 9000 rec total, and
this takes about 4 seconds.

Why doesn't the 'set rowcount' server option speed up an unrestricted select?
 How can I speed up this select?  Any insight greatly appreciated.

--
Dave Hartner - Advanced Systems Engineer
EDS Corporate Information Systems



Tue, 03 Mar 1998 03:00:00 GMT
 Help - performance problem with 'set rowcount'

Set rowcount will just limit the number of rows returned.  If you
are using another clause such as an 'order by' this will cause
the SQL server to sort all rows into a temporary table before
returning the 100 rows.    A better solution may be to use
sybase cursors with a select on a cluster or non-cluster index.


Thu, 05 Mar 1998 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Adaptive 11.5 Vs 'set rowcount'

2. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

3. Problems with 'SET NOCOUNT ON'

4. ADO's Performance vs DAO's Performance

5. Record Set Guru's - Can't Create Record Set error 429

6. Problem using 'SET PROCEDURE TO'

7. How set parameters for 'Select * from Suppliers where SuppID in ('111', '222', '333')'

8. retrieval performance. set rowcount

9. IndexDefs.Add('Help','Please',[ixD2WorksGreat, ixD3Errors])

10. HELP: Replication Problem / OSQL can't connect to it's own server

11. HELP: SQL4.21a 'blocked' locks problem

12. help:'Select for Update' problem


 
Powered by phpBB® Forum Software