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.


 


 


 

Comments:

Dude, it is a very good idea to add logging to these classes. Don't mind your collegues. They are robots :-) Can I make a suggestion? One of the best data access libraries to access sql databases that I have used is DataExpress from Borland Jbuilder (the source code is available with the free version, I think). It provides dataset objects that you can modify and then, when you save changes to the database it figures out all the statements that it has to execute in order to persist the changes in the database. What I like about it is: 1. It supports sql procedures, which means that the deletes, updates, inserts can be procedure calls instead of insert/delete/update sql statements 2. It maps column names to procedure parameters automatically, you only have to make sure the column names match the parameter names 3. I provides a syntax to specify the old and new values. If you update a row the old value is still available and can be accesses by prefixing the parameter name with 'old.' . 4. The classes are very easy to extend and you plug in your own resolvers. You could get back output parameters and use these to modify columns in your database without having them appear as modified. This feature is very useful when you insert a row with sequence generated primary keys. You can get back your id and populate it in the dataset. This library has been around from 1997, when I started to use JBuilder 1 and 2. We are in 2006, I am looking at your CachedRowSet stuff and I am shaking my head. Why don't you guys do something similar with that library?? One more question. Is source code for some of the classes used in Creator 2 available? Specifically, I am looking for CachedRowSetImplX. Thanks

Posted by costa b on January 20, 2006 at 04:23 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

jfbrown

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News
Blogroll

No bookmarks in folder