March 27, 2007 at 7:00 pm
Hi Guys,
I have a design/architecture issue that I'd like to discuss.
I work on a large insurance database which stores all the client details and details of their policies.
Changes in a clients personal details are tracked in a tracking table so that we know what their details were on any given date. Changes of details can be entered in the future and not take effect until they should. The details for any given point in time are pulled from the details table using the record that matches this point in time.
A number of fields in use are computed depending on various factors. An example is the policy reference, it pulls information from the policy type, from the policy and sometimes from the client.
Now the issue comes with trying to search policies based on client details or their reference. Searching on any of these using the base tables doesn't seem to be pratical because the database has to construct the search fields for every policy in the database before it can filter our the ones of interest - which is way way too slow.
The current solution to this problem is to have a set number of real fields in the policy table which are used for searching and are updated by trigger. Any future dated changes are updated overnight.
This works quite well but because the policy table is a heavily used table having additional triggers on it does cause access to the policy table to be slow for all users becuase of the locking that is going on at the trigger level.
I have thought of a possible second solution, which is to have another table with a 1-to-1 relationship with the policy table containing only search information. I would still need to trigger updates but they would now be updating a different table instead of the policy table. Then when I search for policies I can just join my policy-search table on for the purpose of the search.
I would be very interested in any comments on these approaches and any others.
Cheers,
Dale
April 3, 2007 at 8:47 am
Consider computed columns and/or indexed views as possible solutions.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply