Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 839
  • Last Modified:

Performance question regarding SQL query over AS400 logical files?

I have some multi-member AS400 files that I need to create single-member logicals files in order to execute some SQL SELECT queries (via ODBC or JDBC).  These files have many fields in them that I will never need for my ODBC queries.  I want to have the best performance possible because I think these queries will be used alot.  My questions:  

1)  When creating the single member logicals, should I only include the fields that I will need in the SQL queries?  Or should I just create the logical to include all fields that exist in the physcial file?  Which will cause the application to retrieve the data faster?

2)  Should I use an asterisk to SELECT all fields in the single member files/tables, or should I list out only the fields that I need to be returned in the query? (SELECT * FROM ASFILE1) vs. (SELECT FLD1, FLD2, FLD3 FROM ASFILE1)

I've always wondered this and thought I'd post this to see what comments I get? Thanks, sulzener.
2 Solutions
Dave FordSoftware Developer / Database AdministratorCommented:
Just my two cents ...

Whenever I need to SELECT from a certain member in an old multi-member file, I simply override to the "target" member and then SELECT directly from the table.



Select myColumn1 from MYLIB/MYTABLE.

This way, the optimizer can choose the best access path to use (and it's much smarter than I am).

BTW, since working with multi-member files with SQL is such a pain, I almost NEVER use them. They're antiquated, difficult to deal with, and never necessary.

As for your second question, I almost always specify the columns specifically. Not so much for performance (since I think the "performance-hit" is truly negligible), but because it makes my applications more robust by protecting them from unforseen future databse changes.

I am in agreement with DaveSlash.

If you are unable or unwilling to override to the member and decide to build a permanent logical over all members I would suggest including all fields in the logical, it will not have any performance impact having all fields.
Just make sure the Access path maintenance is set to *IMMED.
This will keep the access path from rebuilding when the file is accessed and will make performance faster.

For question 2, I would recommend always specifying only the columns you want.
The main reason is the database change Dave mentioned.
The other is memory usage, Why bring the entire record into memory if you are only looking for one or two columns.

That is my one cents worth.
sulzenerAuthor Commented:
Thank you both.  BTW, The SQL is via a web application, not native AS400.  I cannot do an override on the web.  It must be single member beforehand.

Note that one potential alternative is SQL CREATE ALIAS to give access to a LF member as a view.

Wherever it's reasonable, I would explicitly list columns. Choose which columns should be in a view; choose which columns should be in a SELECT; etc. (If I'm just running basic interactive SQL to visually scan records, I might use SELECT *.)

By naming your choices explicitly, you reduce the amount of data movement between blocks from DASD into whatever program buffers you are using. Even if there is no technical "program" but you're just running ODBC/JDBC/whatever, data movement into the result set can be reduced significantly. Network comm is reduced, movement into display buffers is reduced, temporary table sizes are reduced, etc.

Further, SQL allows column-level authority checks. Limit the number of columns and you also limit the amount of related overhead.

Minor note... AFAIK, "access path" will matter depending on whether or not the SQL ORDER BY clause matches the key fields for the PF or LF being queried. And by creating LFs (or keyed PFs or SQL indexes) that match expected SQL query ORDER BY clauses, you'll get performance boosts for queries -- there is the corresponding overall performance drain from maintaining the indexes, but that tradeoff that users will let you know about.

As far as queries over LFs vs. PFs, I haven't seen any figures that indicate better or worse performance as far as what fields are included in the file definitions.


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now