Home > IT Answers > AS/400 > How Do I Speed Up an ODBC Query of AS400 Table
Kerleyp
0 pts.
 How Do I Speed Up an ODBC Query of AS400 Table
AS/400
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.

Software/Hardware used:
ASKED: November 18, 2005  12:54 PM
UPDATED: November 20, 2009  5:00 PM
RELATED QUESTIONS
Answer Wiki:
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. www.linkersystems.com sol@linker.com 800-315-1174 +1-949-552-1904 from outside of North America
Last Wiki Answer Submitted:  November 18, 2005  1:46 pm  by  SheldonLinker   15 pts.
All Answer Wiki Contributors:  SheldonLinker   15 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

JPLamontre
 0 pts.
 

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.

Tbfleet
 0 pts.
 

…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

TomLiotta
 108,055 pts.
 

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 ?

graybeard52
 3,115 pts.