Buffer this!

A learned colleague of mine coded a process that attempted to access the same row on a DB2 table 375 million times per run. (It was a code lookup). When I pointed this out he dismissed my advice by insisting that it didn't matter because the data would be sitting in a DB2 buffer anyway so there would be no I/O cost.

What he didn't reckon on was the CPU path length of the DB2 SELECT statement he was using. It is a long way from Cobol to DB2 and back again even when DB2 doesn't do any I/O. When we changed the process to use an in-core table, the CPU usage dropped from several hours to several minutes.

The moral is: sometimes a little knowledge is more dangerous than no knowledge at all.

So you want to do a 7 table join?

My usual advice when faced with a query like this is to break it up into simpler parts. Just because you can do something with a single query, doesn't mean that you should. Temporary tables (or QMF's SAVE DATA) are an excellent way to achieve this.

How long should a process take?

"How long is a piece of string?" may be the flippant answer, but it's not a very useful one.

It is possible to predict, with a fair amount of accuracy, how long a process will take if you know how many records must be processed, what is being done to them, what type of machine it is running on, how many i/o buffers are available etc etc

Or, you can run the input file through DFSORT or other high performance utility.

If DFSORT can process a file in 10 minutes (even if it's not doing exactly the same processing...most business applications are I/O bound) and my Cobol program can do it in 15, then I don't spend much time tuning the Cobol because it is probably about as good as it is going to get.

That is just a rule of thumb of course. Your mileage will vary.