Anyone doing OLTP w/SQL 7 but not using MTS?

[johnk]

We recently upgraded a suite of business applications from Access to SQL Server 7. It is our initial SQL Server experience although we are well experienced with Informix and some with Oracle. Our apps are in VB6, DCOM and ADO.<br>
<br>
We have been surprised at some concurrency issues and have had to be more inventive than I expected to achieve good performance.<br>
<br>
Would like to talk to others to share experiences.

 

[calahans]

I presume when you say concurrency issues, you are talking about locking on tables. Since SS7 has row level locking, you should not get problems on inserts. However, have you looked at how your transactions are written. Are you reading a value for update (say an invoice number) and holding the lock until the end of the transaction , thus making other transactions wait until the first is finished. <br>
<br>
We have OLTP app running against SS7 and have not really came accross other concurrancy isues. We needed to review our transaction scripting after our volume test, maybe its the same for you.

 

[johnk]

Hi calahans, Thanks for the reply. We think we have solved our locking issues with our OLTP apps. Briefly, here's what we wound up with.<br>
<br>
We use ADO and the SQL Server Provider. All initial Selects create diconnected recordsets. When a row is changed, we update the database through a separate connection locking the one row only for the absolute minimum necessary. At the same time our logic updates the disconnected recordset (which will never be reconnected) so that the user will see the changes as he pages through his selected recordset. When we read the one selected row for update, we compare all fields for change since original read and notify user if conflicts have occurred.<br>
<br>
Instead of using MTS and connection pooling we create 2 connections for each user. Our first week of operation with 20 users has gone very, very well. At some number of users we will probably have to change to connection pooling. With DCOM, all of our data access tier is resident on the data server which contributes heavily to reducing network traffic.<br>
<br>
<br>
<br>

 

[needsOLTPadvice]

Hi johnk,
I am currently developing a system using VB6, DCOM and ADO, aiming to track the inventory changes as the items are being assembled into a finished product in a manufacturing facility. The issue has become a bit complicated, because the production is low automated and the material is very often returned to warehouse or transfered to other manufacture orders. Can you advise me where to review some similar sample scenarios and solutions?
Thanks