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

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)
)

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