Re: jtds driver and SQL Server performance problem
From: Joe Weinstein (joeNOSPAM_at_bea.com)
Date: 01/16/05
- Previous message: Stuart Leonard: "Re: jtds driver and SQL Server performance problem"
- In reply to: Stuart Leonard: "Re: jtds driver and SQL Server performance problem"
- Next in thread: Stuart Leonard: "Re: jtds driver and SQL Server performance problem"
- Reply: Stuart Leonard: "Re: jtds driver and SQL Server performance problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Sat, 15 Jan 2005 16:43:33 -0800 To: Stuart Leonard <suncds@verizon.net>
Ok. Now try timing a plain statement that does the same as the slow query,
except that you hard-code the parameter value into the SQL. That will
avoid the driver's sending the parameter as unicode, which means the
DBMS must convert the char column value to an NCHAR. Conversions of
data cannot be assumed to retain the same order as the index, so you
will get a table scan.
If I'm right, there should be a driver connection property you can
set to tell the driver to send String parameters as 8-bit chars. That
will be the solution for the PreparedStatement.
Joe Weinstein at BEA
Stuart Leonard wrote:
> 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.
>>
>>
>>
>
>
>
- Previous message: Stuart Leonard: "Re: jtds driver and SQL Server performance problem"
- In reply to: Stuart Leonard: "Re: jtds driver and SQL Server performance problem"
- Next in thread: Stuart Leonard: "Re: jtds driver and SQL Server performance problem"
- Reply: Stuart Leonard: "Re: jtds driver and SQL Server performance problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
|
