Tersus: Carefully Plan Database Access

Carefully Plan Database AccessDate: Jul 17, 2009

Databases are optimized to perform large and complex queries, so it is better to access the database once to read or write multiple records rather than performing multiple database queries.

Reduce the number of database interactions in each service. If possible, avoid accessing the database within repetitive or recursive processes.

An example of inefficient database interaction can be found in the current implementation of the ‘Authorization’ module:

Look at the model of ‘Generate User Table’:

We have one database access in ‘Find All Users’, and then, for each user record, we call the ‘Convert User Record to Row’ process, which accesses the database again to get the user’s roles:

This means, if there are ‘n’ users defined in the system, we access the database 1+n times.

As long as ‘n’ is not big, this is acceptable for the (rather rare) browsing of the user table, but a more efficient model can build the user table with a single database access:

Instead of a repetitive process where each occurrence reads the roles of one user, we can read all user roles together. Simply replace the ‘Find User_Roles for User’ service (within ‘Build Role List for User’) by a ‘Find’ that will return all records in the ‘User_Roles’ table.

Now divide the records to groups according to the ‘User’ field (using the Group Items template), where each group contains the records for one user.

This will create a somewhat more complex model, but the performance will be better.

Note that in small applications (or small modules within an application), if performance is not an issue, you may prefer a simpler and clearer model even when it requires multiple database interactions within a service.