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: sy50027 at milton dot sbi dot com (Simon Yaminian)
Subject: Re: Queries n rows at a time
Date: Jul 17 1998 1:23PM

> From owner-SYBPERL-L@listproc.net Thu Jul 16 18:08:54 1998
> From: Michael Peppler 
> Mime-Version: 1.0
> Content-Transfer-Encoding: 7bit
> Date: 	Thu, 16 Jul 1998 14:54:14 -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:
>  > 
>  > > 
>  > > Perhaps I have misunderstood the question but isn't it enough to run the query
>  > > through $dbh->dbcmd, dbsqlexec (not thru $dbh->sql or siblings). Then run a
>  > > dbnextrow loop and in the loop, work with 200 rows at a time, then get the next
>  > > 20 rows, etc.
>  > > 
>  > > Something like :
>  > > 
>  > > $dbh->dbcmd($query);
>  > > $dbh->dbsqlexec;
>  > > $dbh->dbresults;
>  > > my ($count) = 0;
>  > > while (($col1, $col2, $col3) = $dbh->dbnextrow)
>  > > {
>  > >     $count++;
>  > >     # save $col1, $col2, $col3 into some structure
>  > >     if ($count == 200)
>  > >     {
>  > >         $count = 0;
>  > >         # process the batch of rows
>  > >     }
>  > > }
>  > > 
>  > > 
>  > 	Thank you for the thought.
>  > 
>  > The hangup is that when the query is put together in the dbcmd, the 
>  > dbsqlexec call does not return until the query is completed and this
>  > may take a while. The catch is to break the dbsqlexec call for instance
>  > into a set of "current" and "next" exec calls so we can have a break
>  > in the processing time while also having some data to work with. Again
>  > put all query semantics aside. The idea may seem complicated but it is
>  > in really simple. The key is to maintain a reference point (by Sybperl)
>  > in the submitted query.
> 
> The if the query takes time to complete you should consider modifying
> it, or adding indexes. The time dbsqlexec() takes to return is not
> necessarily linked with the number of rows the query will ultimately
> return.
> 
> Setting rowcount should not affect the time it takes to run the query, 
> but it will limit the number of rows that are returned, obviously.
> 
> This being said, you can't do what you'd like to do because there are
> no SQL primitives to handle that sort of thing directly.
> 
> The only way is to do multiple queries on smaller chunks, using a
> primary key in a where clause.
> 
> 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.

   In setting the rwo count using DBROWCOUNT, I did experience
shorter time in processing the query. This was quite noticeable
when running a wild card query such as

	select * from typically_large_table

and I am not sure whether this could be linked with the way the
table is created/updated. I am going to make more experiments
with this and I shall keep you posted of the results should you
care to know.

   As for having an SQL primitive to serve as the query positioning
handle, you may put that on the wish list. I think that would be a 
nice new edition, don't you think so?!

Regards

Simon Yaminian