Joel Brown's Weblog

Logging Database Access

Last Friday I received a bug that was very vague in it's description - "Creator pages seem slow".  My first guess was that it was an application issue, but there wasn't a way to prove it.  We need to see what sql is being executed and when.

So I went through our CachedRowSet implementation to beef up the logging.  Now I know my esteemed co-workers opinion is to don't log, debug.  I'll just say I disagree.  There are reasons to log at run time and "cross-boundary" tracking (such as shipping sql off to a database) is a common one.  With the FCS version, you'll be able to see

• when a query is executed, including it's actual SELECT statement and any input parameters
  
• what actually happens within acceptChanges().  You might  be surprised - a select will precede every update or delete of a row.

What it doesn't show is the actual time it takes to execute the statement.  To see this information, check the rowset property "printStatements".  Oh, and the output is rather ugly as I'm more substance over style.

Back to the bug.   Yuck.  I've just exposed a performance problem.  It turns out we were executing rowsets when we didn't need to, sometimes multiple times.  So if you think your web pages that access your database is slow in EA or EA2, it'll be better with the production release.

Posted at 07:02AM Oct 11, 2005 by jfbrown in Database Stuff  |  Comments[1]

Using Parameters in SQL Statements

When you wish to execute a select statement containing WHERE criteria that changes from request to request, there are two typical approaches:

• use input parameters within the statement. For example, set your rowset's command is "select a,b,c from mytable where xyzzy = ?".  Each "?" represents a single value.
  Then set the input parameter in your code:
  

  rowset.set Object(1, qvalue ) ; // parameter numbers start at 1.

  
  
• build the sql command at runtime

String command = "select a, b, c from mytable" ; // stash this constant value somewher
rowset.setCommand( 
    command + " where xyzzy = " + qValue.toString() ) ;

In general, use parameters whenever possible. Creator always executes the rowset's command by using a java.sql.PreparedStatement.  PreparedStatements are compiled (prepared) by the JDBC driver or database and accept input parameters so they can be reused with different data. This reuse improves performance of your application.

However, for any specific situation, you may need to build your command at runtime. If you do, keep in mind that the rowset's command should be valid sql and have the same columns that you plan to have at runtime. This allows the creator design time to determine column names for binding. At runtime, you can set the rowset's command property to just about anything - just make sure any bound columns are included in the query.

Input parameters do have shortcomings. Common issues are::

• runtime query performance. For example, the database's query optimizer may come up with a poor query plan when one uses "wumpus like ?" compared to "wumpus like 'big%'".
  
• IN comparisons. Until runtime, you may not know how many values will exists - "xyzzy IN ( ?,?)" or "xyzzy IN (?, ?, ?, ?)"

Most of this isn't specific to Creator, as we use plain old JDBC to prepare and execute the statements.

Q & A

Where do I call setObject()?

Before the rowset is executed or re-executed.  Exactly where is up to you and your application.

What object type do I pass to setObject()?

To be safe, use the java object type corresponding to the database's type - here's where you need to know a little bit about JDBC. 

How can I share the SQL between the rowset's command property if I'm building the actual statement at runtime?

Often you want the SQL's column list -select a,b,c form mytable - to be in the rowset's command.  At runtime, you'll append the WHERE clause.     The rowset's command property is initially set in the bean's constructor (open the code folded section called "Creator-managed Component Initialization" to see it).  So later in the constructor, save a copy of the command into a property.  You'll also need to create that property.

Posted at 11:57AM Oct 03, 2005 by jfbrown in Creator  |  Comments[2]