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.
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 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?
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.
posted 11 years ago
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.
(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
posted 11 years ago
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.
Anderson gave himself the promotion. So I gave myself this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop