Re: jtds driver and SQL Server performance problem
From: Stuart Leonard (suncds_at_verizon.net)
Date: 01/16/05
- Next message: Joe Weinstein: "Re: jtds driver and SQL Server performance problem"
- Previous message: Luke Webber: "Re: jtds driver and SQL Server performance problem"
- In reply to: Stuart Leonard: "jtds driver and SQL Server performance problem"
- Next in thread: Joe Weinstein: "Re: jtds driver and SQL Server performance problem"
- Reply: Joe Weinstein: "Re: jtds driver and SQL Server performance problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Sun, 16 Jan 2005 00:19:03 GMT
The biggest difference between the two queries is the field attributes on
the "Where" clause. The "fast" query is retreiving the row based on an
"int" column.
The slow query is retreiving the row based on a "char" column (not varcar).
The query is based on either the "ae_rfi1New" or the "xe_rf1New" tables.
Here is the code, FWIW. The bottleneck is ocurring within the
getInvoiceView() method.
import java.sql.*;
import java.text.SimpleDateFormat;
import java.text.ParseException;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.GregorianCalendar;
class ImageOneImport
{
Connection con_otg = getJDBCConnection(ImageOneImport.getLocalHost(),
"1433", "remote", "scd123", "otg");
Connection con_export = getJDBCConnection(ImageOneImport.getLocalHost(),
"1433", "remote", "scd123", "export");
//Connection con_otg = getODBCConnection("otg", "remote", "scd123");
//Connection con_export = getODBCConnection("export", "remote", "scd123");
SimpleDateFormat isofmt = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat mdyfmt = new SimpleDateFormat("MMddyy");
PreparedStatement ae_dt1 = null;
PreparedStatement xe_dt1 = null;
PreparedStatement ae_rf1 = null;
PreparedStatement xe_rf1 = null;
PreparedStatement ae_export_upd = null;
PreparedStatement ae_export_add = null;
long curTime;
public ImageOneImport()
{
try
{
ae_dt1 = con_otg.prepareStatement("Select field1, docid from ae_dt1 where
docid = ?");
xe_dt1 = con_otg.prepareStatement("Select field1, docid from xe_dt1 where
docid = ?");
ae_rf1 = con_otg.prepareStatement("Select field2, field3, field5, field7
from ae_rf1New where field1 = ?");
xe_rf1 = con_otg.prepareStatement("Select field2, field3, field5, field7
from xe_rf1New where field1 = ?");
ae_export_upd = con_export.prepareStatement("Update ae_export Set invoice
= ?, salesorder = ?, billto = ?, shipto = ?, invdate = ?, docid = ?, invdte
= ? where objectid = ?");
ae_export_add = con_export.prepareStatement("Insert Into ae_export
(objectid, invoice, salesorder, billto, shipto, invdate, docid, invdte)
VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
} catch (SQLException e){e.printStackTrace();}
//refreshExport();
processInvoices();
}
/*public void refreshExport()
{
ImageOneFiles.createDirInfo(con_export,"W:\\ocr_imgs\\CBBC\\");
}*/
public void processInvoices()
{
ResultSet rs = null;
Statement stmt = null;
int ctr = 0;
try
{
stmt = con_otg.createStatement();
rs = stmt.executeQuery("Select objectid, docid from ae_dl1 Where objectid
>= 13770 Order By objectid");
while (rs.next())
{
int objId = rs.getInt("objectid");
int docId = rs.getInt("docid");
curTime = System.currentTimeMillis();
String invoice = getInvoice(docId);
writeInvoice(docId, objId, invoice);
ctr++;
System.out.println("counter " + ctr + " objectid = " + objId);
}
}catch (SQLException e) {e.printStackTrace();}
finally { try { rs.close(); stmt.close(); con_export.close();
con_otg.close(); } catch (NullPointerException e) {} catch (SQLException e)
{}
}
}
public String getInvoice(int docId)
{
ResultSet rs = null;
String invoice = "";
try
{
ae_dt1.setInt(1, docId);
rs = ae_dt1.executeQuery();
boolean found = rs.next();
if (!found )
{
xe_dt1.setInt(1, docId);
rs = xe_dt1.executeQuery();
found = rs.next();
}
if (found)
invoice = Pad.checkNull(rs.getString("field1"));
} catch (SQLException e) {e.printStackTrace();}
finally { try { rs.close(); } catch (NullPointerException e) {} catch
(SQLException e) {}
}
//System.out.println("getInvoice invoice " + invoice + " docid " + docId);
invoice = checkInvoice(invoice);
long time = System.currentTimeMillis();
System.out.println("getInvoice invoice " + invoice + " docid " + docId);
System.out.println("elapsed getInvoice() " + (time - curTime));
curTime = time;
return invoice;
}
public void writeInvoice(int docid, int objectid, String invoice)
{
String salesorder = "";
String billto = "";
String shipto = "";
String invdate = "";
long invDateMillis = 0;
ResultSet rs = null;
try
{
rs = getInvoiceView(ae_rf1, invoice);
boolean found = rs.next();
if (!found )
{
rs = getInvoiceView(xe_rf1, invoice);
found = rs.next();
}
if (found)
{
salesorder = Pad.checkNull(rs.getString("field2"));
billto = Pad.checkNull(rs.getString("field3"));
shipto = Pad.checkNull(rs.getString("field5"));
invdate = Pad.checkNull(rs.getString("field7"));
try
{
java.util.Date invmdy = isofmt.parse(invdate);
GregorianCalendar cal = new GregorianCalendar();
cal.setTime(invmdy);
invdate = mdyfmt.format(cal.getTime());
invDateMillis = cal.getTimeInMillis();
} catch (ParseException e) { invdate = "";}
}
} catch (SQLException e) {e.printStackTrace();}
finally { try { rs.close(); } catch (NullPointerException e) {} catch
(SQLException e) {} }
try
{
ae_export_upd.setString(1, invoice);
ae_export_upd.setString(2, salesorder);
ae_export_upd.setString(3, billto);
ae_export_upd.setString(4, shipto);
ae_export_upd.setString(5, invdate);
ae_export_upd.setInt(6, docid);
ae_export_upd.setDate(7, new java.sql.Date(invDateMillis));
ae_export_upd.setInt(8, objectid);
int row = ae_export_upd.executeUpdate();
if (row <= 0)
{
ae_export_add.setInt(1, objectid);
ae_export_add.setString(2, invoice);
ae_export_add.setString(3, salesorder);
ae_export_add.setString(4, billto);
ae_export_add.setString(5, shipto);
ae_export_add.setString(6, invdate);
ae_export_add.setInt(7, docid);
ae_export_add.setDate(8, new java.sql.Date(invDateMillis));
ae_export_add.executeUpdate();
}
}catch (SQLException e) {e.printStackTrace();}
long time = System.currentTimeMillis();
System.out.println("elapsed writeInvoice() " + (time - curTime));
curTime = time;
}
public ResultSet getInvoiceView(PreparedStatement ps, String invoice)
throws SQLException
{
ResultSet rs = null;
ps.setString(1, invoice);
rs = ps.executeQuery();
long time = System.currentTimeMillis();
System.out.println("elapsed getInvoiceView() " + (time - curTime));
curTime = time;
return rs;
}
private String checkInvoice(String invoice)
{
if (invoice.length() <=0) return invoice;
boolean ok = true;
try {
Integer.parseInt(invoice);
} catch (NumberFormatException e) {ok = false;}
if (!ok)
{
int j = 0;
char[] old = invoice.toCharArray();
char[] inv = new char[invoice.length()];
for (int i = 0; i<old.length; i++)
{
if (Character.isDigit(old[i]) )
{
inv[j] = old[i];
j++;
}
}
if (j > 0)
invoice = new String(inv).trim();
else
invoice = "";
}
return invoice;
}
static Connection getODBCConnection( String database, String UID, String
PWD)
{
Connection con = null;
try {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } //loads the driver
catch (ClassNotFoundException e) {
System.out.println("Error: " + e);
}
DriverManager.println("===================== testing connection type
three =====================");
// possible requirements
String connect_string = "Driver={SQL Server};" +
"Database=[database_name];" +
"Address=[ip_address],[port_number];" +
"UID=[username]; PWD=[password]";
String driver = "SQL Server";
//String url = "jdbc:odbc:Driver={SQL
Server};Server=SUNCDS2;Database=master;UID=sa; PWD=scd123;
Address=192.168.0.125, 1433"; // mssql7.0
//String url = "jdbc:odbc:Driver={SQL
Server};Server=SUNCDS1;Database=master;UID=sa; PWD=scd123"; // mssql7.0
String url = "jdbc:odbc:Driver={SQL Server};Server=(local);Database=" +
database; // mssql7.0
//Connection con = DriverManager.getConnection("jdbc:odbc:Driver={" +
driver + "};DBQ=" + fileName, UID,PWD);
con = DriverManager.getConnection(url,UID,PWD);
DatabaseMetaData dma = con.getMetaData ();
System.out.println("\nConnected to " + dma.getURL());
System.out.println("Driver " + dma.getDriverName());
System.out.println("Version " + dma.getDriverVersion());
System.out.println("");
}
catch (SQLException e) {e.printStackTrace();new DisplayError(e,false,
"Connection Failed!!!");}
return con;
}
public static Connection getJDBCConnection(String server, String port,
String uid, String pwd, String database)
{
Driver d = null;
Connection conn = null;
try {
d =
(Driver)Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
Class.forName("net.sourceforge.jtds.jdbc.Driver");
} catch (InstantiationException e) {e.printStackTrace();}
catch (IllegalAccessException e) {e.printStackTrace();}
catch (ClassNotFoundException e) {
e.printStackTrace();}
try
{
String url = "jdbc:jtds:sqlserver://" + getLocalHost() + ":" +
"1433" + "/" + database;
conn = DriverManager.getConnection(url, uid, pwd);
DatabaseMetaData dma = conn.getMetaData ();
System.out.println("\nConnected to " + dma.getURL());
System.out.println("Driver " + dma.getDriverName());
System.out.println("Version " + dma.getDriverVersion());
System.out.println("");
}
catch (SQLException e) {e.printStackTrace();new DisplayError(e,false,
"Connection Failed!!!");}
return conn;
}
public static String getUser()
{
String userid = System.getProperty("user.name").trim();
userid = userid.toUpperCase();
return userid;
}
public static String getLocalHost()
{
String host = "";
try
{
host =InetAddress.getLocalHost().getHostName();
}
catch (UnknownHostException e) {}
return host;
}
public static void main(String[] args)
{
ImageOneImport imp = new ImageOneImport();
//System.out.println(imp.checkInvoice(",890761.."));
}
}
"Stuart Leonard" <suncds@verizon.net> wrote in message
news:KsfGd.378$CI6.44@trnddc06...
>I am using the opensource jtds driver to retreive information from an SQL
>Server database. The java application and the SQL Server database both
>reside on the local (c:) drive.
>
> I am using a PreparedStatement object to retrieve a single row from the
> SQL Server database. The "Where" clause of the "Select" statement
> specifies a field that is both a unique and a primary key. The retreival
> time is approximately .6/10ths to 7/10ths of a second, which is horrible.
>
> I am using another PreparedStatement in the java program to retreive a
> single record from a table. The "Where" clause specifies a field that
> contains an index (not primary, not unique), and this retreival is taking
> approximately 10/1000ths of a second.
>
> I cannot come to a conclusion as to why the first record retreival is
> taking so long. Both tables are fairly large (approx. 1.5 million rows)
> for each table. Also, I am selecting only the fields that are needed (I
> am not performing a "Select *")
>
> If anyone can provide me with some debugging ideas, I would appreciate it.
>
>
>
- Next message: Joe Weinstein: "Re: jtds driver and SQL Server performance problem"
- Previous message: Luke Webber: "Re: jtds driver and SQL Server performance problem"
- In reply to: Stuart Leonard: "jtds driver and SQL Server performance problem"
- Next in thread: Joe Weinstein: "Re: jtds driver and SQL Server performance problem"
- Reply: Joe Weinstein: "Re: jtds driver and SQL Server performance problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
