Forum Discussion
SQL View performance
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]
GOAnd 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
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.
Hello.
Just a pointer: as far is performance is concerned compiled stored procedure are faster then views.
Hope this helps
I can convert it to NVARCHAR(MAX) - do you think creating a FULL INDEX on Row_Values column will help for my scenario.Fulltext might help, but you should change the query to use it, JSON functions will not be improved by fulltext index. You must use https://learn.microsoft.com/en-us/sql/relational-databases/search/query-with-full-text-search?view=sql-server-ver16 in your query, your query conditions must be changed to original string in json text, like this
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] WHERE CONTAINS([Row_Values], '"year_code": "yr2023"')It seems need not use a view.
In this way, the JSON hierarchy may be ignored, and you have to consider non-consistent JSON writing. You'd better do some testing with it. Something was wrong with my computer, and it is being treated these days, so I'll have to do test later.
