PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Robb Canfield <robbc at canfield dot com>
Subject: RE: return only a subset of a result
Date: May 22 1998 1:06AM

Glad it helps.

If the record is past the mid-point (if you can determine that) try
reversing the sort order and then take the results and use Perl's sort
operators to re-sort properly. Another possible trick is to select only a
unique field (a primary key) and once you have the range select all fields
using specific queries based on the primary key. 'Select *' is very slow
and in my experience selecting a primary key is much faster.

At 10:57 AM 5/22/98 +1000, you wrote:
>Robb,
>
>  That's a good one :) I tried it and it is an improvement. But for the
>last group of 10 at the end of the result set it's still the same. I am
>actually lopping through the results and skipping to the row that I want
>before processing it. And stopping right after the last row I want.
>
>  Good one :)
>
>Thanks Mate.
>Alb
>---------------------------------------------------------------------------
>|   ANGIS- The Australian National Genomic Information Service |
>|    http://www.angis.su.oz.au      email: albert@angis.su.oz.au   |
>|    Phone: 61-2-9351 7219          FAX: 61-2-9351 5694              |
>---------------------------------------------------------------------------
>
>On Friday, May 22, 1998 10:34 AM, Robb Canfield [SMTP:robbc@canfield.com]
wrote:
>> I am not sure how to do a subset (20-30) but you can limit the number of
>> rows returned by setting 'rowcount' to the maximum number of rows you want.
>> Via ISQL:
>> -- limit selection to 1 row
>>   set rowcount 1
>>   go
>> -- perform query
>>   select * from some_table
>> go
>> 
>> -- Restore normal behavior for rows
>> set rowcount 0
>> 
>> 
>> Returns only the first row. Set 'rowcount' to 0 to return ALL rows
>> (discovered via testing). be ure to reset rowcount after your query.
>> 
>> Maybe you can use this to speed up the operation you need.  You can at
>> least limit the selection to 30 and only take the penalty on the first 20.
>> I have only tested rowcount in limited ways since I just found this
>> yesterday.  Let me know your results.
>> 
>> 
>> At 08:57 AM 5/22/98 +1000, you wrote:
>> >Hi All,
>> >
>> >   I am just wondering is there a way to do the following:
>> >
>> >1. do a select statement with order by - which might result in a few
>> thousand rows
>> >2. but instead of returning all rows, I just want to return, say rows
>> 20-30 of the result
>> >    set.
>> >I know I can make a loop to go through the result set but that's slow. So
>> are there
>> >other smarter ways to do it?
>> >
>> >Thanks :)
>> >Alb
>>
>---------------------------------------------------------------------------
>> >|   ANGIS- The Australian National Genomic Information Service |
>> >|    http://www.angis.su.oz.au      email: albert@angis.su.oz.au   |
>> >|    Phone: 61-2-9351 7219          FAX: 61-2-9351 5694              |
>>
>---------------------------------------------------------------------------
>> > 
>> 
>> ---------------------------------------------------
>> Robb Canfield
>> Canfield Research Group
>>  -- 425.228.6272  --  Fax: 425.228.6273
>