Re: SQL performance - any way to improve?

[Allan DeLeon <allan@sidepage.com>]

******    message to minivend-users from Allan DeLeon <allan@sidepage.com>     ******


Wooo...! [sql list] makes 256 queries in a query
that returns 256 records? Good Lord! On which
versions of Minivend will the bottom option work
on? And has anyone found another way of doing
what [sql list] does?

On Tue, 9 Feb 1999 mikeh@minivend.com wrote:

> ******    message to minivend-users from mikeh@minivend.com     ******
> 
> Quoting Alan Sparks (asparks@nss.harris.com):
> > 
> > I've got Oracle as a database for the store, and performance is generally
> > pretty poor.  In a couple of cases, I have an [sql type=list] type loop that
> > fetches about maybe 255 records (for a billing address list)... the query in
> > Oracle runs in a second or so, but Minivend takes a minute or so to do the
> > same thing.
> > 
> > Are there any ways to improve performance while using an external database?
> 
> Only one I know of -- use your own Perl code directly on a query.
> 
> [sql type=array]
> [control]PERL[/control]
> 
> 	select code,name,price,image from products where category = 'whatever';
> 
> [/sql]
> 
> [perl arg=sql]
> 	for(@$mv_sql_array) {
> 		my ($code, $cat, $price, $image) = @$_;
> 		$price = sprintf ('%.2f', $price);
> 		print <<EOF;
> <A HREF="[area $code]"><IMG SRC="$image"></A><BR>
> <A HREF="[area $code]">$name</A> -- <A HREF="[area order $code]">$price</A>
> EOF
> 	}
> [/perl]
> 
> The problem with using [sql list] is that MV ends up making 256 queries
> instead of one. Since I am not a heavy SQL user, I have not had the
> incentive to optimize for it. I think it could be done pretty easily in
> the special case of "select * from table", but would be difficult to
> do otherwise; perhaps if a field map was passed along, then the query
> could be optimized.
> 
> -- 
> Mike Heins                          http://www.minivend.com/  ___ 
>                                     Internet Robotics        |_ _|____
> Be patient. God isn't               131 Willow Lane, Floor 2  | ||  _ \
> finished with me yet.               Oxford, OH  45056         | || |_) |
>  -- unknown                         <mikeh@minivend.com>     |___|  _ <
>                                     513.523.7621 FAX 7501        |_| \_\
> -
> To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
> email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
> Archive of past messages: http://www.minivend.com/minivend/minivend-list
> 

-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list