Data design - multiple short trips vs one big trip

Question

Boiled down: Is it better to have the calculated field returned with the rest of the results or is it better to calculate it later when you need it?

Example: I have a GridView that displays search results that has many fields like:

• Application ID
• Name
• Account Type
• Account Number

etc.

The account number field is not always consistently the same thing though. For accounts of type A they are eight digits long and for accounts of type B they are twelve.

After the search if they select a record and choose to continue the application I need to check the eigit digit account number for any access restrictions. All accounts of type A and B have an associated eigit digit account number, it is just that type B are the only ones to have the twelve digit number.

So should I add a redundant hidden field like "Common Account Number" to the search results and just hide it, thus making the heavily used search take slightly longer and making the results grid take up more space in the view state?

Or

Should I call the database when they try to continue the application and translate the Application ID into the eigit digit number so that I can use it to do my access restriction check? This will require one extra call to the database but the translation should be fast as both fields are in the same table and the application ID is a primary key. The continue application button will be used less as there are many other command options. People are also more accepting of waiting for something to process rather than waiting for search results.

Answer 1

There's no problem with adding an extra hidden column, especially if you have an internal company application.

However, couldn't you also do the access restriction check in a stored procedure? That is, the stored procedure would handle the app id to eight digit conversion without an extra round trip. Then, from the stored procedure, you could return the appropriate error or the appropriate result set.

Answer 2

You should always make your decision based on real measurements. I like your principle of making the most often used function as fast as possible.

But maybe there's a way of returning the right result set immediately -- for example, do you need the 8-digit numbers in that display, or could you immediately return the 12-digit numbers for the type-B accounts? If yes, maybe you could define a view, or add a computed column right away in your data model. Is there another way of saving state, so that you could avoid hidden fields?

Beyond that, you're the one who can measure that and who has numbers. If the "continue" action is much rarer than the search, and if it doesn't take long, well, by all means, add another database roundtrip. If however you're able to get the search right in one step without adding measurable latency, go for that.