Topic
  • 14 replies
  • Latest Post - ‏2014-03-17T11:02:07Z by EarthDog
EarthDog
EarthDog
16 Posts

Pinned topic Slow strsql fast odbc statement

‏2014-03-13T16:49:20Z |
I am having a strange problem on one of my i systems running 7.1
 
I am using jdedwards 8.0 as erp but the issue is not related as i just found out.
 
After some research it came down to this. I run the same simple select in a specific table. First time i run it from one fat client which connects through odbc and the second time i run it through STRSQL.
 
first time it takes 1 sec maximum and second time it takes 4-5 seconds.
 
Debug (STRDBG) shows that when running on server it has an index suggestion that already exists! and odbc machine uses it.
 
I tried droping and adding the index from both erp and from navigator but results are the same.
 
if i run the same sql from STRSQL for the same table but on our production library it runs fast like odbc case.
 
sorry for my ignorance but iam not even close to being a guy that knows as400...
 
Thanks
  • tomliotta
    tomliotta
    49 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-14T01:06:44Z  

    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.

    Tom

  • EarthDog
    EarthDog
    16 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-14T06:07:22Z  

    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
    krmilligan
    450 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-14T14:23:21Z  
    • EarthDog
    • ‏2014-03-14T06:07:22Z

    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.

     

    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.

  • EarthDog
    EarthDog
    16 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-14T14:47:59Z  

    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.

    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
    krmilligan
    450 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-14T18:41:55Z  
    • EarthDog
    • ‏2014-03-14T14:47:59Z

    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/

    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
    bjbbarker
    18 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-14T19:56:26Z  
    • EarthDog
    • ‏2014-03-14T14:47:59Z

    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/

    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.

  • tomliotta
    tomliotta
    49 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-15T01:01:48Z  
    • EarthDog
    • ‏2014-03-14T14:47:59Z

    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/

    A couple tips if Visual Explain images will be used...

    Visual Explain images

    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.

    Tom

    Updated on 2014-03-15T01:02:15Z at 2014-03-15T01:02:15Z by tomliotta
  • EarthDog
    EarthDog
    16 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-15T14:05:08Z  
    • bjbbarker
    • ‏2014-03-14T19:56:26Z

    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.

    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
  • EarthDog
    EarthDog
    16 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-15T15:19:59Z  

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

  • tomliotta
    tomliotta
    49 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-16T01:30:54Z  
    • EarthDog
    • ‏2014-03-15T15:19:59Z

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

    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.

    Tom

  • EarthDog
    EarthDog
    16 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-16T08:42:03Z  
    • tomliotta
    • ‏2014-03-16T01:30:54Z

    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.

    Tom

    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
    B.Hauser
    282 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-16T10:12:30Z  
    • EarthDog
    • ‏2014-03-16T08:42:03Z

    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?

    An access path (index/LF/key constraint)  can only be used if it has the same sort sequence as the query to be performed.

    Birgitta

  • EarthDog
    EarthDog
    16 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-16T11:47:52Z  
    • B.Hauser
    • ‏2014-03-16T10:12:30Z

    An access path (index/LF/key constraint)  can only be used if it has the same sort sequence as the query to be performed.

    Birgitta

    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

  • EarthDog
    EarthDog
    16 Posts

    Re: Slow strsql fast odbc statement

    ‏2014-03-17T11:02:07Z  

    Solution here: http://dba.stackexchange.com/a/61060/7930