Query Optimization via Local Temp Tables

Contributed by Kasey Chang

05 August 2001

Kasey gives us a tip for speeding up those network queries!Query Optimization via Local Temp Tables
© 2001 Kasey Chang

Do you execute query/ies inside a scan loop, and the query involves one or more tables in the network? If so, consider making some local temp tables.

For example, let's say you need to find all the orders related to each customer, and do some processing on each and every order's shipments. You'll probably write

scan tcCustomer:

  sCustID=tcCustomer.CustID
  qOrdersforCust = Query

  Orders.db | OrderID  | CustID   | someotherinfo |
            | Check _O | ~sCustID | Check         |

  Shipments.db | ShipMentID | OrderID | Shipmentinfo |
               | Check      | _O      | Check        |

  endQuery
  q.executeqbe(tcOrderShipments)
  ;then you do some additional processing...
endscan

This works, but it's not very fast, especially when the table sizes become really large. So how do you speed this up?

Consider generating a local version of the orderShipments table first, then filter it out as you need them. The code would then look like this:

qAllOrdersforCust = Query

Orders.db | OrderID  | CustID | someotherinfo |
          | Check _O | Check  | Check         |

Shipments.db | ShipMentID | OrderID | Shipmentinfo |
             | Check      | _O      | Check        |

endQuery
qAllordersForCust.executeqbe(":PRIV:__ALLORD")

scan tcCustomer:
  sCustID=tcCustomer.CustID
  qOrdersforCust = Query

  :PRIV:__ALLORD.DB | OrderID | CustID   | someotherinfo |
                    | Check   | ~sCustID | Check         |

  :PRIV:__ALLORD.DB | ShipmentID | Shipmentinfo |
                    | Check      | Check        |

  endQuery
  q.executeqbe(tcOrderShipments)
  ;then you do some additional processing...
endscan

If the tables are large enough, the difference in performance could be quite significant.

If you need even MORE speed, you can try indexing the temporary table. Remember, you only need to index the fields that will be used in the query/ies.

Even if you don't need to combine multiple tables into one, simply copying the table to your private directory and operating on the local copy can make a tremendous amount of difference in some circumstances.

I have used this technique to cut the execution time of a script by an average of 70%. I hope it will benefit you too.

Side note: You may have noticed that for the temp table, I used :PRIV:__ALLORD.DB as a table name. This is a way to name temporary tables and have Paradox automatically clean them up when you exit. The tables will be there until you exit Paradox normally or until you delete them yourself. Just put them in your :PRIV: directory and start the file name with two underscores. Some versions of Paradox will not allow you to "pick" them from a list when using these tables in a data model though, so you may have to enter the filename manually.

< Prev		Next >

[ Back ]