Forums Register Login

Strange issue with JDBC execute performance

+Pie Number of slices to send: Send
hi everyboy:

i met a strange issue in my j2ee project, i got connection from tomcat dbcp(connection pool) when i
call ResultSet resultSet = pst.executeQuery(sql) it's became very slow need 100 seconds sometime,
1, but i execute the SQL in toad is very fast and it's very simple in fact
2, but when i open or refresh other servlet which also need invoke database, the previous page will load successful very fast.

I don't know how to explain these strange issues and what's the root cause.

Thanks for your hint.
+Pie Number of slices to send: Send
Try to check execution plan of the sql query in the database.
Check this when your servlet is firing the sql query.
There could be some issue with the indexes of the database tables involved in the query.
+Pie Number of slices to send: Send
 

Kiran Joshi wrote:Try to check execution plan of the sql query in the database.
Check this when your servlet is firing the sql query.
There could be some issue with the indexes of the database tables involved in the query.



Thanks your reply.
The sql query is very simple just like 'select sysdate from dual' and i executed it in toad or sqlplus, it's very fast.
Just when invoke in servlet it will become slow sometimes, i don't know whether have matter with jdbc driver version or other unknown reason?
+Pie Number of slices to send: Send
Ken,

I have witnessed such an issue where the sql query fired by java program was following a different execution path than the one fired through sqlplus or TOAD. And that's why it was taking time too.There was an index which was not being used by query from java prog.
Our DBA had analyzed that by monitoring the execution path of the two cases. He suggested us to use a particular 'hint' in the sql query & it worked.
So the suggestion is, try to monitor the execution plan.
I assume your sql query is querying some db table and not querying 'dual'.
+Pie Number of slices to send: Send
You are right after tried, thanks a lot.
Our 'DBA'(actually we have no a real one) change DB configuration and set server as dedicated mode.
Then i found i can't use DBCP connection pool and we use normal jdbc, but it need long time to establish a connection.
So, would i ask how your web application apply which connect DB way? What happen to me?

below code is our connect DB now:
====================================
This tiny ad will self destruct in five seconds.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 3676 times.
Similar Threads
Strange behaviour when accessing Oracle 8i table from servlet
sql statement, implicitly open cursor and oracle session...
how can I get Record Count from ResultSet?
Connection and PreparedStatement Relation...??
strange error while executing an insert statement in MS SQL Server
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 09:56:02.