SQL Server Performance

Select, Update , Insert on single table SQLServer2

Discussion in 'SQL Server 2005 General Developer Questions' started by Gershom, Nov 7, 2006.

  1. Gershom New Member

    I have a client application which runs three threads "Select", "Update","Insert". Also there are three stored procedures "Select" , "Update" , "Insert" which are used by corresponding threads. Default isolation level "READCOMMITTED" is mentioned in "Select" stored procedure.

    We are using SQL Server 2005

    The way selection and updation occur is,

    Suppose there are 10 records in Table1. "Select" thread starts from say SK 1, "Update" thread starts updating from SK 7. Similarily runs the "Insert" thread.

    When we run multiple client applications around 10. We get transaction deadlocks. Please Helllllllp!!!!!!!!!!!

    Any solution would be greatly appreciated
  2. Roji. P. Thomas New Member

    Can you post your code and table structure?

    Otherwise try to identify the deadlock using the tracedlag 1204 and SQL server profiler.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  3. Gershom New Member

    This code runs in thread. This is for Select. Similarily for Insert and Update

    While (blnStopSelectThread = False)
    lngSelectSeqNo = GetSelectSequenceNo()
    dsSelect = ExecuteSQLReturnDs("EXEC WFMS_TESTLOCK_SELECT '" & lngSelectSeqNo "'")
    ------------- Do something-----------------
    End While

    Table Structure
    Table has 70 columns. Those I am using are "SK","ObjectId" and "DeleteFlag".I select based on "SK" and update the "DeleteFlag" as "Y"

    Thanks for the post
  4. Roji. P. Thomas New Member

  5. Gershom New Member

    ---Select---

    ALTER PROCEDURE [dbo].[WFMS_TESTLOCK_SELECT]
    @SKVARCHAR(50)
    AS


    SELECT TOP 1
    PJ_CONFIG_ITEM_ID
    FROM
    PMA_PROJECTS WITH (READCOMMITTED)
    WHERE
    PJ_SK = @SK
    ORDER BY PJ_SK

    ------Update-----
    ALTER PROCEDURE [dbo].[WFMS_TESTLOCK_UPDATE]
    @PJ_SKVARCHAR(50)
    AS

    UPDATE PMA_PROJECTS
    SET PJ_DELETE_FLAG = 'Y'
    WHERE PJ_SK = @PJ_SK

    ------Insert----------

    ALTER PROCEDURE [dbo].[WFMS_TESTLOCK_INSERT]
    @PROJECTIDVARCHAR(50),
    @MAPIDVARCHAR(50),
    @CHILDOBJECTID VARCHAR(60),
    @OBJECTSEQNONUMERIC
    AS


    INSERT INTO PMA_PROJECTS
    (
    PJ_ID,
    PJ_CONFIG_ITEM_ID,
    PJ_DESCRIPTION,
    PJ_PIT_SK,
    PJ_STATUS,
    PJ_LEVEL_NO,
    PJ_SEQ_NO,
    PJ_LEAF_INDICATOR,
    PJ_TEMPLATE_FLAG,
    PJ_TEMPLATE_PROJECT_ID,
    PJ_SK_REF,
    PJ_FILE_NAME,
    PJ_PRIORITY,
    PJ_REMARKS,
    PJ_COMPANY_ID,
    PJ_USAGE_TYPE,
    PJ_USER_CREATED,
    PJ_DATE_CREATED,
    PJ_OBJECT_REF,
    PJ_MAP_ASSIGN_ENTITY_SK
    )
    VALUES
    (
    @PROJECTID,
    @CHILDOBJECTID,
    @CHILDOBJECTID,
    3,
    'AVAILABLE',
    2,
    @OBJECTSEQNO,
    'Y',
    'N',
    @MAPID,
    24871,
    '',
    10,
    NULL,
    'HTC',
    'WKF',
    'ADMIN',
    GETDATE(),
    NULL,
    NULL
    )
    -----Indexes-------------
    1.NONCLUSTERED INDEX on "PJ_CONFIG_ITEM_ID" i.e ObjectId
    2.NONCLUSTERED INDEX on "PJ_DELETE_FLAG" i.e DeleteFlag
  6. Roji. P. Thomas New Member

    We are close [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />What is your PK? clustered index?<br /><br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  7. Gershom New Member

  8. Roji. P. Thomas New Member

  9. Gershom New Member

    Sorry. PJ_SK is the primary key.
  10. Gershom New Member

    PJ_SK is the only clustered index col
  11. Roji. P. Thomas New Member

    OK. Both your non-clustered indexes are useless. Just drop them. Also the "TOP 1 " in the select is unnecessary since the column PJ_SK is the primary key.

    Repeat the test after dropping the index and let us know the result.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  12. anilsaritha New Member

    Dear Gershom

    Plz reply if the solution given by ROji has worked.
    I am just as eagor as any other

    Anil

    Never Give Up
  13. Gershom New Member

  14. Gershom New Member

    It works with snapshot_isolation_level.

Share This Page