There was several good practice that we can take advantage of to speed up our application.
For instance let’s use vehicle maintenance and reports as example.
To open vehicle maintenance page or generate a report, apps need to search through several joined table and generate thousands of records.
This process might take long time, as we have more and more customer, it will take longer and longer time.
To overcome this problem: the direction we should go is:
1) Use “view”(virtual table) in SQL server database. Currently we do not use any of “view” to speed up.
View are virtual table in database, it can store the maintenance reports and other reports ahead of time.
To write a windows service or robot job running behind the scene for example at midnight to calculate all the maintenance record and report and store them in ‘view’ virtual table.
At day time when user want to see it, it does not need to calculate again, it just fetch it from the view virtual table. It will save lots of waiting time for user and fast response.
I worked for one of the world largest bank five years ago, this is how they handle the millions of transaction every day.
i) Use “view” virtual table store all kinds of reports.
ii) Use “robot” to calculate those reports at night time and store them in virtual table.
iii) Do all the maintenance work like robot job, backup, archive, at night time. Some IT staff work over night to run it.
Think about when you use credit card or make payment credit card at day time, it might not take effect until 24 hours to 48 hours.
2) Data grid contain all the records, that is a big trouble.
Ideally we want each query only return 10 to 20 records to fit the page display, not all the 1,000 record.
Like google search, it is fast, because all the search results are calculated ahead of time and store there, when you want it, google does not need to really search for you, it just give you results from result repository( in our case, I call it “view” virtual table).
Also google only return 10 record for each query.
To accomplish this, on Sql command, you can set row count (10) to return only 10 records each query. But how to make it happen on data grid, I don't know yet.
use index in database, might and might not optimize the performance.
It will depends on the situation.
If the record are not frequently changed, not frequently delete, update, create, the index will optimize the performance.
Otherwise if the record frequently be changed such as delete, update, create, each change will trigger to
generate and update the old index, this will be a burden on database server instead of optimize the performance.
Depends on the situation, to balance the burden of index and optimization of performance of index.
