slow insert performance

  • 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

  • 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/

  • 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 astinyint = 1,

    @ReferenceId as tinyint,

    @CallId as int,

    @ResultingCallId as int,

    @SourceIP as char (15),

    @DestinationIPas char (15),

    @ProtocolId astinyint,

    @ApplicationId as int,

    @OperationValue asvarchar (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 assmallint,

    @TrunkMember as smallint,

    @Causeas varchar (50),

    @DateReceived as datetime,

    @TimeZoneOffset assmallint

    )

    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)

    )

  • 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

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

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


    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

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • Table are use every sec. Yes there's index.

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

  • 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

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • 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

  • 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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply