[mv] Trying to limit SQL queries

["Christopher P. Lindsey" <lindsey@mallorn.com>]

******    message to minivend-users from "Christopher P. Lindsey" <lindsey@mallorn.com>     ******

Hi all,

I'm still struggling with the number of SQL queries that are being made
on a page, so I'm hoping that someone has some input on how to make this
more functional/efficient.  I have a working hack (at the bottom), but
I don't like it.

I have a page that's supposed to list all matching plants within a 
given category.  Initially, I had done it like this:

   <!-- 
      First attempt.  In a database containing 150 plants in the category
      'conifers', this results in 231 SQL queries for a single page.
   -->
   [sql type=list
         query="select distinct p.code, p.description, p.invasive, p.sci_name, h.hardiness_name, c.category_name, s.size_name, p.price from plant as p left join size as s on s.size_id = p.size_id left join category as c on c.category_id = p.category_id left join hardiness as h on h.hardiness_id = p.hardiness_id where c.category_name ='[data session arg]' order by p.sci_name" ]
   <font size=-1>
      [if-sql-field description]
         <a href='[area [sql-code]]'><b>[sql-param sci_name]</b></a>
   [comment]
         <a MV="page" mv.arg="[data session arg]" mv.href="[sql-code]" href="[area [sql-code]]"><b>[sql-param sci_name]</b></a>
   [/comment]
      [else] 
         <b>[sql-param sci_name]</b>
      [/else]
      [/if-sql-field]
      [if-sql-field invasive] 
         <img src="/images/invasive.jpg" BORDER=0 ALT="invasive">
      [/if-sql-field]
      [sql-param hardiness_name] [sql-param size_name] [sql-code]
      $[sql-param price]<br>
   </font>
   [/sql]

As you can see, that was an expensive process.  231 SQL queries is *not*
a good thing, as this should be done with only one.  I next tried using
the search functions in minivend:

   [loop search="
      st=sql
      sq=select distinct p.code, p.description, p.invasive, p.sci_name, h.hardiness_name, c.category_name, s.size_name, p.price from plant as p left join size as s
    on s.size_id = p.size_id left join category as c on c.category_id = p.category_id left join hardiness as h on h.hardiness_id = p.hardiness_id where c.category_name ='[data session arg]' order by p.sci_name
      ml=9999
   "] <stuff> [/loop]

This had the funky result of making [loop-code] expand to the entire row
that matched, and doing anything with [loop-field] still resulted in another
SQL query.  Trying the "se=" stuff instead of rolling my own query only
resulted in errors about minivend not finding the necessary table.  

But realizing that in the above case each row returned tab-delimited fields,
I thought that I could use this to my advantage and embed the search function
in perl:

   <!-- Aha!  Only 1 query / page, but it's still a hack. -->
   [perl interpolate=1]
   $a = "<font size=-1>\n";
   foreach $i ([loop search="st=sql/ml=999/sq=select distinct p.code, p.invasive, p.sci_name, h.hardiness_name, c.category_name, p.price from plant as p left join size as s on s.size_id = p.size_id left join category as c on c.category_id = p.category_id left join hardiness as h on h.hardiness_id = p.hardiness_id where c.category_name ='[data session arg]' order by p.sci_name"] q([loop-code]), [/loop]) {
      @tmp = split '    ', $i;
      $a .= "<a href=\"/cgi-bin/catalog/$tmp[0]\"><b>$tmp[2]</b></a>\n";
      $a .= " $tmp[3] $tmp[5] $tmp[0] \$$tmp[6]<br>\n";
   }
   $a .= "</font>";
   return $a;
   [/perl]

This is definitely more efficient, costing only one SQL query.  Displaying
categories that contain 1500 entries now only takes a few seconds instead
of 15 minutes like the first one.

But I'd really rather do this in native minivend.  Comments?

Chris
-
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