[ic] Performance different between a direct DATA and a LOOP SEARCH

[interchange-users@icdevgroup.org (Barry Treahy, Jr.)]

This is a real basic question, but it is really making a mess of my 
random component.  Thanks to Mikes little benchmark tag, I was able to 
confirm and isolate the perfomance problem to the random component, 
which I pretty much suspected.  I trying various approachs to improve 
the page generation, but it still sucked at almost 20 seconds.

For kits, I replaced this:

[loop prefix=INV
   random="[control matches 2]"
   search="
     fi=inventory
     st=db
     ra=yes
     ml=[control pool 1000]
     co=yes
 
     ac=0
     sf=price
     op=le
     se=100.00
     nu=1
    "]
  [loop prefix=PRO
    search="
      fi=products
      st=db
      ra=yes
      co=yes
 
      ac=0
      sf=sku
      op=eq
      se=[INV-code]
      nu=0
   "]

with this:

[loop prefix=INV
   random="[control matches 2]"
   search="
     fi=inventory
     st=db
     ra=yes
     ml=[control pool 1000]
     co=yes
 
     ac=0
     sf=price
     op=le
     se=100.00
     nu=1
    "]
[benchmark verbose=1]
[data table=products column=price key="[INV-code]"] 

 and used a half-dozen individual [data] references back to the products 
table and the generation time dropped to .67 seconds.

I fail to see why that second inner loop is killing this module when it 
is targeting a single, specific record whose key is the primary key for 
the table...  I would expect the double loop to run faster, but it 
obviously doesn't...

Why?

Barry

-- 

Barry Treahy, Jr  *  Midwest Microwave  *  Vice President & CIO 

E-mail: Treahy@mmaz.com * Phone: 480/314-1320 * FAX: 480/661-7028