Table order in joins for best performance.

Does the order of tables in a join statement make a difference for optimum performance and memory management? I heard that the last join should be the largest table.

For IDS the order of tables in the FROM clause does not matter as the optimizer reorders the tables to minimize the cost of the query. You are using SE, however, and I don't know if its optimizer was updated to that level, but given that SE v7.20 supports data distributions (UPDATE STATISTICS MEDIUM/HIGH...) I suspect that it was. Maybe someone from IBM can speak to that. However, you can test it yourself by running the query different ways with SET EXPLAIN ON; set and see if the query plans different for each. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jul 10, 2012 at 2:07 AM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > Does the order of tables in a join statement make a difference for optimum > performance and memory management? I heard that the last join should be the > largest table. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340e1bfe685c04c477d60d

On Mon, Jul 9, 2012 at 11:07 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > Does the order of tables in a join statement make a difference for optimum > performance and memory management? I heard that the last join should be the > largest table. > Which version of SE are you using? If you are using SE 4.10 or earlier, then it uses a heuristic optimizer and table order can seriously affect performance, even if you've run UPDATE STATISTICS. If you are using SE 5.00 or later, then it uses a (rudimentary) cost-based optimizer and table order is far less critical. SE, however, does not understand any of the more complex variations on UPDATE STATISTICS. It only does a very simple, very fast update. You can run it in a second or less, once a day, unless you've got an incredibly dynamic system. The best ways to check if you are worried about the performance of some SQL are: (1) measure the response time (is it acceptable), and (2) look at the output from SET EXPLAIN ON. The answer for IDS would be different. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --f46d04089131db6d7a04c47a9ba6