Post reply

Fetching unique record within a stored procedure

  • May 19, 2019 at 2:45 am

    #3645083

    I have a table "Mytable" containing a list of IDs.

    There is a web application and the user logging into the web application is assigned an ID from the table mentioned above. So if 2 users are logging in concurrently, an ID from this table is given to each of the user. The ID and the UserID is then inserted into another table. However care should be taken that the same ID is not assigned to more than one user

    I have used the statement "select ID from mytable with (updlock, holdlock)". This ensures that the ID assigned to the user is unique and that the same ID is not assigned to more than one user.

    However this table has records in millions and since there is locks used, the select statement becomes too slow resulting in slowness of the application.

    Is there a better way to handle this and avoid using the locking hints as mentioned above?

     

     

     

  • May 19, 2019 at 1:33 pm

    #3645121

    Yes.  Use a column that has the IDENTITY property and, if you need to know what value the IDENTITY proper assigned during an INSERT of a new row, use the SCOPE_IDENTITY() function.

    Another possibility is to use a SEQUENCE, although that requires more thoughtful code.

    As to the problems that arise in a table having "millions of rows" vs OLTP performance, consider a proper archiving method.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 4, 2019 at 5:40 pm

    #3650200

    Seems a little backwards to me that you have the IDs already existing in a table and then they're assigned to a User on login. I would think you'd want to do what  Jeff mentioned  above and just use a proper identity column on a table and have that just be created for you on the inserts into that table.

     

    However, if you really must do it this way (for whatever business reasons) you can try experimenting with adding the readpast hint into your locking query hints.

    "select ID from mytable with (updlock, holdlock, readpast)"

    The general idea is that your select will ignore rows that are being locked by another select happening at essentially the same time. You should test this though b/c the locking behavior depends a lot on how your indexes are structured. You may still end up getting lock escalations past the row level (page and table locks) without the proper indexes.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply