0

I have a scenario where one main stored procedure calls a nested procedure and that nested procedure contains simple insert statements for multiple tables and almost every insert is taking so long

I know there are lots of possibilities for instance Disk, CPU, Memory, indexes and triggers

How do I find Clustered and NON-Clustered indexes are not the reason of this slowness?

Insert Statement:

INSERT INTO PEDetail with(rowLock) (CID,CustID,PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID)      
SELECT CID,@PID,@PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID      
FROM PEDetail with(nolock)   
where PpeID = @OldPpeid      

Table Structure:

 SET ANSI_NULLS ON
    GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PEDetail](
    [CID] [int] NOT NULL,
    [CustID] [int] NOT NULL,
    [PpeID] [int] NOT NULL,
    [PeID] [int] NOT NULL,
    [SubSys1ID] [int] NOT NULL,
    [SubSys2ID] [int] NOT NULL,
    [CharacteristicID] [int] NOT NULL,
    [cmbID] [int] NOT NULL,
    [PETemplateID] [int] NOT NULL,
 CONSTRAINT [PK_PEDetail] PRIMARY KEY CLUSTERED 
(
    [CID] ASC,
    [CustID] ASC,
    [PpeID] ASC,
    [PeID] ASC,
    [SubSys1ID] ASC,
    [SubSys2ID] ASC,
    [CharacteristicID] ASC,
    [cmbID] ASC,
    [PETemplateID] 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

ALTER TABLE [dbo].[PEDetail]  WITH CHECK ADD  CONSTRAINT [FK_PEDetail_PEHeader] FOREIGN KEY([PpeID], [PeID])
REFERENCES [dbo].[PEHeader] ([PpeID], [PeID])
GO

ALTER TABLE [dbo].[PEDetail] CHECK CONSTRAINT [FK_PEDetail_PEHeader]
GO

ALTER TABLE [dbo].[PEDetail] ADD  CONSTRAINT [DF_PEDetail_cmbID]  DEFAULT ((0)) FOR [cmbID]
GO

NON Clustered Indexes:

CREATE NONCLUSTERED INDEX [ix_PEDetail_CID_PeID_6] ON [dbo].[PEDetail] 
(
    [CID] ASC,
    [PeID] ASC,
    [SubSys1ID] ASC,
    [SubSys2ID] ASC,
    [CharacteristicID] ASC,
    [PETemplateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO



CREATE NONCLUSTERED INDEX [IX_CID_CharacteristicID_CID_CharacteristicID] ON [dbo].[PEDetail] 
(
    [CID] ASC,
    [CharacteristicID] ASC
)
INCLUDE ( [PpeID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_PpeID_PeID] ON [dbo].[PEDetail] 
(
    [PpeID] ASC,
    [PeID] ASC
)
INCLUDE ( [CID],
[CustID],
[SubSys1ID],
[SubSys2ID],
[CharacteristicID],
[cmbID],
[PETemplateID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
  • How long is "so long"? – Martin Smith Apr 26 at 9:14
  • Upload the actual execution plan to brentozar.com/pastetheplan and add the link to your question. Depending on the number of rows selected, the IX_PpeID_PeID index may or may not be optimal. An OPTION(RECOMPILE) query hint will help avoid parameter sniffing issues. – Dan Guzman Apr 26 at 9:17
  • All columns are in clustered index key, it looks little crazy for me. – Denis Rubashkin Apr 26 at 9:18
  • @martin for a single row 5 seconds – user164767 Apr 26 at 10:02
  • 1
    @user164767, do what you like but 5 seconds for a single row insert is much too long regardless of indexes. I suspect the SELECT as the cause of the extremely bad performance. This isn't to suggest your indexing doesn't need improvement. – Dan Guzman Apr 26 at 10:54
-1

The performance problem it's because of your clustered index. When you use clustered, and you add a new record, it needs to physically rearrange all records in the table to make space for it.

Drop the clustered index and create an unique non-clustered index instead. That should fix your performance problem.

As a general rule, I'd also recommend having a clustered index, but with only a numerical autoincremental id.

  • 1
    I understand why you would think that about clustered indexes as there is a lot of misunderstanding on this topic but "it needs to physically rearrange all records in the table to make space for it." is not the case. See stackoverflow.com/a/24470091/73226 – Martin Smith Apr 26 at 23:07
  • I’ve literally spent months destroying badly built clustered indexes in production environments to make queries faster. Let’s see if it helps with what the user needs. – Luis Alberto Barandiaran Apr 28 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.