October 26, 2009 at 1:58 am
Hi,
We have an application which runs in java and connected to sql server.java team using TVP(sql server) for bulk insert.but it is inserting only 1000 records per 4 sec.bt when i researched on tvp,it is inserting 40000 data per second.Why is this tvp not fast in java?Any problem in connection with java?
Here is the java code...
public static int insertMessages(int PAT, int userid, int connectionid2, int esmeid2) {
StringBuilder addrows = null;
Statement stmInsert = null;
if (selectStatus == true) {
//System.out.println("Inserting Records!!!!!!!!!!====" + userid + "=========" + connectionid2 + "=========" + esmeid2);
String msgids = "";
boolean flag = false;
boolean f = false;
String s1[] = null;
PreparedStatement pstm = null;
try {
// String query = "set transaction isolation level repeatable read insert into TBL_PULL_MESSAGES(esme_msgid,sender,message,msisdn,smsc_msgid,msgtype,pid,validity,priority," +
// "registereddelivery,status,submit_time,web_submit_type,request_time,userid,connectionid,esmeID,systemid) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//String queryInsert = "set transaction isolation level repeatable read insert into TBL_PULL_MESSAGES(esme_msgid,sender,message,msisdn,smsc_msgid,msgtype,pid,validity,priority,registereddelivery,status,submit_time,web_submit_type,request_time,userid,connectionid,esmeID,systemid) values";
String queryInsert = "insert into @tbl_pull_messages_type(esme_msgid,sender,message,msisdn,smsc_msgid,msgtype,pid,validity,priority,registereddelivery,status,submit_time,web_submit_type,request_time,userid,connectionid,esmeID,systemid) values";
addrows = new StringBuilder();
addrows.append(queryInsert);
// System.out.println("Main Queryyyyyyyyyyyyyyyyyyyyy" + query);
pconn3 = getConnection();
//pconn3.getMetaData()
System.out.println("2No of active connections:" + poolableConnFactory.getPool().getNumActive());
// pstm = pconn3.prepareStatement(query);
stmInsert = pconn3.createStatement();
aa1 = (ArrayList) getDatas(PAT, userid);
int no1 = 0;
int msgCount1 = aa1.size();
for (int i = 0; i < aa1.size(); i++) {
s1 = (String[]) aa1.get(i);
sender = s1[0];
message = s1[1];
msisdn = s1[2];
pid = Integer.parseInt(s1[4]);
validity = Integer.parseInt(s1[5]);
priority = Integer.parseInt(s1[6]);
registereddelivery = Integer.parseInt(s1[7]);
status = Integer.parseInt(s1[8]);
submit_time = s1[9];
//System.out.println("Submit_Timeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee" + submit_time);
submitsm_status = 0;
SMSC_MsgID = s1[11];
esme_msgid = s1[12];
//System.out.println("esme_msgid" + esme_msgid);
userid1 = Integer.parseInt(s1[13]);
//System.out.println("UserId1 is " + userid1);
//System.out.println("UserId is " + userid);
web_submit_type = Integer.parseInt(s1[14]);
Request_Time = Integer.parseInt(s1[15]);
btsl_approved = Integer.parseInt(s1[16]);
esm_class = Integer.parseInt(s1[17]);
data_coding = Integer.parseInt(s1[18]);
rconnid = Integer.parseInt(s1[19]);
resmeid = Integer.parseInt(s1[20]);
SYSTEM_ID = Integer.parseInt(s1[21]);
//System.out.println("Btsl_approved is " + btsl_approved);
if (esm_class == 3 && data_coding == 0) {
iMsgType = 0;
} else if ((esm_class == 0 && data_coding == 8) || (esm_class == 3 && data_coding == 8) || (esm_class == 67 && data_coding == 4)) {
iMsgType = 1;
} else if (esm_class == 0 && data_coding == 16 || (esm_class == 3 && data_coding == 16) || esm_class == 3 && data_coding == 2) {
iMsgType = 2;
} else if (esm_class == 67 && data_coding == 245) {
iMsgType = 3;
}
if (msisdn.length() > 12 || message.contains("You have won") || message.contains("Your mobile has won")) {
statusMsg = true;
} else {
statusMsg = false;
}
if (btsl_approved == 0) {
int aStart2 = 3;
int aEnd2 = 48;
Random random1 = new Random();
EsmeID = showRandomInteger(aStart2, aEnd2, random1);
if (!statusMsg) {
try {
//System.out.println("Inserting into TBL_PULL_MESSAGES");
addrows.append("(");
addrows.append("'");
addrows.append(esme_msgid);
addrows.append("'");
addrows.append(",");
addrows.append("'");
addrows.append(sender);
addrows.append("'");
addrows.append(",");
addrows.append("'");
addrows.append(message);
addrows.append("'");
addrows.append(",");
addrows.append("'");addrows.append(msisdn);addrows.append("'");
addrows.append(",");
addrows.append("'");addrows.append(SMSC_MsgID);addrows.append("'");
addrows.append(",");
addrows.append(iMsgType);
addrows.append(",");
addrows.append(pid);
addrows.append(",");
addrows.append(validity);
addrows.append(",");
addrows.append(priority);
addrows.append(",");
addrows.append(registereddelivery);
addrows.append(",");
addrows.append(status);
addrows.append(",");
addrows.append("'");addrows.append(submit_time);addrows.append("'");
addrows.append(",");
addrows.append(web_submit_type);
addrows.append(",");
addrows.append(Request_Time);
addrows.append(",");
addrows.append(userid1);
addrows.append(",");
iIndex = showRandomInteger(1, 8, random1);
if (iIndex == 0 || iIndex == 6) {
addrows.append(iIndex + 1);
addrows.append(",");
addrows.append(iIndex + 1);
addrows.append(",");
} else {
switch (SYSTEM_ID) {
case 325:
case 359:
case 330:
case 331:
addrows.append(8);
addrows.append(",");
addrows.append(8);
addrows.append(",");
break;
default:
addrows.append(iIndex);
addrows.append(",");
addrows.append(iIndex);
addrows.append(",");
}
}
addrows.append(SYSTEM_ID);
addrows.append("),");
msgids = msgids + "\'" + esme_msgid + "\'" + ",";
//System.out.println("StringBuilderrrrrrrrrrrrrrrrrrrrrrr" + addrows.toString());
// int count = stmInsert.executeUpdate(addrows.toString());
msgids = msgids + "\'" + esme_msgid + "\'" + ",";
flag = true;
} catch (Exception sqe) {
cat.warn("Error", sqe);
sqe.printStackTrace();
try {
msgids = msgids + "\'" + esme_msgid + "\'" + ",";
boolean ff = updateStatus(msgids, true);
msgids = "";
} catch (Exception ex) {
ex.printStackTrace();
} //inside catch end
}//catch end
}//end of if statusMSg
}//end of btsl_approved
}//end of for
//int k[] = pstm.executeBatch();
String addrows1 = addrows.toString().substring(0, addrows.toString().length() -1);
String executeString = "DECLARE @tbl_pull_messages_type AS tbl_pull_messages_type;" +
addrows1 + "; EXEC InsertPullMessages @tbl_pull_messages_type";
System.out.println("Before Insertion" + new java.util.Date());
stmInsert.execute(executeString);
System.out.println("After Insertion"+new java.util.Date());
// int count = stmInsert.executeUpdate(addrows.toString());
// stmInsert.execute("EXEC InsertPullMessages @tbl_pull_messages_type");
aa1.clear();
newPATBal = PAT - msgCount1;
//System.out.println("No of records updateddddddddd===" + s1.length);
if (pconn3 != null) {
poolableConnFactory.getPool().returnObject(pconn3);
}
} catch (Exception e) {
System.out.println(e);
cat.warn("Error", e);
f = updateStatus(msgids, flag);
} finally {
try {
if (stm != null) {
stm.close();
}
if (rs != null) {
rs.close();
}
if (pconn3 != null) {
poolableConnFactory.getPool().returnObject(pconn3);
}
} catch (SQLException fse) {
System.out.println("Finally Block " + fse.getMessage());
fse.printStackTrace();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
return newPATBal;
}
Reply will be appreciated
October 27, 2009 at 5:06 am
1) TVP is a SQL 2008 feature so this post should go on a SQL 2008 forum, not this one (SQL 2005)
2) Have you benchmarked your java application to determine how many rows worth of data it is throwing over to SQL Server per second?
3) Have you checked for network delay?
4) Once the data hits SQL Server, have you checked for IO stalls there? Done any wait stats analysis? Checked for blocking? Do you have triggers on the table being inserted to? Lots of indexes? Referential integrity to be maintained?
5) Be careful about comparing your throughput to what you read about in full-blown benchmark tests. I doubt VERY much that your hardware (or your configuration) is anywhere near the level of what they used! 😉
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply