g_emerick

I'm using Microsoft jdbc driver sp2 with MS SQL Server 2000 sp3. I'm
trying to reduce the network calls to the db by using batch inserts.
Below is the sample code, standard and batch method. Batch is no
faster. A network ****yzer shows that either way the driver is doing
remote procedure calls for each insert. I've tried Data Directs
driver and it does batch properly and much faster. Can someone show
me what I am missing.

//STANDARD
String url = "jdbc:microsoft:sqlserver://myServer:1433User=me;Password=pass;DataBaseName=Ba tchTest";
String sql = "insert into Something( Something, TestSomething)
values(?, ? )";
Connection cn = null;
PreparedStatement ps = null;

try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
cn = DriverManager.getConnection(url);
ps = cn.prepareStatement(sql);

int i = 0;
while( i++ < 1000 ) {
ps.setString(1, "This is something: " + i );
ps.setString(2, "Something more : " + i );
ps.execute();
}
} catch (Exception e) {
System.out.println("problem: " + e.getMessage() );
} finally {
try {ps.close(); } catch (Exception e) {}
try{ cn.close(); } catch (Exception e1) {}
}

//BATCH
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
cn = DriverManager.getConnection(url);
ps = cn.prepareStatement(sql);

int i = 0;
while( i++ < 1000 ) {
ps.setString(1, "This is something: " + i );
ps.setString(2, "Something more : " + i );
ps.addBatch();
}
int[] rows = ps.executeBatch();
} catch (Exception e) {
System.out.println("problem: " + e.getMessage() );
} finally {
try {ps.close(); } catch (Exception e) {}
try{ cn.close(); } catch (Exception e1) {}
}


Thanks,
Greg

joe weinstein

It's difference in the drivers. Did you try the
'Joe's "bootleg" batching'?

String myBatch = "";

int i = 0;
while( i++ < 1000 ) {
myBatch += "insert into Something( Something, TestSomething) values('"
+ "This is something: " + i
+ "', '"
+ "Something more : " + i
+ "')"
+ "\n";
}

Statement s = c.createStatement();
s.executeUpdate( myBatch );

As long as your insert parameters can be easily represented by their
string representations, this should be as fast as anything.
Let me know if this helps,
Joe

g_emerick

Joe,

Thanks for the reply and help. I'm surprised that the MS driver is
'faking' batch. Maybe I shouldn't be. I just wanted to make sure I
wasn't crazy, or missing something. Your 'bootleg' code rocks. It
also works for stored procedures which is my goal. One thing, though.
Yours took 8s to run the while loop only. The code below takes 0s
for the loop. Check it out, and thanks again.


StringBuffer myBatch = new StringBuffer(8000);

int i = 0;
while( i++ < 1000 ) {
myBatch.append( "insert into Something( Something, TestSomething)
values('" );
myBatch.append( "This is something: ").append(i).append("', '");
myBatch.append( "Something more : ").append(i).append("')");
myBatch.append( "\n");
}

Statement s = c.createStatement();
s.executeUpdate( myBatch.toString() );


Greg

joe weinstein

You're very welcome. Yep, I just typed in that code for clarity,
but as you diligently demonstrated, StringBuffer is the efficient
Java way to iteratively construct the SQL string.
Glad to help,
Joe Weinstein at BEA

suepurkis

Greg,
Joe pointed out that there is a difference in the Microsoft and
DataDirect drivers. To explain the difference -- DataDirect added
support for SQL Server native batch processing in our 3.2 release, and
the Microsoft driver was built on our 2.2 release.


Sue


Sue Purkis
DataDirect Technologies