Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

major difference between oracle and ms-access with java code  RSS feed

 
mohan shetty
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I am facing a problem regarding performance.This is all about java code which involves a sql query to fetch huge data(in 1000's)from tables.this code with sql select statement works fine in MS-ACCESS(i.e execution of this query takes in seconds)but the same java code with same sql select statement takes 40 minutes or even more for its execution on ORACLE.The query goes like this
select ="e.dataDate, e.reference,e.status,";

from = DBNames.A_TABLE " p, "
+ DBNames.B_TABLE
+ " t,
+ DBNames.WECTM_ENGINE_ALERT_TABLE
+ " e ";

where1="p.id=t.nl"+ " and t.alertId=e.id and e.engineId=";

where2 ="p.id=t.nh"+ " and t.alertId=e.id and e.engineId=";

This where1 or where2 am passing through a FOR loop to make a sql statement.
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 36406
454
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mohan,
Do you have an index on those tables? The first thing I think of when seeing an order or magnitude difference is that maybe MS-Access has an index while Oracle is doing a full table scan - or worse - 3 full table scans.

You can tune this by running the query through Oracle explain. Explain will tell you what execution plan the database is using. You want to make sure it is using indexes rather than table scans. If it is not, you may be missing an index.
 
Don't get me started about those stupid light bulbs.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!