Re: Optimizing Many-Table Joins

Michael Mueller RD writes: -> ->Can anyone tell me the tactics that the query optimizer uses for ->optimizing joins, especially queries joining a large number (e.g. >4) ->of tables? What is the max number of tables allowed in a query? ->How does this compare to Sybase's handling? Thanks! Michael, I think the max number of tables allowed in a query is largely irrelevant because the number is larger than anything you would actually want to use. The most efficient way of performing queries on large numbers of tables is to perform smaller queries involving one, two, or three tables, save the intermediate results in a temp table, and run another query based on the intermediate results and other tables. You may need to use repeat this process numerous times. I often start with a query which uses only a single table, and eliminates many rows. The smaller your intermediate results, and the fewer tables involved in each query, the better your performance should be. If you really don't want to use this method, use the set explain command and try adjusting your indexes to work efficiently with your query. But the method using temp tables should still work better. Regards, - Cathy -------------------------------------------------------------------------------- Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (303) 491-1294 Colorado State University Veterinary Teaching Hospital Fax: (303) 491-1205