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

[jay desi]

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

 
SELECT distinct left(columnA,5) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC 
 

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:-

 
           String query="SELECT distinct left(columnA,5) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC ";

           if (con==null)
               return null;

           Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

           if (s==null)
               return null;

           s.setFetchSize(150);
           
           ResultSet rs = s.executeQuery(query); //problem here
 

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

  
ResultSet rs = s.executeQuery(query);
 

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]

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

SELECT count(*) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC   

SELECT columnA FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC   

SELECT left(columnA,5) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC   

SELECT distinct left(columnA,5) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N'

SELECT distinct left(columnA,5) FROM tempDb.testTable WHERE columnA <> 'N' ORDER BY columnA DESC   

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]

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:-

 
SELECT count(*) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC 
 

was quick. Others took about same time as before.

My problem is that

 
SELECT distinct left(columnA,5) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC 

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

ResultSet rs = s.executeQuery(query); 

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

[Paul Sturrock]

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

[jay desi]

Paul,

I used scrollable because I do

return !rs.next();
...
...

rs.beforeFirst()

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

 ResultSet rs = s.executeQuery(query); 

takes same time even if I change resultset type.

[Jeanne Boyarsky]

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]

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

 
SELECT count(*) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC 

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]

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

 
SELECT count(*) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC 

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]

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]

ok, this was my fault. This was what I had entered on sql prompt:-

SELECT distinct left(columnA,5) FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC  

This was what I had in JDBC

 
SELECT distinct left(columnA,5) AS columnA FROM tempDb.testTable force INDEX(samIndex) WHERE columnA <> 'N' ORDER BY columnA DESC  

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]

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]

(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]

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.