SQL Server Performance Forum – Threads Archive

Multiple Front End Users â€“ Cursors Vs. SQL

When developing multiple front ends the developer faces the age old problem of two or more people trying to edit the same record. Using cursors seems to make this easy from a programmer#%92s perspective but seems to be slow performance wise. Separate query statements combined with a time stamp or a separate field for editing speed things up but cause other programming problems. Any ideas on the best way to approach the development of a front end for multiple users? Thanks
Tom <><< Tom Laurie
CIS Professor
NH Technical Institute

You can use transactions and control the isolation level. It’s pretty well documented in books online. I would make sure that all your code is wrapped in stored procedures though, here is a good example: BEGIN TRANSACTION
SELECT * FROM tblWhatever
IF NOT @@ERROR=0
BEGIN
ROLLBACK TRANSACTION
RETURN 0
END
COMMIT TRANSACTION After the BEGIN TRANSACTION statement, you can use the SET statement to control the isolation level, make sure that you read the differences between all the isolation levels. As far as your front-end goes, this depends really, but it shouldn’t matter with regards to your problem, this would be a back-end issue.
"How do you expect to beat me when I am forever?"

Doesn’t SQL Server control locking automatically? I’m glad this topic came up because a VB programmer asked me recently about locking when a user updates the same record at the same time. Hmm.. ———-
T Kelley
MS, MCDBA, OCA, CIW

When you use the BEGIN TRANSACTION statement, there is a default locking level associated with it, I believe it is READ COMMITTED. This and other locking levels are fully documented in BOL.
"How do you expect to beat me when I am forever?"

By default, locking is done at the record level by SQL Server. As royv has suggested, using transactions with a stored procedure is probably the best overall solution. Your goal should always be to minimize the amount of locks, and how long they are held, and this option is one of the best options to accomplish this goal. The problem with cursors is that they can cause excessive blocking, which can impede an application’s performance.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

]]>