Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hhb
Starting Member
1 Post |
 Posted - 2003-05-06 : 13:21:48
|
| I have a table in my database that holds 3 million records. It is constantly being updated with batch updates while being pounded with reads by the rest of the application. We have the problem that I assume many applications have had in the past...how do you configure your application to handle a table that would work best as two tables: one read only and one write only? We've looked into all kinds of solutions: partitioning tables, replication, etc. My issue is there's a lot of great theory out there telling us how to deal with this table but when you try to do it, it doesn't always seem to work as "they" (Microsoft) said it would. For instance, the reason to split up our table into two tables (read and write) is to reduce the locking that occurs when a transaction is updating records that are also trying to be read. We've tried a bit of replication but have run into issues where the replication itself seems to lock as many records when copying data to the secondary table. We don't have the luxury of allowing much latency for the updates so we need this data to be real-time mirrored on the read and write side. If anyone has real experience with this issue please let me know. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-06 : 13:31:00
|
| You could do it with replication, it looks like you have tried this, so you should be familiar with it. You may also want to consider using NOLOCK hints on your querys of the read-only data. -Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
|
|
|
Load Balancing A High Transaction Table