This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes java/MYSQL Serious speed problem. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java/MYSQL Serious speed problem." Watch "java/MYSQL Serious speed problem." New topic
Author

java/MYSQL Serious speed problem.

Miran Cvenkel
Ranch Hand

Joined: Nov 23, 2010
Posts: 147
Same server, same sql in both cases. I have 5.1.17 java connector, doubt that changing to 5.1.18 would make a difference.


1. SET query_cache_type = 0; RESET QUERY CACHE ;
2. run query in heidisql (any client side UI) --> /* 0 rows affected, 18 rows found. Duration for 1 query: 0,046 sec. */



3.here sql_execution_time = 116 miliseconds

The problem with timing exponentialy rises with other queries.



Searchable nature photo gallery: http://agrozoo.net/jsp/Galery.jsp?l2=en
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Does your non-Java client UI produce scrollable and updateable result sets? Do you actually need scrollable and updateable result sets?
Miran Cvenkel
Ranch Hand

Joined: Nov 23, 2010
Posts: 147
Paul Clapham wrote:Does your non-Java client UI produce scrollable and updateable result sets? Do you actually need scrollable and updateable result sets?


It does(UI produce scrolable, updatable)

Anyway, if I replace, in upper code:


with



it does not affect speed at all.


EDIT:
if I change number of records retrieved via LIMIT, heidi sql gives me
/* 0 rows affected, 120 rows found. Duration for 1 query: 0,047 sec. (+ 0,078 sec. network) */

Total:125 milisec
same sql with java 239 milisec
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19656
    
  18

Miran Cvenkel wrote:

You don't need a Calendar object for that. Just use System.currentTimeMillis():


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Miran Cvenkel
Ranch Hand

Joined: Nov 23, 2010
Posts: 147
calendar does not influence a thing. Doh I realy don't need it.

I thought of that before an removed from code:



and got sql_execution_time = 0

Note, tat some sql-s via heidi take like 0,1 s, via java code 1s. Hence I went doing this test, otherwise I have some connection pooling.
So, it is sure thing that problem is somewhere in ResultSet rs = stm.executeQuery(sql);
Miran Cvenkel
Ranch Hand

Joined: Nov 23, 2010
Posts: 147
So I made my own connection pooling, just to be sure that each time I run the code I use same connection, that is opened just first time and remains opened.

pseudocode;



Each time I run above code I get(+/- 2 ms on each number ofcourse):

[122, 85, 82, 53, 54, 54, 52, 54, 55, 53]

?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

There you go. So you made one of the common errors in benchmarking, namely failing to run things several times so that you don't measure start-up costs like classes being loaded. Now that you have fixed that, you can make a better comparison.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: java/MYSQL Serious speed problem.
 
Similar Threads
getting the index of the closest value in an array that is sorted in reverse order
How to compare time reliably
finding date diff
MySQL query Cache / Java
Any Alternative for this CODE !!!!!!