Hi,

I have the following program using jsp to display a simple 'SELECT'  statement in a small table. I monitored the server, it takes a long time to reach the Quickbook, then take a long time to get the data. The display in front end is very fast. So I believe something wrong with the QODBC. Please help me to find out what's the problem with it.

My simple code is:

Connection con = null;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:HuliftQODBC","admin","0423wx");


PreparedStatement pstmt = null;
CallableStatement cstmt = null;
java.sql.Statement stmt=null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
DatabaseMetaData dbmd = null;
dbmd= con.getMetaData();
System.out.println("db:"+dbmd.getDriverName()+"--"+dbmd.getDatabaseProductName());
String field="";
int rsSize=0;
String  sql="";
String opt=request.getParameter("opt");
if (opt==null) opt="rs";
try {

sql="select TimeCreated, VendorRefFullName, ItemlineQuantity from ItemReceiptItemLine where ItemLineItemRefListID='" + item+"'";
System.out.println("sql:"+sql);
 stmt = con.createStatement;
 rs=stmt.executeQuery(sql);
 rsmd = rs.getMetaData();

Thanks,

Basically QODBC is doing a full table scan for the query because ItemLineItemRefListID is not a Jump-in. Jump-ins act as indexes when used in a WHERE clause. Use:

sp_columns ItemReceiptItemLine

to see what jump-ins you can use. You can also call the QODBC optimized table directly by using the NOSYNC tag :

sql="select TimeCreated, VendorRefFullName, ItemlineQuantity from ItemReceiptItemLine NOSYNC where ItemLineItemRefListID='" + item+"'";

for a faster response.

Yes, you can use a subset of data by starting from a starting date instead of the whole table:

sql="select TimeCreated, VendorRefFullName, ItemlineQuantity from ItemReceiptItemLine
where TxnDate > {d '2006-06-30'} and ItemLineItemRefListID='" + item+"'";

Tom,

Thank you for your response. But my problem is the connection to Quickbook takes me 1 mins 2 secs. Get data takes me another 1 mins. Query is not the big problem I believe. When I hit the "submit" button in the broswer, server side  get the request right away, then set up a connection to Quickbook through the QODBC. But looks like frozen there in the following minute. The next timestamp I receive after the connection created, is 1 min 2 sec later. Then process the query in Quickbook and pass the resultset to the webserver in a minute. Until I see the result from the browser, about 2 mins in total. For me, the first step is to limite the connection time. Then is the query time. Please help. thanks,

Don't shut off QuickBooks try leaving QuickBooks running (without a company file open) and then connecting your java jdbc application. If your live company file has lots of new data all the time, you might want to try turning off optimization.

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more on optimization.