MySQL Connection too slow
Please anyone with expirience in applets accessing MySQL Server!
I made a test with a loop code executing queries and got the following conclusion:
In media a query takes more than 2 seconds. I used PrepareStatement for better performance but it doesn磘 make it better. I磛e heart about persistent connection. It磗 realy faster? You know anything I could do for better performance? Obs.: When I ping my server the time answer is 200 ms. I磎 from Brazil. But I think it sholdn磘 make the mysql too slow as it is. Please help me.....
I磎 very thank for ur help..
My java code:
public void ConectaBD()
{
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
//Class.forName("org.gjt.mm.mysql.Driver").newInstance();
}
catch(Exception e)
{
System.out.println("Error: " + e);
}
try
{
con = DriverManager.getConnection("jdbc:mysql://labinfor.com.br/labinfor_codvirtual?user=labinfor_cliente&password=800091");
}
catch(SQLException e)
{
System.out.println("Error: " + e );
e.printStackTrace();
}
}
public void Logon()
{
java.util.Date start = new java.util.Date();
long startTime = start.getTime();
if (ValidaCPF(TFCPF.getText()))
{
queryLogon = "SELECT Nome FROM clientes WHERE CPF='" + TFCPF.getText().trim() + "' AND senha='" + TFSenha.getText() + "'";
int indCon=0;
Timer t = new Timer(1000, this);
t.start();
while(indCon<100)
{
Conecta();
try
{
stmt[indStmt] = con.createStatement();
rsLogon = stmt[indStmt].executeQuery(queryLogon);
Achou = "false";
while (rsLogon.next())
{
Achou = "true";
StrNome = rsLogon.getString(1);
LValidaCPF.setText("Bem-vindo " + StrNome);
TFCPF.disable();
TFSenha.disable();
BLogon.setLabel("Logoff");
System.out.println("delay["+indCon+"]= "+System.currentTimeMillis());
}
if (Achou == "false")
{
LValidaCPF.setText("CPF ou senha incorreto");
}
Desconecta();
}
catch(Exception e)
{
System.out.println("Error: " + e);
}
indCon+=1;
}
}
}
[2310 byte] By [
latorrejra] at [2007-10-2 7:31:54]
A connection pool should make your application significantly faster. The Apache Jakarta DBCP is a popular one; google should find it easily.
Also for performance the indexing of your tables may matter greatly.
Almost always use PreparedStatement rather than creating SQL using string concatenation. Imagine what happens if the first variable contains a ' character:
SELECT Nome FROM clientes WHERE CPF= 'O'Malley's' AND ...
That's invalid SQL. PreparedStatement will take care of that automatically.
Connection times of 1 to 5 seconds are normal. Pool your connections.
To understand why:
First, setting up a TCP/IP connection takes roughly 50% longer than a ping; a ping is one packet to the server and one packet back, a TCP/IP handshake to create a connection is one packet to the server, one back, and another to the server. Then you have a required brief wait before the connection is considered complete; we'll ignore the wait and call it .3 seconds for the TCP/IP handshake.
Second, the database has to go through the login process. Just sending the login request and response is yet another packet round trip; in your case, that's another .2 seconds in just network time alone. With MySQL, the login involves checking the security of the connection and the user; it often requires a reverse DNS lookup on the IP address of the client. Probably 4 or 5 tables have to be queried and a user environment is created within the server. The server work for the login might take .75 seconds, at a guess. Adding the network time makes this step .95 seconds or so.
Third, there's the time it takes to do the query itself; another network round trip; .2 second network round trip, plus maybe .1 second to run the query on the database, for a total of .3 seconds.
Fourth, there's the time it takes to tear down the TCP/IP connection on connection close. That involves 4 packets, 2 round-trips, so add another .4 seconds.
Adding it all up, you get .3 + .95 + .3 + .4 = 1.95 seconds, of which .3 is your SQL query (network and DB time), .9 is network time for TCP/IP connection and login request, and .75 is DB time for the login.
Thanks StuDerby,
So. are u saying to me that there磗 no way to do a best performance conection with applets? and I must look another implementention to my application? What about persistent connection? there磗 no way of authenticating the use once and keep the connection alive until user needs. I mean the handshake time just in firt time connection and the another consider the user authenticated?
I must not consider a pool implementation a solution do I?
Thank you again....
Connection times of 5 seconds sound excessive to me. I'd expect there to be some sort of network misconfiguration there.Have you tried specifying the server using its IP address instead of its DNS name? Does this improve matters noticeably?
I should've said connection times of 1/10th to 5 seconds are normal; I've seen the latter (slightly worse, actually) using TCPS (Oracle's SSL-encrypted TCP) over a network route that had 2 satellite hops in it; the latency over satellite (at least that type) was atrocious; pretty decent bandwidth though... Converting to TCP within an established SSH tunnel cut connection times by 4/5ths (just over a second), which was as good as we could get at the time.
The OP's connection times are normal for the network latency he's got, but that is pretty high for "normal" routes over commercial Internet, unless you have a big overseas hop. Times from Austin, TX to major Brazilian government websites are between 150 and 200 ms.; connections staying within Brazil should be better, perhaps except for an overloaded public or University network...
traceroute (or tracert on Windows) might show that the bulk of the time is going to a particular hop on the route; if so, there might be something that can be done about it. It's not too uncommon for people to set up firewalls that are adequate for the initial load but can't keep up as more load as added (often the problem is actually the logging that the firewall is doing; it just can't write to disk fast enough).
If your applet is single-threaded, than a single persistent connection should work fine, if you aren't staying connected for really long times...
If you are staying connected for long times, then you run the risk of multiple users exhausting the connection resources of the database, and of intervening network devices terminating the connection. There are ways to deal with both, usually by using a "connection pool" that has only one connection in it...
> Times from Austin, TX to ...You're in Austin? Been to the Salt Lick recently...? Darn[*], I've got to get back to Austin some time soon!Dave (who is COLD).*Poot, heck, and piffle, I hate this effing swear filter.
> Been to the Salt Lick recently...? Not the real deal in far too long. They've got a stand at the airport now that's OK for the airport, and the spinoff restaurant on 360 is decent too, but I need to make time for the drive down to Driftwood; maybe next month...
Thanks again StuDerby
I really wanna know about persistent connection. Can u tell me with what classes in java with a little example? Another think that makes me not so optimist is if my hosting must install some module on server side or change mysql server configuration..... This 3 questions will solt my doubts. Thank you very much.... and if u could send me ur e-mail I磗 be very glad....
Have a nice day......
or you can add me too: latorrejr@hotmail.com (I hope there磗 no restriction to announce msn in this forum!!!) ; )
rssssss
My English is worst and worst
My questions is:
what java classes I must use to implement persistent connections with?
If possible I磀 like a simple example in a java applet connecting to a mysql server....
I was thinking about connecting to a mysql server throw a php on server side and getting the result by parameters passed to the applet... Did Anyone already make it? I just don磘 know why anyone doesn磘 make a java class that receive parameters from a server-side engine as a resultset..... Is that possible? I just don磘 know how to pass the query parameters to the php code......
Is persistent connection really faster than a non persistent connection? Can u make a comparition? I ask u because if it doesn磘 make many difference I would lost time and i won磘 have the solution....
Ahhh...
When u go to Brazil I磍l show u the best places in south Brazil where I live....
Thank u again!!!
