• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java/MYSQL Serious speed problem.

 
Miran Cvenkel
Ranch Hand
Posts: 200
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Paul Clapham
Sheriff
Posts: 21154
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 200
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20559
57
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Miran Cvenkel wrote:

You don't need a Calendar object for that. Just use System.currentTimeMillis():
 
Miran Cvenkel
Ranch Hand
Posts: 200
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 200
2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21154
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic