Re: Suitability for real-time data processing?

You may want to avoid the identity field all together. Try using a scheme of reverse numbering, ie 0000001 becomes 1000000, 0000002 becomes 2000000. This will allow the clustering of this field to become broad or should I say have lots of room at the highest level. I've seen this help to eliminate hot points, kind of a neat big boy trick. Then make sure you stick to the rules of inline updates.

Next, your response times for retrieval are tough to meet for either Oracle and SQL Server. It really is more dependant upon access times to the data, ie data cached in memory or do you have a well indexed table residing on fast drives and controllers, network bandwidth, etc. Is it possible to keep some of this information local (on the client)? If this is the only app using the data in the look up tables and running on the client, then it may be possible to keep these values resident on the client machines with a check at appropriate positions to see if any of the values have changed. (Could do a check on max date or something like that, or a controlling table, etc.)

Update times will also be dependant upon where validation or business rules will be applied. From what I've seen so far, this is difficult to determine.

Strange...some folks get payed big bucks to answer architecture questions.....

Steve Brown wrote in message <750sjl$kuj$1_at_fir.prod.itd.earthlink.net>...
>Ian Posner wrote in message

 <#NuQihgJ#GA.198_at_uppssnewspub04.moswest.msn.net>...
>
>>I agree -- generally locking problems during high contention arise as a
>>result of poor design. Of crucial importance in 6.5 is using clustered
>>indexes on major foreign key fields in such a way that highly contentious
>>updates are spread evenly accross the table space. Additionally use stored
>>procs for all data access and have all transactions controlled from within
>>the stored procedures so as to reduce the time locks are held. Also, avoid
>>using cursors in stored procs -- use pure SQL and set-based algorithms:
>
>
>Agreed. It seems SQL Server is sensitive to "Hot Spots" especially during
>INSERT DML statements. If the table in question is being rapidly appended,
>then clustering on an index, especially on an IDENTITY column with little
>UPDATEs probably won't generate expected or desired results. In that case,
>a non-clustered index is probably best.
>
>The problem is page splitting. If you cluster on that type of table, SQL
 Server
>will repeatedly incur the additional cost of transversing the index to the
 same
>page. Better to allow a simple end-of-table inserts and incur the expected
>page splitting and occasional extent lock.
>
>If you are successful in applying clustered indexes described above, the
 other
>thing you can do to minimize page splitting is use FILLFACTOR and PAD_INDEX
>options of the CREATE INDEX statements. In this way you can leave room for
>INSERTs not only at the data page level, but also the non-leaf index level
 as well.
>Of course, this only works with existing data so DBCC REINDEX will be
 required
>occasionally.
>
>Depending on the type of modifications to the database, one might also want
 to
>examine the use of locking hints and request an UPDLOCK instead of a shared
>lock at the outset. Better to experience "blocking" instead of deadlocks :)
>
>Steve Brown
>sjbrown_at_axdev.com
>www.axdev.com
>
>
>
Received on Mon Dec 14 1998 - 00:00:00 CST