Maybe you would want to re-look at things from a higher, systems architecture level. See if there is a place/time when data reading and writing can be split. For example, I was doing an app where millions of rows were read from a datbase, and thousands were written in the course of the day. We didn't have any problems with losing power from reads, but we did have problems with queries returning slow from the "reader". So we split the database. The read/write database took all the inserts and updates and no tables had indexes. The read-only database had indexes on lots of columns in each table, the read-only database was copied to each local machines that did querying once a day.

For your setup (since it is more real-time), what if you had a dedicated machine that acted as the read/write host, and it used hard drives with write-caching turned off in firmware (or with the right Windows API/Reg adjustments). No indexes other than the column used for auto-incementing IDs.

People creating work orders write temp tables to their own machine, and they are copied from their desktop to the read/write host. Your read/write host (via a .NET service or one of the payware COM ones) responds to the new files by importing them into the right table.

Your read-only desktops used by report users have a timer that runs every 5 seconds (could be in a totally seperate always-running .exe) do a SQL-Select on the read/write tables for rows with an ID not in their local copy; if any exist in the resulting cursor, scan through them and insert into their local copies of the data. And if these "read only" report users need to change data, still they create a temp DBF and file-copy it to read/write system. If any user thought they were missing data, all they'd have to do is wait 5-10 seconds for their workstation to get a data refresh.



>>May I know how frequent is the record insert and update?
>>Just would like to clarify, does fast transaction casue error.
>>Because, I am developing stock control system with POS frontend. The application is using by hypermarket, which have 7 POS open at the same time and total of 3000 sales transactions (at least 5 item and could up to 30 item per sales) per day.
>
>The record insert, delete and update count is quite high; for the work order tables alone, it could be as much as 20,000+ records per hour.
>
>A work order can contain as much as 1500 records. There are "standard work orders" that a user can "import" which can generate 1500 inserts at one time. There may be 10+ people creating, pricing and reporting on work orders. There may be 50 work orders created in an hour. Then the materials group generates stock requisitions and stock issues from the work orders; that's several thousand more records being posted to inventory, purchasing and financial tables. There are a number of other applications (like Time reporting and MRP) that also come into the picture. Lots of action.