Re: Working with tables, best method

Walden H. Leverich wrote:

However, the ordering of the tables can have significant impact, especially if you have additional filtering criteria. Which makes sense; if you have a file with order numbers to process, you don't read every record in the order file and chain to the

list of orders to see if you should process it; you read the orders to process and chain to the order file.

Yes, but the better the query-optimizer the less I as a programmer have
to worry about that. The optimizer should know from statistics that the
order-master has 1M rows and the orders-to-process file has 10 rows and
reorder the query to do the select in the right way.

Statistics can make or break an optimizer.

Absolutely. And from what I understand, each release of DB2 adds more real-world statistics so that helps the SQE make ever better decisions. But if you sit down and think about it, optimization can really only be best done for queries; the data-driven nature of business application programming (in which you often have to do completely different business logic based on a setting in a field) doesn't lend itself as well to mechnical optimization. You need a human brain that understands the application to do the work.

At the same time, though, the grunt work of information display can certainly be handled by an engine; in some ways its akin to the fact that you may be able to squeeze a few cycles out of a machine by writing in assembly language, but the development time isn't worth it. Add to that the fact that SQL can spend so much time beneath the HLL layer on multiple-record operations, and SQL becomes the preferred tool.

Joe