• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
jay desi
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why are you using a scrollable ResultSet? What happens if you use a forward only ResultSet?
 
jay desi
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jay,
Thanks for sharing the solution. These things are interesting to me!

And it would explain why a different execution plan is chosen.
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
(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.
 
jay desi
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
You'll never get away with this you overconfident blob! The most you will ever get is this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic