Home » Developer & Programmer » JDeveloper, Java & XML » Query runs slow inside class file (Oracle/Unix, Websphere/Windows)
Query runs slow inside class file [message #299387] Mon, 11 February 2008 15:54 Go to next message
guestuser1004
Messages: 6
Registered: February 2008
Junior Member
Hi Guys,

The below query runs with in 5 Second in Oracle SQL+, but take 5 minutes to run inside my Java code...Any insight on this.


SELECT clientcode,formtype,scandate FROM ml_bsl_s where (clientcode NOT IN (SELECT CLNT_CD FROM tb_bsl_client ));

I am having a Oracle connection String inside My class file and executing the query.
Re: Query runs slow inside class file [message #299431 is a reply to message #299387] Tue, 12 February 2008 00:59 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
A query does not run within a class. It always runs in the database.
Which portion of the class consumes the 5 minutes?
Re: Query runs slow inside class file [message #299443 is a reply to message #299387] Tue, 12 February 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you have to review your Java code.
Don't loop on each row, one by one, bulk collect into an array.
Increase prefetch size.

Regards
Michel
Re: Query runs slow inside class file [message #299584 is a reply to message #299443] Tue, 12 February 2008 09:01 Go to previous messageGo to next message
guestuser1004
Messages: 6
Registered: February 2008
Junior Member
Frank,

I am executing the query from my class file.

Its this below part

clientcode NOT IN (SELECT CLNT_CD FROM tb_bsl_client )

Michel,

I tried array, not sure what prefetch size is and would do.
Could u elamorate it a bit.

Thanks

Re: Query runs slow inside class file [message #299585 is a reply to message #299584] Tue, 12 February 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/search?remark=quick_search&word=prefetch+&tab_id=&format=ranked

Regards
Michel
Re: Query runs slow inside class file [message #299617 is a reply to message #299584] Tue, 12 February 2008 13:41 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
guestuser1004 wrote on Tue, 12 February 2008 16:01


clientcode NOT IN (SELECT CLNT_CD FROM tb_bsl_client )


Sorry, but (by itself) this is not a query.

Furthermore, you claim that you execute the same query from both sqlplus and java. That means that either you are mistaken, and the two differ, or it is some overhead that is taking time (eg creating connections, whatever).
That is why I asked you if you timed which part of the class takes the bigger part of the 5 minutes
Re: Query runs slow inside class file [message #299618 is a reply to message #299617] Tue, 12 February 2008 13:44 Go to previous messageGo to next message
guestuser1004
Messages: 6
Registered: February 2008
Junior Member
Frank,


Every thing is fast in my java class except when I put this query
SELECT clientcode,formtype,scandate FROM ml_bsl_s where (clientcode NOT IN (SELECT CLNT_CD FROM tb_bsl_client ));


Works good for
SELECT clientcode,formtype,scandate FROM ml_bsl_s,tb_bsl_client where (ml_bsl_s.clientcode = tb_bsl_client.CLNT_CD ));

Re: Query runs slow inside class file [message #299619 is a reply to message #299618] Tue, 12 February 2008 13:48 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
How long does that first query take in sqlplus?
There is not much use in comparing two completely different queries
Re: Query runs slow inside class file [message #299620 is a reply to message #299619] Tue, 12 February 2008 13:48 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
also, how many records does this query return?
Re: Query runs slow inside class file [message #299621 is a reply to message #299620] Tue, 12 February 2008 13:49 Go to previous messageGo to next message
guestuser1004
Messages: 6
Registered: February 2008
Junior Member
The first query takes about 5 secounds in SQLPLUS.

It returns arround 76.
Re: Query runs slow inside class file [message #299622 is a reply to message #299621] Tue, 12 February 2008 13:51 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Use sqltrace in both sessions (sqlplus and java) and compare the outcomes.
Re: Query runs slow inside class file [message #299623 is a reply to message #299622] Tue, 12 February 2008 13:56 Go to previous messageGo to next message
guestuser1004
Messages: 6
Registered: February 2008
Junior Member
Got the below from SQL PLUS

Opration Object Name Table Access
Table Access ML_BSL_S 6
Table Access TB_BSL_CLIENT 138
recursive calls 125
db block gets 0
consistent gets 967
physical reads 933
redo size 0
bytes sent via SQL*Net to client 1129
bytes received via SQL*Net from client 681
SQL*Net roundtrips to/from client 9
sorts (memory) 6
sorts (disk) 0

Not sure about the JAVA SQL TRACE.
Re: Query runs slow inside class file [message #299624 is a reply to message #299623] Tue, 12 February 2008 14:01 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I meant the more thorough version

How many records do both tables contain?
Re: Query runs slow inside class file [message #299625 is a reply to message #299624] Tue, 12 February 2008 14:03 Go to previous message
guestuser1004
Messages: 6
Registered: February 2008
Junior Member
ml_BSL_S Contains 1043 Records
TB_BSL_CLIENT Contains 96000 Records
Previous Topic: SYSAUX for xml repository?
Next Topic: ORA-30625: error when running code
Goto Forum:
  


Current Time: Fri Mar 29 02:37:36 CDT 2024