java prog is very slow
hai
i am beginner level in java prog
i insert data in ms sql server 2000. i am insert 5000 record in database . but i am application take long time .pls give advise for me
very quick
import java.sql.*;
public class SqlDatabase
{
public Connection connection;
public boolean openConnection(String strServerIPAddress, String strDatabaseName, String strUserName, String strPassword)
{
String url = "jdbc:microsoft:sqlserver://" + strServerIPAddress + ":1433" +";DatabaseName=" + strDatabaseName;
try
{
//Loading the driver...
DriverManager.registerDriver(new com.microsoft.jdbc.sqlserver.SQLServerDriver());
//Buiding a Connection
connection = DriverManager.getConnection(url, strUserName, strPassword);
if(connection== null )
return false;
}
//catch( java.sql.SQLException e )
catch (SQLException e)
{
System.out.println(e.getMessage());
return false;
}
return true;
}
public void SqlDataInsert(String Random_num,String Check_Sum)throws Exception {
{
//DriverManager.registerDriver(new com.microsoft.jdbc.sqlserver.SQLServerDriver());
//Connection connection = DriverManager.getConnection( "jdbc:microsoft:sqlserver://202.146.65.221:1433;DatabaseName=MMS_Gateway","Tech","tech32132");
if (connection != null)
{
try {
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = stmt.executeQuery("select * from Random_CheckSum where 1=2");
resultSet.moveToInsertRow();
resultSet.updateString("Random_Number",Random_num);
resultSet.updateString("Check_Sum",Check_Sum);
resultSet.insertRow();
System.out.println("Data Sroted in Database");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
}
[1983 byte] By [
KUMARa] at [2007-10-2 5:50:07]
Advise: use code tags when posting code
>i am insert 5000 record in database . but i am application take long timeHow much time it is taking ?
Is it me or is posting the full connection string including username and password a rather bad idea?
> Is it me or is posting the full connection string> including username and password a rather bad idea?It would be a good idea to a few.
Use [url=http://forum.java.sun.com/help.jspa?sec=formatting]code tags[/code], and format you code nicely.
See
import java.sql.*;
public class SqlDatabase
{
public Connection connection;
public boolean openConnection(String strServerIPAddress,
String strDatabaseName,
String strUserName,
String strPassword) {
String url = "jdbc:microsoft:sqlserver://" + strServerIPAddress
+ ":1433" +";DatabaseName=" + strDatabaseName;
try {
//Loading the driver...
DriverManager.registerDriver( new com.microsoft.jdbc.sqlserver
.SQLServerDriver() );
//Buiding a Connection
connection = DriverManager.getConnection(url,
strUserName,
strPassword);
if(connection== null )
return false;
} catch (SQLException e) {
System.out.println(e.getMessage());
return false;
}
return true;
}
public void SqlDataInsert(String Random_num,String Check_Sum)
throws Exception {
{
if (connection != null) {
try {
Statement stmt = connection.createStatement
( ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet resultSet = stmt.executeQuery
("select * from Random_CheckSum where 1=2");
resultSet.moveToInsertRow();
resultSet.updateString("Random_Number",Random_num);
resultSet.updateString("Check_Sum",Check_Sum);
resultSet.insertRow();
System.out.println("Data Sroted in Database");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
}
Are you running SqlDataInsert 5000 times in a loop? If so maybe addBatch would help, or turning off auto commit, doing the updates, then committing, or maybe looking at the SQL queries. See if you can do it as one sql statement, rather than 5000.
mlka at 2007-7-16 1:59:31 >
Never "throws Exception", throw what you expect to be thrown, no more.
mlka at 2007-7-16 1:59:31 >
I am curious why you need to execute an insert query in this manner. A simple way is to use a plain insert sql statement like INSERT INTO Random_CheckSum (Random_Number, Check_Sum) VALUES(?, ?)
I have to agree with aniseed and RageMatrix, the way you are performing the insert does not seem like the best.
ResultSet resultSet = stmt.executeQuery("select * from Random_CheckSum where 1=2");
resultSet.moveToInsertRow();
resultSet.updateString("Random_Number",Random_num);
resultSet.updateString("Check_Sum",Check_Sum);
resultSet.insertRow();
You are using "select *" which is usually considered bad style and can cause performance problems (in databases I have used anyway).
Also, the code may very well retrieve every row in this table just to insert one row. Which would happen for each insert if you are calling the method for each insert (and it looks like you are).
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
I did not look up how the above statement works but I would not be surprised if specifying access in this manner causes more performance issues.
now my java prog is very fast . now take 15 sec to 20 sec for insert 5000 records
now i have change my code is
import java.sql.*;
public class SqlDatabase
{
public Connection connection;
public boolean openConnection(String strServerIPAddress, String strDatabaseName, String strUserName, String strPassword)
{
String url = "jdbc:microsoft:sqlserver://" + strServerIPAddress + ":1433" +";DatabaseName=" + strDatabaseName;
try
{
//Loading the driver...
DriverManager.registerDriver(new com.microsoft.jdbc.sqlserver.SQLServerDriver());
//Buiding a Connection
connection = DriverManager.getConnection(url, strUserName, strPassword);
if(connection== null )
return false;
}
//catch( java.sql.SQLException e )
catch (SQLException e)
{
System.out.println(e.getMessage());
return false;
}
return true;
}
public void SqlDataInsert(String Random_num,String Check_Sum)
{
if (connection != null)
{
try {
String Sql="INSERT INTO Random_CheckSum (Random_Number, Check_Sum) VALUES(?, ?)";
PreparedStatement pstmt =connection.prepareStatement(Sql);
pstmt.setString(1, Random_num);
pstmt.setString(2, Check_Sum);
pstmt.executeUpdate();
System.out.println("Data Sroted in Database");
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
}
Thank for all
