Database Performance Tip, using a delete table to improve network performance.

Sometimes you want to display a large amount of data from a database table in your client application. One example from my past was a trading system built in Swing. The center piece of which contained a huge scrollable table with hundreds of thousands of financial transactions, which needed to be updated every few seconds.

Now obviously you can’t just refresh the data every few seconds. Pulling those hundreds of thousands of rows over the network would be impossibly slow.

You are only interested in what has changed, and what has become ubiquitous in database table design is the inclusion of a CREATED_DATE and UPDATED_DATE column. This allows us to programmatically grab only the data which has changed.

SELECT *
FROM TRANSACTIONS
WHERE UPDATE_DATE > $MY_LAST_SELECT_TIMESTAMP$

We store the timestamp of the last time we performed out select, and use this timestamp to pull out only the rows that have changed. After we have these updated rows, we amalgamate them with our current client side data, using perhaps a huge Hashmap to store all out client side data.

This isn’t brain surgery, but what happens if, along with updates to the data rows, you also can have rows deleted in our database? If we just use the above SQL query we will not pick up on any rows that have been deleted, because the row physically just isn’t there. Therefore we cannot reflect the exact state of the database table in our update query.

One solution is to not allow any physical table row deletions. A row is only moved into a “deleted state”, but physically it still exists in the table.

What if you need to be able to delete rows anyway?

Well… you need a “Delete table”.

Table: TRANSACTIONS
TRANS_ID    TRANSACTION DESCRIPTION    CREATED DATE    UPDATED DATE
1    Something here    12.12.2011    13:22:03 12.12.2011
3    Something here    12.12.2011    14:54:11 12.12.2011
4    Something here    12.12.2011    8:33:23   12.12.2011
6    Something here    12.12.2011    8:33:27   12.12.2011

Table: DELETED_TRANSACTIONS
TRANS_ID    UPDATED DATE
2    8:33:23 12.12.2011
5    8:35:56 12.12.2011

Every time a row is deleted in our transaction table, we want to update the DELETED_TRANSACTIONS table, by noting the ID of the row that was deleted.
Now we need two queries. One to reflect the changes in the database, which is the same as before :

SELECT *
FROM TRANSACTIONS
WHERE UPDATE_DATE > $MY_LAST_SELECT_TIMESTAMP$

And then we need this query to find any rows that have been deleted in since the last query was executed :

SELECT *
FROM DELETED_TRANSACTIONS
WHERE UPDATE_DATE > $MY_LAST_SELECT_TIMESTAMP$

Hope this helps!


Posted in Design Pattern | Tagged , , , , | Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

THREE_COLUMN_PAGE