aspose file tools*
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
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: 18570
    
    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: 19697
    
  20

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: 18570
    
    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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java/MYSQL Serious speed problem.