Re: Suitability for real-time data processing?

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:

Where possible, have the SQL Server perform batch processing using pure SQL rather than some "smart" programmer's row-by-row algorithmn which works great on his own computer with 100 records on a local SQL Server with no other users, but fails in the highly contentious production environment.

If this sounds like I'm taking the p*ss out of the average VB/SQL programmer, unfortunately I am, because time and time again, this is a scenario I encounter, with the programmers saying "Let's switch to Oracle..."

--

Ian Posner
MindQuest Solutions Ltd
iposner_at_dial.pipex.com
Malcolm Blackhall wrote in message <366F70A0.CC7A46F8_at_pbnec.com>...


>I think either Oracle or Microsoft SQL Server should do the job in a

>reasonably designed system with adequate hardware.

>

>If you are looking for clustering to help you with spreading workload

 across


>servers, I think you are looking at Oracle.  Microsoft's clustering for SQL

>Server 6.5 is of the failover variety.

>

>I am not sure what you mean by "relatively poor locking functionality" for

>SQL Server.  SQL Server 6.5 supports page level locking, and row level

>locking on inserts if you follow all the rules.  Oracle supports row level

>locking.  Without starting the row v. page level locking debate all over,

>either approach works if you design for it.  Poorly designed systems will

>probably perform better with row level locking.

>

>Considering the foregoing,  unless Oracle offers you a significant

>advantage, I would think that you would go with Microsoft SQL Server.  SQL

>Server will probably be cheaper.  Your company apparently has experience

>with SQL Server.  There is a corporate policy to use SQL Server.

>