Re: Working with tables, best method

Doug Palme wrote:

Without disagreeing with you Joe and with trepidation that I am opening up a can of sardines here; would you be willing to expound on the argument that I/O is faster than SQL? The reason I ask is there is an intense debate going on here locally over the issue of DDS defined PF's versus DDL defined files and whether SQL is faster or slower than normal I/O.

Any additional information would be appreciated.....

Douglas

Okay, I'll try to be brief, because a lot of this has been hashed over frequently on the lists (you may be able to find some of this by searching on "SQL performance native" and "DDS vs DDL").

The problem is that there are two different issues: DDS vs DDL and SQL vs native. DDS/DDL has to do with how you define your files and in nearly every case, DDL-defined files provide better access than DDS. The primary reason is that DDL checks data on the write to the file, while DDS performs checks on the read, and since (with the exception of logging) most application are at least 80% read (many much higher), DDL outperforms DDS hands down. There are a couple of caveats, a couple of things DDS can do that DDL can't, most are not really big issues. The biggest one is that the concept of a field reference file is pretty kludgy in DDL, but anyway, you can research those issues.

SQL vs native is completely different. Remember, for the most part, SQL can access both DDL and DDS defined files, and so can native I/O. So choosing which language to access your files usually has nothing to do with choosing the method you use to create them. SQL access provides a few features that aren't typically available in native I/O (I think SQL still has better LOB support, for example) but for traditional business purposes, neither one has a functional database0-related advantage over the other.

So finally it comes down to performance, and there the differences are clear. When I last ran my benchmarks (on V5R3, I think), single-record chains, updates and writes all outperformed their SQL counterparts by a wide margin. It's pretty simple to do; write a program that reads a random record out of a file using a chain and using a SELECT statement, put it in a loop for about 100,000 reads and see which wins.

I hope that helps clear things up a little bit.

Joe