Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Programming Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


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.


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.