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: Frank Solomon <sysfrank at pop dot uky dot edu>
Subject: RE: return only a subset of a result
Date: May 22 1998 11:49AM

Given this problem and the number of rows/GB of data that you specify and
using perl/sybperl as the client language, and assuming that your database
is not very volatile (thus dirty reads wouldn't be too much of a problem),
my approach would be:

1. select all the primary keys, put them in an array, 
2. use the array of primary keys to specifically select the records I
wanted    from the database.  

This also assumes that the primary keys are much smaller than the actual
records.  

For example if your primary keys are say 10 bytes long you'd fetch the 1MB
of keys once (most of the time would probably be spent processing the
filter expression).  Then, fetching the "page" of full records for display
would only involve a simple query on the primary key alone which should be
very fast.

This is probably much better than the "set rowcount" method which processes
the entire "where expression" for each fetch.  It's probably also better
than using a cursor which might hold read locks on the data pages or be
slow.  

A pure SQL version of this might put the primary keys of the result set in
a temporary table with a monotically increasing primary key (identity
column?) which could be selected on; that way you wouldn't need to download
the primary keys to the client if you had a slow network connection.

Of course the data might change between the two "reads".  But then, genomes
probably don't change that often :-)

Frank

At 12:31 PM 5/22/98 +1000, you wrote:
>Well :) If it's post then all I need to do is loop through and skip and
>extract the rows that I want. Not much else you can do I suppose.
>So the time consuming part is the SQL itself. I am working with a
>> 40Gb database from which an average select statement (with filtering)
>will give me about 100,000 rows in return. So my aim is to not show
>everything in 1 go but show them in pages of, say, 10 or 20, at a
>time. Now if there's a way for me to specify which rows of the final
>result set that Sybase will return to me then that'll be idea. It'll be the
>most time efficient way to do it. But can it be done? That's the question.
>
>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              |
>---------------------------------------------------------------------------
>
>On Friday, May 22, 1998 11:55 AM, Leigh Moncher [SMTP:dvor@voicenet.com]
wrote:
>> Okay okay...  And yes it is an interesting problem....  Didn't quite
>> catch the idea of the program there...  Is your issue post evaluate 
>> (i.e.working the result set) or with the actual turnaround of returning
>> the rows?
>>  
>> At 09:45 AM 5/22/98 +1000, Albert Ng wrote:
>> >Exactly! Thank You Tim :) My SQL can be as refine as possible but
>> >that's not the point. The point is for ANY SQL result, I don't wanna
>> >so everything to the user in one go, 'cuz that could take forever to do.
>> >So I was trying to PAGE the results into pages of 10 or 20 or "N".
>> >Now if I process everything it'll miss the point of paging it. So ........
>> >what do you think?
>
>
>

************************************
           Frank Solomon
       University of Kentucky
PGP Public key available via finger
   http://www.uky.edu/~sysfrank
------------------------------------
        Life begins at 180
************************************