sybperl-l Archive

From: sy50027 at milton dot sbi dot com (Simon Yaminian)
Subject: Re: Queries n rows at a time
Date: Jul 15 1998 3:35PM

> From owner-SYBPERL-L@listproc.net Wed Jul 15 10:54:42 1998
> From: Michael Peppler 
> Mime-Version: 1.0
> Content-Transfer-Encoding: 7bit
> Date: 	Wed, 15 Jul 1998 07:42:46 -0700 (PDT)
> To: SybPerl Discussion List 
> Cc: sy50027@milton.com
> Subject: Re: Queries n rows at a time
> X-Listprocessor-Version: 8.2.07 -- ListProc(tm) by CREN
> 
> Simon Yaminian writes:
>  > 
>  > > 
>  > > It depends on the query.  If there's a unique field, you can use it to 
>  > > chop your query into blocks.  eg
>  > > 
>  > >    select id, name, address from employees
>  > >    where state='NY' and id>$x and id<=$x+100
>  > > 
>  > > 
>  > 	Thanks for your reply.
>  > Suppose you have the following query:
>  > 
>  > 	select * from employees
>  > 
>  > How can you get those let's say 200 at a time and suppose you have 20,000
>  > employees. The idea is, instead of waiting let's say 20 seconds to complete
>  > the query, wait .2 second at a time and get some data to work with in the
>  > meantime.
> 
> If you have an appropriate index you don't have to wait 20 seconds to
> get data back.
> 
> But assuming you have a unique index on employee_id, you could do:
> 
> set rowcount 200
> 
> $data = $dbh->sql("select * from employee");
> # do something with the data...
> $last_employee_id = $data[$#{$data}]->[0];    # assume employee_id is
> 					      # column 0
> $data = $dbh->sql("select * from employee where employee_id >
> $last_employee_id");
> 
> etc...
> 
> You can put this in a loop to make it nicer.
> 
> Michael
> -- 
> Michael Peppler         -||-  Data Migrations Inc.
> mpeppler@datamig.com    -||-  http://www.mbay.net/~mpeppler
> Int. Sybase User Group  -||-  http://www.isug.com
> 
> 
	Thanks for your reply.

   Not all queries are that simple and squared. Imagine you are running a
set of batch commands with resulting rows expected to be in the thousands.
The idea is to control the number of rows retrieved at a time independent
of the query semantics. Something similar to setting DBROWCOUNT but with 
the difference that you can resume where you last stopped. This entails
establishing a reference point in the query with much resemblance to having
a reference position when navigating within the structure of a data base.

   I welcome all thoughts.

Regards
Simon Yaminian