Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22

    Question Unanswered: SQL fast through odbc, slow from console

    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
    Last edited by earthdog; 03-13-14 at 13:47.

  2. #2
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Some more info:

    Its a simple SQL that i am running:

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

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    The only 2 things I can think of are:
    1 - isolation level. you can test that by overruling (just add "with ur" to the query)
    2 - network overhead to transport the result-set to the client (just encapsulate you query with "select count(*) from (your query)"). In that case the query will be executed, but the result set is 1 row.

    Show us the results
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  4. #4
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Quote Originally Posted by dr_te_z View Post
    The only 2 things I can think of are:
    1 - isolation level. you can test that by overruling (just add "with ur" to the query)
    2 - network overhead to transport the result-set to the client (just encapsulate you query with "select count(*) from (your query)"). In that case the query will be executed, but the result set is 1 row.

    Show us the results
    Although your suggestions are "chinese" to me i am starting my research now and i will come back with my findings.

    Thank you very much!

    P.S: Could these affect LIBRARY-wide all the objects or most of them?

  5. #5
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    1) with UR makes no difference at all

    2) Network overhead? But the delay is local when running the statement from STRSQL on the console of the AS400

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Sorry, cannot help you with AS400 specifics.
    AS400 seems to have a 'status aparte' in the db2 world. Dunno why.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  7. #7
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Quote Originally Posted by dr_te_z View Post
    Sorry, cannot help you with AS400 specifics.
    AS400 seems to have a 'status aparte' in the db2 world. Dunno why.
    thanks for trying though!

  8. #8
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Take a look at the 2 results for the same sql between 2 libraries.!!

    Postimage.org / gallery - Snap1, Snap2

  9. #9
    Join Date
    Nov 2011
    Posts
    334
    although i dont kown about as400, but i noticed the difference between the access plans.snap1 takes a index scan while snap2 uses a index probe。

  10. #10
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22
    Quote Originally Posted by fengsun2 View Post
    although i dont kown about as400, but i noticed the difference between the access plans.snap1 takes a index scan while snap2 uses a index probe。
    Yes i know. I have searched more on this and i think that this is causing it

    http://www.dbforums.com/db2/1700701-...ce-issues.html

  11. #11
    Join Date
    Nov 2007
    Location
    Athens, Greece
    Posts
    22

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •