How Do I Speed Up an ODBC Query of AS400 Table

We have a very small piece of software that queries our inventory. once you have selected a product (very quick table has 32k records) and you want to see who has ordered that product it takes minutes (usually about 5) to query that table (830k records) looking for suggestions about how to speed up that query or maybe the DB in general. When you do the same program in a Native AS400 program it is instant.

Answer Wiki

Thanks. We'll let you know when a new response is added.

My first guess is that you are running MS Access or some other program that’s having all the records delivered to it, then processed. Write your query to run as a view or stored procedure. That way, the query will run on the 400, and just deliver the result over ODBC.

Sheldon Linker
Linker Systems, Inc.
+1-949-552-1904 from outside of North America

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • JPLamontre
    You have checked in the 400 with a program. ok. Are you sure you have used exactly the same sql query ? if no, try the sql select with strsql. normaly you reproduce the same problem. If problem comes from DB2, you have the same answer time. In this case, run STRDBG (witout any parameter) before STRSQL, then DSPJOBLOG : you with find a mountain of details, and one (at leats) of the message will suggest you some index. Create them.
    0 pointsBadges:
  • Tbfleet
    We've found that our queries run faster if built using a view of the file that is indexed based on the selection criteria. In some tests, we found the time savings to be substantial. In one case, they query ran in 2 seconds. The original code took more than 15 minutes. This test was over a file containing 3 million records.
    0 pointsBadges:
  • TomLiotta
    ...view of the file that is indexed ... Views aren't indexed (yet). Views and indexes are separate objects. I assume you have some specific meaning that you didn't describe. Any chance you could help the OP by supplying a description? Tom
    125,585 pointsBadges:
  • graybeard52
    1. Check file QUSRSYS/QAQQINI and make sure the value for IGNORE_DERIVED_INDEX is set to *NO. 2. Also check the queries being sent. Is it being sent only one query, or is it sending thousands of queries. If it send lots of queries, and there is a router between, there will be a lot of time lost on router make/break requests. 3. Check your subsystems. When you run it AS400, are you running it interactive or batch ? Different optimization settings occur and sub-systems may have different parameters as well. Many times the ODBC requests get run in QUSRWRK or QSYSWRK, whereas the "native" runs are usually QINTER or QBATCH. Are you on a supported release ? V5R4 or later ?
    3,115 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: