CODEWITHSUNDEEP
sqlsql-server
vinton
vinton

Reputation: 39

Performance improvement of SQL Server table with millions of records

I have a table in SQL Server which has millions of records.

I was trying to do a select by passing id in the where condition, like this:

select id,processid value 
from table1 
where processid= 5

It's returning around 1 million records and took around 25 minutes to execute.

There is one index on the table. Do I need to create a separate non clustered index?

please see my table script

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Schema1].[Table1](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [ProcessId] [bigint] NOT NULL,
        [Amount2] [decimal](21, 6) NULL,
    [Amount1] [decimal](21, 6) NULL,
        [Amount3] [decimal](21, 6) NULL,
            [Amount4] [decimal](21, 6) NULL,
    [CreatedById] [int] NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [UpdatedById] [int] NULL,
    [UpdatedDate] [datetime] NULL,
    [IsActive] [bit] NOT NULL,
    [IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_Schema1_Table1] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Table1_INDEX_FG]
) ON [Allocation_DATA_FG]
GO

ALTER TABLE [Schema1].[Table1]  WITH CHECK ADD  CONSTRAINT [CHK_Table1ComputeNode] CHECK  ((([ProcessId]%(3)+(1))=(2)))
GO

ALTER TABLE [Schema1].[Table1] CHECK CONSTRAINT [CHK_Table1ComputeNode]
GO

I have to do performance improvement in a stored procedure which do have lots of joins with table. But this selection itself taking too much time.

Please give suggestions to improve the performance

Upvotes: 1

Views: 156

Answers (2)

Alexander Volok
Alexander Volok

Reputation: 5940

Optimal variant:

  1. Since id is ever increasing identity field, change the primary key from non-clustered to clustered. Also this will prevent fragmentation.
  2. Add non-clustered index on processid. It will be covered for your query. Which will make it ideal in terms of select performance

Less optimal:

In case if the primary key cannot be adjusted:

CREATE INDEX ProcessID on table1(ProcessID) INCLUDE (id)

Upvotes: 3

Slava Murygin
Slava Murygin

Reputation: 1955

At first, if you just need to extract list of IDs for certain ProcessId and you do not want to create clustered index by ID you can create clustered index on "processid" column. If you do not want to create clustered index at all, create index by ProcessId with included ID:

CREATE INDEX IDX_Table1 ON [Table1](processid) INCLUDE ([Id])

At second, your query can't return millions of records, because accordingly to CHECK CONSTRAINT [CHK_Table1ComputeNode] processid can only have values: 1,4,7,...

Upvotes: 0

Related Questions