aspose file tools*
The moose likes JDBC and the fly likes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark ""select.." in jdbc gives bad performance but not in mysql directly" Watch ""select.." in jdbc gives bad performance but not in mysql directly" New topic
Author

"select.." in jdbc gives bad performance but not in mysql directly

jay desi
Greenhorn

Joined: Jul 02, 2009
Posts: 27
I am using MySQL db , connector/J driver and have a table "testTable" that contains 3 million rows.
Query is of the form:-



When I execute this query directly on mySQL tool and/or mySQL prompt, I get results within 11-12 seconds.

But, in below java snippet:-



It takes on an average 80-85 seconds to execute:



Pl note that I also tried changing my resultset to TYPE_FORWARD_ONLY, but it didnt help. I tried to change to PreparedStatement, but it also
didnt help.

How can I improve performance on JDBC side? Does stored procedure help here? Actually, I have a simple query and did not think
of using stored proc here. Any kind of tips are really appreciated.

Thanks
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31074
    
232

If you change the query to the following similar ones, does it perform in JDBC?


Knowing which of these go fast and which go slow will help with constructive advice on what is going on. Also, what kind of data is in column A? It seems like Y/N data from the where clause, but then the rest doesn't make sense.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
jay desi
Greenhorn

Joined: Jul 02, 2009
Posts: 27
jeanne..Thanks for your advice.
The column actually contains character data comprising of 9 digits. 1% of rows contain data "N". I need to use "distinct" to remove duplicates.
Out of given options:-



was quick. Others took about same time as before.

My problem is that


takes about 85 seconds in JDBC , while about 11 sec on mysql prompt. I tried to measure time taken to obtain connection. In worst case, it is about 0.3
seconds.

What sould I do with


to get faster results. Also, there are no time issues while looping through the resultset.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Why are you using a scrollable ResultSet? What happens if you use a forward only ResultSet?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
jay desi
Greenhorn

Joined: Jul 02, 2009
Posts: 27
Paul,

I used scrollable because I do


to check for resultset empty condition. But then I changed it to FORWARD_ONLY and found that there was no improvement in timing issues.


takes same time even if I change resultset type.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31074
    
232

Jay,
That's very useful information. Since count(*) performed quick and SELECT left(columnA,5) performed slow, that tells me the problem is with the left function [rather than the distinct keyword or returning the data.]

As a workaround, you could do "select columnA" and do the substring and distinct in Java. If this doesn't return a tremendous amount of extra data, it's a worthwhile option.

As to the actual problem, it's likely the jdbc driver causing it. I had this problem once with a db2 function. I couldn't change the driver then, so I wound up moving some logic to Java.
jay desi
Greenhorn

Joined: Jul 02, 2009
Posts: 27
Jeanne..thanks for the explanation and sorry for the confusion here..When I said that:-

was quick, I meant quick compared to original query when run from mysql prompt. But when I run it through JDBC, it takes about
75-80 seconds, almost same time as taken by original query.

I thought that MySQL connector was latest jdbc driver.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31074
    
232

jay desi wrote:Jeanne..thanks for the explanation and sorry for the confusion here..When I said that:-

was quick, I meant quick compared to original query when run from mysql prompt. But when I run it through JDBC, it takes about
75-80 seconds, almost same time as taken by original query.

I thought that MySQL connector was latest jdbc driver.

I was asking about through JDBC. If that's slow through JDBC, it means you aren't getting reamed on the network transfer or left function. Did any of the other SQL statements I provided perform quickly through JDBC. It's almost like it isn't using your index. Do you have a DBA that could look at the database to see what execution path is being taken?
jay desi
Greenhorn

Joined: Jul 02, 2009
Posts: 27

It's almost like it isn't using your index. Do you have a DBA that could look at the database to see what execution path is being taken?


That is most confusing part. Because, when I run query directly, it executes almost 8 times
faster then in JDBC. I guess, query is taking a different execution path when fired from JDBC compared to when fired directly. Does that happen?


Did any of the other SQL statements I provided perform quickly through JDBC


No, all the statments take almost same time through JDBC.

Another strange thing is that, I have other queries with me. JDBC takes about same time
as fired from sql prompt for those queries while getting resultset from statement.
jay desi
Greenhorn

Joined: Jul 02, 2009
Posts: 27
ok, this was my fault. This was what I had entered on sql prompt:-



This was what I had in JDBC


It is an extremely bad idea to have alias as column name. I dont know but I had not altered alias name. Spent hours trying different techniques
on java side.

And life is normal again Thanks Jeanne
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31074
    
232

Jay,
Thanks for sharing the solution. These things are interesting to me!

And it would explain why a different execution plan is chosen.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

(thinking) Would it be related to the fact that alias columnA has the same name as a field columnA in the table?
I'm asking because I have been using aliases often in combination with various JDBC constellations, and did not hit the same issues.


OCUP UML fundamental and ITIL foundation
youtube channel
jay desi
Greenhorn

Joined: Jul 02, 2009
Posts: 27
jan..that is what I posted before

It is an extremely bad idea to have alias as column name


I am not sure on this, but changing alias name had solved my issue.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: "select.." in jdbc gives bad performance but not in mysql directly