Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Unanswered: Insert & Select SCOPE_IDENTITY() problem

    Have a strange problem. Inserting a row into a table & retrieving the value of its unique identifier (primary key & identity) is taking seconds, when I would expect 100's per second. Insert seems to execute ok, but the select seems to be taking up all the time.

    Using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

    Have a table as follows containing roughly 300k rows:

    CREATE TABLE [dbo].[TableA] (
    [MessageID] [int] IDENTITY (1, 1) NOT NULL ,
    [SendDateTime] [datetime] NULL ,
    [Sender] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [MessageText] [ntext] COLLATE Latin1_General_CI_AS NULL ,
    [Status] [int] NULL ,
    [DateTimeSent] [datetime] NULL
    ) ON [PRIMARY]
    GO

    Primary key is MessageID - a clustered index.

    AN insert to this db takes 30ms but however, a
    Select SCOPE_IDENTITY() FROM TableA takes 3875ms.

    This is the stored procedure that inserts data into the table:
    This is the stored procedure that inserts the new entry into tableA
    CREATE PROCEDURE [dbo].[sp_Insert_OutgoingMessages]
    @inp_SendDateTime AS DATETIME = NULL,
    @inp_Sender AS NVARCHAR(30) = NULL,
    @inp_MessageText AS NTEXT = NULL,
    @inp_Status AS INTEGER = NULL,
    @inp_DateTimeSent AS DATETIME = NULL,
    @out_MessageId AS INTEGER OUTPUT
    AS
    INSERT INTO TableA
    ( SendDateTime,
    Sender,
    MessageText,
    Status,
    DateTimeSent)
    VALUES
    ( @inp_SendDateTime,
    @inp_Sender,
    @inp_MessageText,
    @inp_Status,
    @inp_DateTimeSent)

    SELECT @out_MessageId = SCOPE_IDENTITY() FROM TableA
    GO

    And finally this is the output of a DBCC SHOWCONTIG:
    Table: 'TableA' (1461580245); index ID: 1, database ID: 8
    TABLE level scan performed.
    - Pages Scanned................................: 9555
    - Extents Scanned..............................: 1202
    - Extent Switches..............................: 1224
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 97.55% [1195:1225]
    - Logical Scan Fragmentation ..................: 1.66%
    - Extent Scan Fragmentation ...................: 98.00%
    - Avg. Bytes Free per Page.....................: 117.7
    - Avg. Page Density (full).....................: 98.55%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Has anyone got any ideas why the select would take so long? Any advice re the SHOWCONTIG results?

    Thanks in advance.

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    Use SELECT @out_MessageId = SCOPE_IDENTITY()
    GO

    the FROM is telling it to go through every row in the table.

    Tim S

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •