Solved

Index not being used after Java upgrade in same Sybase database

Posted on 2012-12-26
5
721 Views
Last Modified: 2012-12-27
After upgrading to Java 11 driver from Java 8 : one of the query in my sybase production database (12.5.4) has significantly slowed down.
From execution plan, it looks like one index is not being used:

Question: Why the index is not veing used?
Note that same query in the same database is running fine in Java 8.

Query details:
select s.n_client, 
       pa.i_ac, s.n_ac, 
       pa.n_ac, 
       ...
       pa.posn_grp_desc, pa.posn_grp_nme, pa.n_iss_typ_grp, 
  from position_asset pa, auto_client_pf_stat s, auto_load_stage l 
 where pa.i_ac = s.i_ac and l.i_ac = pa.i_ac 
   and pa.d_valn_as_of = s.d_valn_as_of 
   and pa.c_valn_freq like 'D_' and 
   (s.c_freq = 'W' or s.c_freq like 'D%') 
   and (s.d_rpt_run = '01/01/2001'or s.d_rpt_run = '12/31/9999') 
   and s.d_btch = '01/01/2001'
   and l.i_strm = '290J'

Open in new window

Unfortunately, position_Asset is a view as shown below. All other database objects are table.
 
select p.n_ac,
          p.d_valn_as_of,
          p.c_valn_freq,
          ..
          ...
   r.qlty_rat_typ_spstfrn,
    r.qlty_rat_typ_spstlcl,
    r.qlty_rat_typ_md,
    r.qlty_rat_typ_mdlt,
    r.qlty_rat_typ_mdst
 from position p,
      asset    a,
      assetb   b,
      asset    u,                           
      aip_irqr r
where p.i_sybase_ast     =  a.i_sybase_ast
  and p.i_sybase_ast     =  b.i_sybase_ast
  and r.instr_issr_id    =* b.instr_issr_id         
  and r.instr_issr_id_01 =* b.instr_issr_id_01
  and u.i_sybase_ast     =* b.i_undrlyng_sybase_id

Open in new window

Query Plan:
In Java 8: query plan is using index scan in position table (inside the view). See below:

FROM TABLE
  position
            p
  Nested iteration.
      Index : xp1
      Forward scan.
      Positioning by key.
     Keys are:
          i_ac  ASC
          d_valn_as_of  ASC
          c_valn_freq  ASC

However, in Java 11 full table scan is being used:

FROM TABLE
   position
            p
      Nested iteration.
      Table Scan.      Forward scan.
      Positioning at start of table.
      Using I/O Size 16 Kbytes for data pages.
      With LRU Buffer Replacement Strategy for data pages

Note that index xp1 is not being used anymore while the query is calling the view. Everything else in both the query-plans look similar.
0
Comment
Question by:Prameet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
phoffric earned 500 total points
ID: 38722948
It has been over a decade since I worked DB development and optimization issues, so I can only offer some basic generalities. Since you have not received a reply from experts with more current experience (holidays, vacations), I will offer you my few cents.

Cent 1:
Write a stand-alone script (i.e., no Java) to execute the query directly into Sybase; do the timing, and review the plan to see whether the index is being used. Since you have not updated Sybase, then the performance should remain the same. If this is satisfactory, then write a small Java program (if possible, just to make your SQL monitoring easier) to run your query. Monitor and capture the actual SQL query sent to Sybase. Verify that it matches the query in the stand-alone script. If not, then try to rewrite the Java query until your monitor shows the desired query.

Cent 2:
Create stored procedures so that the plan is optimized and not dependent on which Java version you are using. (By the way, I am not sure what you meant by versions 8 and 11; not sure what mechanism you are using to connect Java to Sybase.)

Cent 3:
Also, take a look at this to see if any ideas here may be useful. This link is not intended to be a solution (it doesn't even include Java), but there may be some useful thoughts:
   http://www.experts-exchange.com/Database/Sybase/Q_21240241.html
I assume that you have recreated the index in question to update the statistics.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 38723252
Yes, please tell more about what you mean by "running in Java 8" or "using Java 11" to connect to Sybase, when as far as I know neither does...?

Are you talking to *exactly* the same database in exactly the same ASE both times? When I say exactly, I mean exactly... if you tell me one is a dump & load of another, refreshed weekly, that isn't the same.

There are a lot of things that could be causing this but we are idly speculating until you tell us a bit more about exactly what you're doing.
0
 

Author Comment

by:Prameet
ID: 38723924
Thanks a lot for your time. My apology for being not clear on ja details.

The database enviornment is exactly same - same ASE and same data.

Team is facing this issue having jconn3.jar(from Weblogic11) for sybase db. They are using jconn3 in the ja11 migration. The ja8 app is working fine in production with this query. But ja11-app is causing slowness in production.

Please let me know if you want further details.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 38725417
Then it must be settings in the middleware. I'm more familiar with ODBC than JDBC (which is what you must be using), but there are settings in connection properties like PrepareMethod (which determines whether SQL is sent as is or as "lightweight procedures" which are stored procedures) or SelectMethod (set-based result sets vs row-by-row).

I can see both of those changing what the ASE optimiser chooses. It may be that different versions of your JDBC driver have different defaults. If you explicitly set all connection properties the same in both versions, you should see the same behaviour.
0
 

Author Comment

by:Prameet
ID: 38725936
Thanks. We've found statistics are not up-to-date in one of the table! But still it is not explaining why ja8 is working fine. We have asked management to update statistics.

We will compare connection properties in the meantime to ensure those are identical!

Thanks for all your help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question