February 4, 2019 at 7:21 pm
Below table contains five millions of records.
My Table Structure
CREATE TABLE [dbo].[T_PDF](
[F_PRODUCT] [varchar](50) NOT NULL,
[F_LANGUAGE] [varchar](2) NOT NULL,
[F_PRODUCT_NAME] [nvarchar](2000) NULL,
[F_FORMAT] [varchar](3) NOT NULL,
[F_SUBFORMAT] NVARCHAR(10),
[F_CUSTOM1] [nvarchar](4000) NULL,
[F_CUSTOM2] [nvarchar](4000) NULL,
[F_CUSTOM3] [nvarchar](4000) NULL,
[F_CUSTOM4] [nvarchar](4000) NULL,
[F_CUSTOM5] [nvarchar](4000) NULL,
[F_COUNTER] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_T_PDF] PRIMARY KEY CLUSTERED
(
[F_PRODUCT] ASC,
[F_LANGUAGE] ASC,
[F_FORMAT] ASC
[F_SUBFORMAT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
NON CULSTERED INDEX DETAILS FOR the ABOVE TABLE.
CREATE INDEX IX_PRODNAME ON T_PDF(F_PRODUCT_NAME)
CREATE INDEX IX_SUBFORMAT ON T_PDF(F_SUBFORMAT)
MY Stored Procedure
CREATE PROCEDURE [dbo].[TEST]
@LANGUAGE NVARCHAR(2),
@SUBFORMAT NVARCHAR(50),
@PRODUCTNAME NVARCHAR(200),
AS
BEGIN
SET NOCOUNT ON
SELECT
TP.F_PRODUCT AS ID,
TP.F_PRODUCT_NAME AS [NAME],
TP.F_LANGUAGE AS LANGCODE,
TP.F_FORMAT AS FMTCODE,
TP.F_CUSTOM1 AS TN,
TP.F_CUSTOM2 AS CP,
FROM T_PDF TP
LEFT JOIN V_PROD_ALIAS_SYN SYN ON SYN.F_PRODUCT = TP.F_PRODUCT
WHERE
TP.F_PRODUCT <> ''
AND (@PRODUCTNAME IS NULL OR
REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME,'[','\['),'_','\_'),'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME
OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME
OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME
OR SYN.F_DATA LIKE @PRODUCTNAME)
AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
AND EXISTS (SELECT 1 FROM AllSubformats ASF WHERE ASF.Val = TP.F_SUBFORMAT)
END;
In above procedure the three OR Conditions for product name search(
OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME
OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME
OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME) taking more time in the table when i passing or not passing the @PRODUCTNAME as input parameter.
I am not able to create index for this above three CUSTOM columns because the each field size NVARCHAR(4000).
so how can we create index for this columns(F_CUSTOM1,F_CUSTOM2,F_CUSTOM3) or any other method to improve this performance.
February 5, 2019 at 4:53 am
I can't see a way of speeding up the comparisons on the CUSTOM columns.
It might be possible to improve the performance of the left join to your view. But as you haven't included the definition of the view or the underlying tables it's not possible to say if you can or by how much it would improve the performance.
You could try the following hints at the end of the SQL:OPTION (OPTIMIZE FOR UNKNOWN)
orOPTION (RECOMPILE)
February 5, 2019 at 5:11 am
Also, share the execution plan of the SP.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 5, 2019 at 5:53 am
So... all those REPLACE things are going to prevent statistics use and index use and you're going to see degraded performance. Whatever structure is in place that forces that upon you, replace it instead of using REPLACE. Functions on the data columns in WHERE, HAVING and ON clauses will seriously, negatively, impact performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2019 at 6:12 am
Grant Fritchey - Tuesday, February 5, 2019 5:53 AMSo... all those REPLACE things are going to prevent statistics use and index use and you're going to see degraded performance. Whatever structure is in place that forces that upon you, replace it instead of using REPLACE. Functions on the data columns in WHERE, HAVING and ON clauses will seriously, negatively, impact performance.
Just skimmed the thread quickly, might be missing something but since all string functions are deterministic, my thought is that this could be improved by using persisted calculated column(s).
😎
February 5, 2019 at 6:47 am
Eirikur Eiriksson - Tuesday, February 5, 2019 6:12 AMJust skimmed the thread quickly, might be missing something but since all string functions are deterministic, my thought is that this could be improved by using persisted calculated column(s).
😎
That could work. Sure. Those can be indexed too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2019 at 7:28 am
Grant Fritchey - Tuesday, February 5, 2019 6:47 AMEirikur Eiriksson - Tuesday, February 5, 2019 6:12 AMJust skimmed the thread quickly, might be missing something but since all string functions are deterministic, my thought is that this could be improved by using persisted calculated column(s).
😎That could work. Sure. Those can be indexed too.
The custom columns are defined as nvarchar(4000) so wouldn't be able to index them.
February 5, 2019 at 7:36 am
Jonathan AC Roberts - Tuesday, February 5, 2019 7:28 AMGrant Fritchey - Tuesday, February 5, 2019 6:47 AMEirikur Eiriksson - Tuesday, February 5, 2019 6:12 AMJust skimmed the thread quickly, might be missing something but since all string functions are deterministic, my thought is that this could be improved by using persisted calculated column(s).
😎That could work. Sure. Those can be indexed too.
The custom columns are defined as nvarchar(4000) so wouldn't be able to index them.
No. Not those. I mean the persisted calculated columns suggested by Eirikur instead of the nvarchar(4000) (and what's up with that?).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2019 at 7:36 am
Jonathan AC Roberts - Tuesday, February 5, 2019 7:28 AMGrant Fritchey - Tuesday, February 5, 2019 6:47 AMEirikur Eiriksson - Tuesday, February 5, 2019 6:12 AMJust skimmed the thread quickly, might be missing something but since all string functions are deterministic, my thought is that this could be improved by using persisted calculated column(s).
😎That could work. Sure. Those can be indexed too.
The custom columns are defined as nvarchar(4000) so wouldn't be able to index them.
What is the actual length of the data?
😎
Definition is one thing, usage is an entirely different thing. I've seen enough < 10 character usage of MAX defined columns that having a penny for each, would earn me enough to buy a flat in central London!
February 5, 2019 at 8:29 am
Grant Fritchey - Tuesday, February 5, 2019 7:36 AMJonathan AC Roberts - Tuesday, February 5, 2019 7:28 AMGrant Fritchey - Tuesday, February 5, 2019 6:47 AMEirikur Eiriksson - Tuesday, February 5, 2019 6:12 AMJust skimmed the thread quickly, might be missing something but since all string functions are deterministic, my thought is that this could be improved by using persisted calculated column(s).
😎That could work. Sure. Those can be indexed too.
The custom columns are defined as nvarchar(4000) so wouldn't be able to index them.
No. Not those. I mean the persisted calculated columns suggested by Eirikur instead of the nvarchar(4000) (and what's up with that?).
I can't see that the calculated columns would be a lot shorter than the original columns. the replace is only replacing the odd character in the string.
February 5, 2019 at 8:38 am
You do realize that the index on Product_Name is going to have a wee bit of a problem if it ever exceeds the maximum number of bytes for an index, right?
I'd also seriously question the CUSTOM columns. Do they really need to be 4000 characters (8000 bytes) wide? If they do, then change them to NVARCHAR(MAX) and move them to out of row storage. They're just making a train wreck out of the Clustered Index especially if they suffer "ExpAnsive Updates". Even if they're in the "Insert Only" category, they're still slowing down searches in the Clustered Index and if they total up to something larger than 8kBytes, some of them are going to go out of row anyway.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2019 at 6:16 am
1 yes the index on Product_Name wee a bit of problem in future if it exceeds the 900 bytes.But index was created for
Product_Name with warnings.whether sqlserver will use these index for Product_Name Search?
2 if i increase the size of all Custom Columns to NVARCHAR(MAX) will improve performance of Product_Name search?.How can we move them to out of row storage?
February 6, 2019 at 6:22 am
jkramprakash - Wednesday, February 6, 2019 6:16 AM1 yes the index on Product_Name wee a bit of problem in future if it exceeds the 900 bytes.But index was created for
Product_Name with warnings.whether sqlserver will use these index for Product_Name Search?
2 if i increase the size of all Custom Columns to NVARCHAR(MAX) will improve performance of Product_Name search?.How can we move them to out of row storage?
1) Maybe it will, but not with your current code.
2) NO! Changing that will give you a small enhancement to performance (very small), but it won't fix the fundamental issue. You're storing data poorly which is causing you to retrieve it even more poorly.
We're dancing around the edges of the problem. Why is your structure laid out like this? Why do you have to try to clean the data as you query it? What is the root purpose of this data? Are you ready to make changes to the structures which are going to be needed in order to improve performance?
I know what you're looking for is a switch you can throw that will make it all better. There is not one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2019 at 11:06 pm
Grant Fritchey - Wednesday, February 6, 2019 6:22 AMjkramprakash - Wednesday, February 6, 2019 6:16 AM1 yes the index on Product_Name wee a bit of problem in future if it exceeds the 900 bytes.But index was created for
Product_Name with warnings.whether sqlserver will use these index for Product_Name Search?
2 if i increase the size of all Custom Columns to NVARCHAR(MAX) will improve performance of Product_Name search?.How can we move them to out of row storage?1) Maybe it will, but not with your current code.
2) NO! Changing that will give you a small enhancement to performance (very small), but it won't fix the fundamental issue. You're storing data poorly which is causing you to retrieve it even more poorly.We're dancing around the edges of the problem. Why is your structure laid out like this? Why do you have to try to clean the data as you query it? What is the root purpose of this data? Are you ready to make changes to the structures which are going to be needed in order to improve performance?
I know what you're looking for is a switch you can throw that will make it all better. There is not one.
Agreed on #2. Moving the custom columns out of row will NOT help the current query as written. It also won't hurt it much (especially with how poorly it's currently written). It will keep "ExpAnsive Updates" of the custom columns from wreaking page split havoc on the CI though. It will also make any queries that don't use the custom columns absolutely fly because there will be many more rows per page.
One of the things that I absolutely don't understand is why they're doing replaces on both the product name column and the variable for such things like the TM symbol. They really need to cleanup and homogenize their data.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2019 at 4:19 am
Jeff Moden - Wednesday, February 6, 2019 11:06 PMOne of the things that I absolutely don't understand is why they're doing replaces on both the product name column and the variable for such things like the TM symbol. They really need to cleanup and homogenize their data.
I'm sure this won't shock you, but 100% agreement. Rather than killing yourself trying to desperately find a way to tune a query that just will not tune, fix the code & structures that are the root of the problem. It's like fixing a leaky roof by fiddling with the windows or shoring up a shaky foundation by painting the walls. Focus on the root of the problem.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply