Forum Discussion

mstechmach's avatar
mstechmach
Copper Contributor
Aug 20, 2024

SQL View performance

Hello!

 

if a base SQL table has both cluster and non clustered index, do I need to create again indexes in the SQL view to get better query performance.

 

 

6 Replies

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Aug 21, 2024

    mstechmach 

    It depends on the structure of your view. In general, if a view uses only one table and the main fields designed by the query condition have indexes in the base table, the view need not built indexes. It also depends on the context in which the view is used, such as whether has more conditions when using the view. Other situations require closer analysis. Therefore, it is a good idea to provide table structure, definition of views, and use cases to provide better support.

    • mstechmach's avatar
      mstechmach
      Copper Contributor
      Aug 21, 2024

      rodgerkong 

       

      Thanks for your response!

       

      Here is the base table structure

      CREATE TABLE [dbo].[MyTable](
	[Row_Id] [bigint] NOT NULL,
	[Year_Code] [varchar](100) NOT NULL,
	[Row_Values] [text] NULL,
	[Date_Refreshed] [datetime] NOT NULL,
 CONSTRAINT [PK_MyTable_Row_Id] PRIMARY KEY CLUSTERED 
(
	[Row_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Date_Refreshed]  DEFAULT (getdate()) FOR [Date_Refreshed]
GO

CREATE NONCLUSTERED INDEX [IX_MyTable_Code] ON [dbo].[MyTable]
(
	Year_Code ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

       

      the SQL View structure is as below

      CREATE VIEW [dbo].[vw_MyTable] AS

SELECT 
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."id"') AS id,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."year"') AS year_code,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."emp_id"') AS emp_id,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."emplocalid"') AS emplocalid,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."firstname"') AS firstname,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."lastname"') AS lastname,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."middlename"') AS middlename,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."dateofbirth"') AS dateofbirth,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."race"') AS race,
JSON_VALUE(cast([Row_Values] as varchar(8000)), '$."gender"') AS gender

FROM [dbo].[MyTable]


GO

      And I would like to query the view as below and my question do I need to create any index on the view to get better query performance.

       

      SELECT  [id]
      ,[year_code]
      ,[emp_id]
      ,[emplocalid]
      ,[firstname]
      ,[lastname]
      ,[middlename]
      ,[dateofbirth]
      ,[race]
      ,[gender]
  FROM [dbo].[vw_MyTable]  where  [year_code] ='yr2023'

       

      Looking forward to hear from you

       

       

       

      • rodgerkong's avatar
        rodgerkong
        Iron Contributor
        Aug 21, 2024

        mstechmach 

        shortly, In this scenario,  indexed views should improve query performance, but it still need to be tested at different data scales. In addition, base tables containing TEXT types can not be included in indexed view, see the https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16#deterministic-view-requirement. Change the column to NVARCHAR(MAX) if possible.

         

        Index( IX_MyTable_Code ) in your base table MyTable will not be used in your final query. View vw_MyTable parses json fields from table column. Build indexes base on the fields extract from json string will great improve the performance of search specific fields.

         

        You can also provide some sample data of column Row_Values for further analysis.

Resources