Index not being used after Java upgrade in same Sybase database
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'
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.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
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.
Prameet
ASKER
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.
Joe Woodhouse
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Prameet
ASKER
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!
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.