locked
How I improve Bulk table performance using index in SQL Table RRS feed

  • Question

  • User-582711651 posted

    Hi,

    Anyone, please help me 

    Please ref my code, the following table having 30 Lak records, getting null (StoName) value when I tried to get a record from this table

    for Ex.,

    Select 
    A.[StoID], 
    B.[StoName]
     
    From       [dbo].[Mast_Table] (Nolock) [A]  
    Left Join  [dbo].[Sto_Document] (Nolock) [B] on [A].[StoID] = [B].[StoID]
    
    Where [A].[StoID] = '77001'

    Kindly ref my below-mentioned table, is anything that can be changed for improving faster?

    USE [MYSQLDB]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Sto_Document](
    	[RowID] [bigint] IDENTITY(1,1) NOT NULL,
    	[StoID] [int] NOT NULL,
    	[StoName] [varchar](150) NOT NULL,
    	[IsActive] [int] NOT NULL,
    	[CreatedBy] [int] NOT NULL,
    	[CreatedOn] [datetime] NULL,
    	[ModifiedBy] [int] NULL,
    	[ModifiedOn] [datetime] NULL,
    	[DisabledBy] [int] NULL,
    	[DisabledOn] [datetime] NULL,
     CONSTRAINT [PK_Sto_Document] PRIMARY KEY CLUSTERED 
    (
    	[RowID] 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
    

    I would like to add few things in cluster like this 

    CONSTRAINT [PK_Sto_Document] PRIMARY KEY CLUSTERED 
    (
    	[RowID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    

    Thanks in advance. 

    Sunday, April 4, 2021 6:12 PM

Answers

  • User1535942433 posted

    Hi ayyappan.CNN,

    You're right.When you  just read data from a very very large table,you could use Nonclustered Indexes to improve the performance.

    I think,you could add below this to your select query.

    CREATE NONCLUSTERED INDEX [IX_Mast_Document]
    ON [dbo].[Sto_Document] ([StoID])
    GO

    More details,you could refer to below article:

    https://www.sqlshack.com/tracing-and-tuning-queries-using-sql-server-indexes/

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 5, 2021 6:22 AM