Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

slow insert performance Expand / Collapse
Author
Message
Posted Tuesday, June 26, 2012 2:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 6, 2012 12:28 AM
Points: 5, 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

Post #1321091
Posted Tuesday, June 26, 2012 2:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 2,117, Visits: 5,445
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/
Post #1321094
Posted Tuesday, June 26, 2012 2:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 6, 2012 12:28 AM
Points: 5, 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)
)
Post #1321095
Posted Tuesday, June 26, 2012 2:51 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:45 PM
Points: 562, Visits: 887
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/
Post #1321096
Posted Tuesday, June 26, 2012 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 6, 2012 12:28 AM
Points: 5, 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)
)
Post #1321097
Posted Tuesday, June 26, 2012 3:13 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:45 PM
Points: 562, Visits: 887
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/
Post #1321102
Posted Tuesday, June 26, 2012 3:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 6, 2012 12:28 AM
Points: 5, Visits: 14
Table are use every sec. Yes there's index.

some insert is 2 - 10 ms, some insert is big like 500 ms
Post #1321103
Posted Tuesday, June 26, 2012 3:40 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 11:45 PM
Points: 562, Visits: 887
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/
Post #1321109
Posted Wednesday, June 27, 2012 11:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 1:47 PM
Points: 167, 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



Post #1322055
Posted Thursday, June 28, 2012 7:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 4,352, Visits: 6,168
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 at GMail
Post #1322481
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse