SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


slow insert performance


slow insert performance

Author
Message
jmoog
jmoog
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 14
Hi Guys,

Can you please figure out the reason why insert with this table is sometimes slow. Some insert 2 ms, some insert 500 ms. Here is the schema


CREATE TABLE [dbo].[TT_TSAPIDataA](
[TempCallDataId] [int] IDENTITY(1,1) NOT NULL,
[SiteId] [tinyint] NOT NULL,
[ReferenceId] [smallint] NULL,
[CallId] [int] NOT NULL,
[ResultingCallId] [int] NULL,
[SourceIP] [char](15) NOT NULL,
[DestinationIP] [char](15) NOT NULL,
[ProtocolId] [tinyint] NULL,
[ApplicationId] [int] NULL,
[OperationValue] [varchar](50) NULL,
[SpecificEvent] [varchar](50) NULL,
[DNIS] [int] NULL,
[ANI] [varchar](20) NULL,
[CallingNumber] [varchar](20) NULL,
[CallingAddressType] [varchar](20) NULL,
[CalledNumber] [varchar](20) NULL,
[CalledAddressType] [varchar](20) NULL,
[DomainExtension] [int] NULL,
[Count] [tinyint] NULL,
[OriginationPort] [int] NULL,
[ConnectedNumber] [varchar](20) NULL,
[ConnectedNumber2] [varchar](20) NULL,
[ConnectedAddressType] [varchar](20) NULL,
[TrunkGroup] [smallint] NULL,
[TrunkMember] [smallint] NULL,
[Cause] [varchar](50) NULL,
[DateReceived] [datetime] NOT NULL,
[TimeZoneOffset] [smallint] NULL
CONSTRAINT [PK_TT_TSAPIDataA] PRIMARY KEY CLUSTERED
(
[TempCallDataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TT_TSAPIDataA] ADD CONSTRAINT [DF_TT_TSAPIDataA_DateReceived] DEFAULT (getutcdate()) FOR [DateReceived]
GO
Adi Cohn
Adi Cohn
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8261 Visits: 6594
Do you have any triggers on the tables? Any indexed views on the table? Are all inserts that you are doing are insert values or are they insert-select?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
jmoog
jmoog
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 14
Hi,

No triggers on table. I am doing the direct insert in stored procedure. Here is the code. What do you think about the table structure? Is it good enough?

[usp_Insert]
(
@SiteId as tinyint = 1,
@ReferenceId as tinyint,
@CallId as int,
@ResultingCallId as int,
@SourceIP as char (15),
@DestinationIP as char (15),
@ProtocolId as tinyint,
@ApplicationId as int,
@OperationValue as varchar (50),
@SpecificEvent as varchar (50),
@DNIS as int,
@ANI as varchar (20),
@CallingNumber as varchar (20),
@CallingAddressType as varchar(20),
@CalledNumber as varchar (20),
@CalledAddressType as varchar(20),
@DomainExtension as int,
@Count as tinyint,
@OriginationPort as smallint,
@ConnectedNumber as varchar (20),
@ConnectedNumber2 as varchar (20),
@ConnectedAddressType as varchar (20),
@TrunkGroup as smallint,
@TrunkMember as smallint,
@Cause as varchar (50),
@DateReceived as datetime,
@TimeZoneOffset as smallint
)
AS
BEGIN

INSERT INTO [TT_TSAPIDataA]
([SiteId]
,[ReferenceId]
,[CallId]
,[ResultingCallId]
,[SourceIP]
,[DestinationIP]
,[ProtocolId]
,[ApplicationId]
,[OperationValue]
,[SpecificEvent]
,[DNIS]
,[ANI]
,[CallingNumber]
,[CallingAddressType]
,[CalledNumber]
,[CalledAddressType]
,[DomainExtension]
,[Count]
,[OriginationPort]
,[ConnectedNumber]
,[ConnectedNumber2]
,[ConnectedAddressType]
,[TrunkGroup]
,[TrunkMember]
,[Cause]
,[DateReceived]
,[TimeZoneOffset])
VALUES
(@SiteId1,
@ReferenceId1,
@CallId1,
@ResultingCallId1,
@SourceIP1,
@DestinationIP1,
@ProtocolId1,
@ApplicationId1,
@OperationValue1,
@SpecificEvent1,
@DNIS1,
@ANI1,
@CallingNumber1,
@CallingAddressType1,
@CalledNumber1,
@CalledAddressType1,
@DomainExtension1,
@Count1,
@OriginationPort1,
@ConnectedNumber1,
@ConnectedNumber21,
@ConnectedAddressType1,
@TrunkGroup1,
@TrunkMember1,
@Cause1,
DATEADD(mi,@TimeZone,@DateReceived1), --@DateReceived
@TimeZone)
)
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 Visits: 1099
jmoog (6/26/2012)
Hi Guys,

Can you please figure out the reason why insert with this table is sometimes slow. Some insert 2 ms, some insert 500 ms. Here is the schema


CREATE TABLE [dbo].[TT_TSAPIDataA](
[TempCallDataId] [int] IDENTITY(1,1) NOT NULL,
[SiteId] [tinyint] NOT NULL,
[ReferenceId] [smallint] NULL,
[CallId] [int] NOT NULL,
[ResultingCallId] [int] NULL,
[SourceIP] [char](15) NOT NULL,
[DestinationIP] [char](15) NOT NULL,
[ProtocolId] [tinyint] NULL,
[ApplicationId] [int] NULL,
[OperationValue] [varchar](50) NULL,
[SpecificEvent] [varchar](50) NULL,
[DNIS] [int] NULL,
[ANI] [varchar](20) NULL,
[CallingNumber] [varchar](20) NULL,
[CallingAddressType] [varchar](20) NULL,
[CalledNumber] [varchar](20) NULL,
[CalledAddressType] [varchar](20) NULL,
[DomainExtension] [int] NULL,
[Count] [tinyint] NULL,
[OriginationPort] [int] NULL,
[ConnectedNumber] [varchar](20) NULL,
[ConnectedNumber2] [varchar](20) NULL,
[ConnectedAddressType] [varchar](20) NULL,
[TrunkGroup] [smallint] NULL,
[TrunkMember] [smallint] NULL,
[Cause] [varchar](50) NULL,
[DateReceived] [datetime] NOT NULL,
[TimeZoneOffset] [smallint] NULL
CONSTRAINT [PK_TT_TSAPIDataA] PRIMARY KEY CLUSTERED
(
[TempCallDataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TT_TSAPIDataA] ADD CONSTRAINT [DF_TT_TSAPIDataA_DateReceived] DEFAULT (getutcdate()) FOR [DateReceived]
GO



Please provide details asked by Adi and also provide the sample Insert statements under the consideration.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
jmoog
jmoog
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 14
Here is the insert command

NSERT INTO [TT_TSAPIDataA]
([SiteId]
,[ReferenceId]
,[CallId]
,[ResultingCallId]
,[SourceIP]
,[DestinationIP]
,[ProtocolId]
,[ApplicationId]
,[OperationValue]
,[SpecificEvent]
,[DNIS]
,[ANI]
,[CallingNumber]
,[CallingAddressType]
,[CalledNumber]
,[CalledAddressType]
,[DomainExtension]
,[Count]
,[OriginationPort]
,[ConnectedNumber]
,[ConnectedNumber2]
,[ConnectedAddressType]
,[TrunkGroup]
,[TrunkMember]
,[Cause]
,[DateReceived]
,[TimeZoneOffset])
VALUES
(@SiteId1,
@ReferenceId1,
@CallId1,
@ResultingCallId1,
@SourceIP1,
@DestinationIP1,
@ProtocolId1,
@ApplicationId1,
@OperationValue1,
@SpecificEvent1,
@DNIS1,
@ANI1,
@CallingNumber1,
@CallingAddressType1,
@CalledNumber1,
@CalledAddressType1,
@DomainExtension1,
@Count1,
@OriginationPort1,
@ConnectedNumber1,
@ConnectedNumber21,
@ConnectedAddressType1,
@TrunkGroup1,
@TrunkMember1,
@Cause1,
DATEADD(mi,@TimeZone,@DateReceived1), --@DateReceived
@TimeZone)
)
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 Visits: 1099
jmoog (6/26/2012)
Here is the insert command

NSERT INTO [TT_TSAPIDataA]
([SiteId]
,[ReferenceId]
,[CallId]
,[ResultingCallId]
,[SourceIP]
,[DestinationIP]
,[ProtocolId]
,[ApplicationId]
,[OperationValue]
,[SpecificEvent]
,[DNIS]
,[ANI]
,[CallingNumber]
,[CallingAddressType]
,[CalledNumber]
,[CalledAddressType]
,[DomainExtension]
,[Count]
,[OriginationPort]
,[ConnectedNumber]
,[ConnectedNumber2]
,[ConnectedAddressType]
,[TrunkGroup]
,[TrunkMember]
,[Cause]
,[DateReceived]
,[TimeZoneOffset])
VALUES
(@SiteId1,
@ReferenceId1,
@CallId1,
@ResultingCallId1,
@SourceIP1,
@DestinationIP1,
@ProtocolId1,
@ApplicationId1,
@OperationValue1,
@SpecificEvent1,
@DNIS1,
@ANI1,
@CallingNumber1,
@CallingAddressType1,
@CalledNumber1,
@CalledAddressType1,
@DomainExtension1,
@Count1,
@OriginationPort1,
@ConnectedNumber1,
@ConnectedNumber21,
@ConnectedAddressType1,
@TrunkGroup1,
@TrunkMember1,
@Cause1,
DATEADD(mi,@TimeZone,@DateReceived1), --@DateReceived
@TimeZone)
)


Have you observed the values you are passing ? Do you have any index on the said table ? How frequently the table is used and for what purpose ?

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
jmoog
jmoog
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 14
Table are use every sec. Yes there's index.

some insert is 2 - 10 ms, some insert is big like 500 ms
rhythmk
rhythmk
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1716 Visits: 1099
jmoog (6/26/2012)
Table are use every sec. Yes there's index.

some insert is 2 - 10 ms, some insert is big like 500 ms



It is difficult to say from my end.But as you tell that the table is used in every second it may be possible that some other query is creating the lock on the table or something is running behind or parallel to the insert statements.

Please use SQL profiler to check that and for a quick help get your DBA.

--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
:-)
thadeushuck
thadeushuck
Old Hand
Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)

Group: General Forum Members
Points: 381 Visits: 164
Have you looked at what types of wait's you are getting while it is running? Or ruled out IO bottlenecks.

Usual Suspect list:

1. Bad Code
2. Worse Code
3. Bad SAN
4. Other Activity (usually bad code)
5. Bad Table design
6. Bad PK Clustering choice + no fill factor
7. Over indexing
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32549 Visits: 8675
first guess is blocking. use sp_whoisactive from sqlblog.com to see that as it happens in real time. use wait stats analysis over a 3-5 minute period to see locking (or other such as writelog) issues in the aggregate.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search