Pinned topic Slow strsql fast odbc statement
Re: Slow strsql fast odbc statement2014-03-14T01:06:44ZThis is the accepted answer. This is the accepted answer.
First things we'd need to see would be the statements that run in the two environments. Without those, it'll mostly be a list of guesses. And after seeing those, it'd probably be useful to see the environment settings from both clients.
Re: Slow strsql fast odbc statement2014-03-14T06:07:22ZThis is the accepted answer. This is the accepted answer.
Its a simple SQL that i am running:SELECT * FROM testDTA/F74G0904 WHERE ( WFDCT = 'JE' AND WFDOC =166823.000000 AND WFKCO = '00003' AND WFDGJ = 114090 AND WFJELN =1.000000 AND WFLT = 'AA' AND WFEXTL = ' ')The table has around 10.000.000 in both libraries.
GREEN SCREEN - LIB1 - 4 SECS
GREEN SCREEN - LIB2 - 1 SEC
ODBC CLIENT - LIB1 - 1 SEC
ODBC CLIENT LIB2 - 1 SEC
It seems to be library specific and connection specific at the same time.
DSPJOBLOG in the case of "GREEN SCREEN - LIB1 - 4 SECS" suggests an index that already exists. Also as i said tried to drop and create the index.
krmilligan 120000MDWP450 Posts
Re: Slow strsql fast odbc statement2014-03-14T14:23:21ZThis is the accepted answer. This is the accepted answer.
- EarthDog 270003686P
There are different SQL settings that influence the query optimizer's plan decision in the two environments. If you were using Visual Explain instead of the outdated STRDBG command, you would be presented with a listing of all the settings that influence the optimizer and it would be easy to compare the differences.
These settings and the SQL Performance tools are all covered in the DB2 for i SQL Performance Workshop - http://ibm.com/systems/power/software/i/db2/education/performance.html
The DB2 for i Center of Excellence team can also be hired to assist with your SQL performance tuning and education.
Re: Slow strsql fast odbc statement2014-03-14T14:47:59ZThis is the accepted answer. This is the accepted answer.
- krmilligan 120000MDWP
Indeed i am finding out how much of a different beast the as400 is. Unitkl recently i didnt have the responsibility for such things, but for this period i must deal with it.
Imagine i am just finding out the basics of different index types DDS,SQL e.t.c...
Although i understand your logic , what can i present you in order to be a little more specific?
I have just read how to perform a debug session from navigator and ia m browsing the results of the execution plan..
take a look: http://postimg.org/gallery/k0g3qdju/
krmilligan 120000MDWP450 Posts
Re: Slow strsql fast odbc statement2014-03-14T18:41:55ZThis is the accepted answer. This is the accepted answer.
You don't want be viewing the message text with Visual Explain - these are still the outdated messages from STRDBG.
The various settings that influence the optimizer are in the right-hand pane, you'll want to scroll down through those. Clicking around on the steps of the execution plan will allow you to see if a different index is being used. Adding objects names to the Icons with the View task also helps with this. You can click on the "guide feet" at top to access any index advice.
Another good resource would be the indexing strategy white paper linked to: http://ibm.com/systems/i/db2/awp.html
bjbbarker 10000007W420 Posts
Re: Slow strsql fast odbc statement2014-03-14T19:56:26ZThis is the accepted answer. This is the accepted answer.
On your visual explain for the slow SQL, click on the footprints icon and it will show you which indexes it thinks should be built. I would double check the sort sequence it wants to use and then create the index there. We found when we were still using STRSQL that it would use a sort sequence of *HEX and for all of our JDBC/ODBC connections were using *LANGIDSHR. After changing the session attributes for STRSQL to *LANGIDSHR most of our sql statements ran the same.
You mentioned that you are on a AS400. Are you really using 15 year old technology? What version of the OS are you running? 7.1 has some great features for SQL and is much faster (unless you are using STRSQL which is optimized differently). STRSQL is optimized to bring back one page of data for the SQL statement where JDBC/ODBC is optimized to bring back all data for the SQL statement.
Re: Slow strsql fast odbc statement2014-03-15T01:01:48ZThis is the accepted answer. This is the accepted answer.
A couple tips if Visual Explain images will be used...
First, you can size the panes to show sufficient information. (See image VisEx01.jpg.) Test some options like View-> Zoom-> Fit window. The icons can be displayed vertically to reduce the width of the left-hand pane, and the columns can be widened in the right-hand pane to show longer text columns.
And second, click on different icons to see different statistics. That helps show different parts of the entire sequence of your query. (See VisEx02.jpg and VisEx03.jpg.)
When showing images to others, it helps if we can see more.
TomUpdated on 2014-03-15T01:02:15Z at 2014-03-15T01:02:15Z by tomliotta
Re: Slow strsql fast odbc statement2014-03-15T14:05:08ZThis is the accepted answer. This is the accepted answer.
- bjbbarker 10000007W4
No we are not behind in as400 tech :) we use it for 15 years. For example the system in question is an E4B.
Now that you mentioned it i took a look at both indexes which are exactly the same in every thing EXCEPT one thing:
The index were the statement runs fast has the sort sequence table = QSYS.QRUS0401S
In fact as i am looking now ALL my indexes have that in this specific library.
In all my other Libraries and systems its defined as *HEX (None).
Could be this causing my performance problem in the while library?
Could be this a setting in LIBRARY level so every new index always gets this?
In visual explain he proposes to create the index that already exists (with sort option NOT *HEX). Maybe he needs the *HEX index and the existing one doesnt for his needs?
Any light into this?Updated on 2014-03-15T14:09:07Z at 2014-03-15T14:09:07Z by EarthDog
Re: Slow strsql fast odbc statement2014-03-15T15:19:59ZThis is the accepted answer. This is the accepted answer.
So i checked all my AS400s with this command:
select sort_sequence,count(*) from qsys2.sysindexstat group by sort_sequence
What i found out is that ALL libaries in all my systems have "BY HEX VALUE".
ONLY the library where i have the issue has "*LANGIDSHR"
So i believe that my issue has the roots in this. Probably thats why SQE doesnt take under consideration the existing index ....
Re: Slow strsql fast odbc statement2014-03-16T01:30:54ZThis is the accepted answer. This is the accepted answer.
- EarthDog 270003686P
In that case, do your connection attributes match the index sort sequence attributes when you see performance issues with your queries?
For STRSQL, the sort sequence can be set with the SRTSEQ(*LANGIDSHR) parameter or by using F13=Services. For a network connection, it will depend on the client.
Have you created any indexes that use SRTSEQ(*HEX) in that library? You'll need to set the appropriate connection attribute before creating such an index.
Re: Slow strsql fast odbc statement2014-03-16T08:42:03ZThis is the accepted answer. This is the accepted answer.
- tomliotta 100000BBYW
If it is like this, could this be the reason why optimizer doesnt use the index that already exists with the same keys?
And maybe thats why he proposes to create the same index but probably in *HEX?
B.Hauser 1000007U1D304 Posts
Re: Slow strsql fast odbc statement2014-03-16T10:12:30ZThis is the accepted answer. This is the accepted answer.
- EarthDog 270003686P
An access path (index/LF/key constraint) can only be used if it has the same sort sequence as the query to be performed.
Re: Slow strsql fast odbc statement2014-03-16T11:47:52ZThis is the accepted answer. This is the accepted answer.
- B.Hauser 1000007U1D
So this so cause the problem from the first place....
I just double checked my FAT clients ODBC setup and on some i dound that the odbc setting in language tab is NOT HEX