Tim Schwallie wrote in message ...
>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 Tue Dec 15 1998 - 00:00:00 CST
