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


slow insert performance


slow insert performance

Author
Message
jmoog
jmoog
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3795 Visits: 6513
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
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
SSC Eights!
SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)

Group: General Forum Members
Points: 942 Visits: 1078
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
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
SSC Eights!
SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)

Group: General Forum Members
Points: 942 Visits: 1078
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
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
SSC Eights!
SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)SSC Eights! (942 reputation)

Group: General Forum Members
Points: 942 Visits: 1078
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
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13175 Visits: 8566
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