July 21, 2003 at 11:14 am
Have a strange problem. Using SQL Server 2000.
Have 2 tables within a database, need to insert values into both.
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
CREATE TABLE [dbo].[TableB] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[MessageID] [int] NOT NULL ,
[Recipient] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Status] [int] NOT NULL ,
[DateTimeSent] [datetime] NULL
) ON [PRIMARY]
GO
And is indexed on MessageID & Recipient
Both tables contain only about 400,000 rows of data. AN insert into tableA takes 11-12 SECONDS and an insert into tableB takes 120-200ms. No other applciations using these tables. Any ideas? Have included a SHOWCONTIG output - have no idea how to interpret it though!!!
Table: 'TableB' (1413580074); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 6768
- Extents Scanned..............................: 852
- Extent Switches..............................: 1649
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 51.27% [846:1650]
- Logical Scan Fragmentation ..................: 35.17%
- Extent Scan Fragmentation ...................: 98.00%
- Avg. Bytes Free per Page.....................: 3332.7
- Avg. Page Density (full).....................: 58.82%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
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.
July 21, 2003 at 11:25 am
Are they indexed the same? The different might be in page splitting, you have very little free space per page in table A.
Andy
July 22, 2003 at 2:13 am
Only TableB has added indexes.
Primary key of TableA is the identity field MessageID, primark key of tableB is the identity field ID.
What would you suggest Andy/anybody?
July 22, 2003 at 7:17 am
Could you post the schema of the table in question?
July 22, 2003 at 7:35 am
Hi, the table is as in the first message 🙂
primary key is identity field MessageId, no other index. only getting 4-6 successful inserts to the table per minute, thought it may be to do with fragmentation, but can't interpret the stats...
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
July 22, 2003 at 11:37 am
is TableA a heap? Have you tried copying tableA and putting a clustered index on MessageID (or alternatively, SendDateTime)? Is it necessary to have unicode fields? What's the average amount of data inserted per row?
July 23, 2003 at 3:58 am
This is the stored procedure that inserts the new entry into tableA
CREATE PROCEDURE [dbo].[mmap_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
It seems illogical but this it seems that its not the INSERT that is taking so long but the actual SELECT on the new identity of the inserted row.
From SQL Profiler:
Insert taking 30ms
Select SCOPE_IDENTITY() FROM TableA taking 3875ms. Now totally confused.
MessageId field is primary key Identity field and is a clustered index. Therefore TableA is not a heap.
What effect would the collation have on the insert/select? Average data per row is small - messagetext typically < 160 chars.
Baffling me at stage - how can a select on the scope_identity take so much longer to execute than an Insert?
July 23, 2003 at 7:05 am
Run a comparison between using SCOPE_IDENTITY and @@IDENTITY and MAX(IDENTITYCOL). Strange that this would be taking so long. Are there concurrency issues (lots of users)? High OLTP table?
July 24, 2003 at 5:08 am
Andy/jpipes
Thanks for your help guys but have FINALLY figured this one out - the problem lay in the select scope identity.
Nasty, nasty bug that lay in our code for years undetected:
SELECT @out_MessageId = SCOPE_IDENTITY() FROM TableA
GO
Should be simply:
SELECT @out_MessageId = SCOPE_IDENTITY() GO
Seems as if the query was processing every single row of the table before returning the identity field value. Simple fix but caused LOADS of heartache.
Stored procedure execution time reduced from 6+ seconds to 40ms.
July 24, 2003 at 6:36 am
anam: good catch. don't know why I didn't notice that. You may want to contribute this little headache to the FAQ...just a suggestion. It may keep someone else from the same mistake!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply