Performance question regarding SQL query over AS400 logical files?

Posted on 2005-05-09
Last Modified: 2012-05-05
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.
Question by:sulzener
    LVL 18

    Accepted Solution

    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.

    LVL 3

    Assisted Solution

    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.

    Author Comment

    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.
    LVL 26

    Expert Comment


    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.

    LVL 26

    Expert Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now