SQL Server Performance

Multiple Front End Users – Cursors Vs. SQL

Discussion in 'General DBA Questions' started by tlaurie, Dec 20, 2002.

  1. tlaurie New Member

    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
  2. royv New Member

    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?"
  3. tkelley New Member

    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

  4. royv New Member

    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?"
  5. bradmcgehee New Member

    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

Share This Page