How should data be updated? Or how to "Caching" data to avoid constantly request at sql server?

Ellen Hu

15y2k

Hi All,

(I am not interested using caching at web page, and looking for database solution only)

There are many data needs to be displayed on the web page, like company total working hours (this is just an example, there are more data in fact), also for individuals can login and update their account through web page, say report working hour.

After an individual added some hours, the total hours for this individual and whole company will be changed accordingly.

The totals are calculated via a complicated stored procedue, having joined multi tables(the sp is also optimized), many users log into their accounts might at almost same time.

Question:
Is it good idea to create a table to hold such totals, and this table will be updated only at midnght through scheduled job?

In that case could avoid to calling those complicated stored procedures, in order to gain performance?

or there are some other approaches?

Thank you for your time to looking this thread.

ellem

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs15y ago
Yes, I am being idealistic. I understand that we usually must accept the situation that is less than ideal.

I think the important thing is that we don't know have an adequate understanding of your requirements.
Ellen Hu15y ago
Sam,

Somehow I do agree with you. However, no matter what, just have to show the change is my task. :)

Suthish,

It is NOT one time update, users can modify their data as many times as they want. Also CTE is not a solution, I can say my queries are very efficient, had implement CTE, Merge all sorts of things.

Right now is the structure changes. Either changed current tables to store data, or add "another layer", would be tables, to capture the data.

Thank you for all your time,

Ellen
Suthish Nair15y ago
As you said if it's an one time activity of data saving and the same data get shared all users. So this is static records? Also, you can use CTE (Common table expressions) to optimize your SP's to an great extend. Also readon: http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx
Sam Hobbs15y ago
Of course it is entirely possible for someone to say that you could create a temporary table to be used during the day and then apply the daily updates in a batch at night. Batching updates was very common back in the days when data was kept on magnetic tape reels. There was a time when data would be punched into cards made of cardboard and then read as batches and then the transactions would be matched to the master data which would then be updated. The main disadvantage of doing something similar for your system is that the master data (your main database) would not be current during the day. You could write queries that read the daily data but then that makes your system more complicated.

The best solution is to redesign the database. You know that, right? The database probably evolved from a much simpler design. It was probably designed by people without experience with database design. The system is now expensive to modify and maintain; not just the data and database, but the software that processes the data. Management does not want to redesign because it would be expensive to do that, but it is also expensive to keep it the way it is. This dilemma has existed in data processing for half a century.

You need to do a study. Management likes studies. Analyze how expensive it would be to redesign and maintain in the future after that then analyze the cost of maintaining the system the way it is. Even if the results of the study show clearly that the long-term costs can be reduced by doing a redesign now, management will likely choose to delay the expenditures and keep the system the way it is. The advantage for you is that when they are reluctant to accept the cost of maintaining the system the way it is, you can at least respond by saying that it would be less expensive if the system were designed better.

Does that help? Are these comments relevant?
Ellen Hu15y ago
Sam,

Thank you for your reply.

Actually it is an internet based application, the users are from some of the fortune 100, the working hour is just an example for the data, it can be money etc.... thus, users login to our application through their personal PC station around word, the majority is in USA.

Because the total is always changed based on the individual's change, it caused constantly calling those complicated stored procedures, remember we have many those sort of data need to be displayed at user interface, and will decrease the application performance.

The application self is using c#.net, and database is sql 2008.

Any idea will be appreciated.

Ellen
Sam Hobbs15y ago
Is this for a LAN or intranet (not internet) application? I assume it is.

Would all the relevant updates be done from one computer? In many companies there are many computers and employees would not all use the same computer.

What do you use to access the database? Do you use Entity Framework, TableAdapters, low-level ADO .Net (such as SqlConnection) or something else? It is normal for database programs to query records, then show the records in a form, then update multiple records. So it is possible to use the database in a very typical manner.

I assume that you need to do something different for your company. I don't know how your company wants the employees to clock out at the end of their work. Traditionally, workers punch out using a time card and a clock that stamps the time on the clock. Using newer technology, workers can swipe a badge. You need to describe details such as that; at least explain whether there is just one computer being used or if the updates would come from multiple computers.