Native SELECT vs ODBC Select

[bbssupport]

When joining tables, we have discovered a massive performance difference between using the native SELECT WITH command vs ODBC LEFT JOIN on native PxPlus files.

Attached are three test programs. You won't be able to run them without the data files or dictionary, but you'll be able to see what we are doing.

File INF31 has approximately 300,000 records, and the fields being restricted are not within a key so the entire file is being read. APF00 has 310 records, the field being joined on is the primary key of APF00.

With the native SELECT WITH, the process takes 10 minutes to return the results. With the pxpsql.exe command line utility, or the [OBD] tag to read the data via the ODBC driver, results are returned in less than a second.

All three queries return the same results.

Why would this be?