Need help in execution plan: ConvertIssue="Cardinality Estimate"
-
Hi,
I've table as following,
CREATE TABLE [dbo].[SFATenderDetail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[SFAProjectId] [int] NOT NULL,
[TenderCode] [varchar](200) NOT NULL,
[ClosingDate] [date] NULL,
[TenderName] [varchar](500) NOT NULL,
[ImplementPeriod] [int] NULL,
[ContractPeriod] [int] NULL,
[TenderProposedValue] [decimal](18, 2) NULL,
[ExpectedStartDate] [date] NULL,
[ScopeOfWork] [nvarchar](max) NULL,
[ImplementPlanning] [nvarchar](max) NULL,
[WinningImplementStrategy] [nvarchar](max) NULL,
[CrtDte] [datetime] NULL,
[UpdDte] [datetime] NULL,
CONSTRAINT [PK_SFATenderDetail] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [SFATenderDetail_UQ001] UNIQUE NONCLUSTERED
(
[SFAProjectId] 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
ALTER TABLE [dbo].[SFATenderDetail] ADD CONSTRAINT [DF_SFATenderDetail_ClosingDate] DEFAULT (getdate()) FOR [ClosingDate]
GO
ALTER TABLE [dbo].[SFATenderDetail] ADD CONSTRAINT [DF_Table_1_ImplementMonth] DEFAULT ((0)) FOR [ImplementPeriod]
GO
ALTER TABLE [dbo].[SFATenderDetail] ADD CONSTRAINT [DF_SFATenderDetail_ContractMonth] DEFAULT ((0)) FOR [ContractPeriod]
GO
ALTER TABLE [dbo].[SFATenderDetail] ADD CONSTRAINT [DF_SFATenderDetail_ExpectedStartDate] DEFAULT (getdate()) FOR [ExpectedStartDate]
GO
ALTER TABLE [dbo].[SFATenderDetail] ADD CONSTRAINT [DF_SFATenderDetail_CrtDte] DEFAULT (getdate()) FOR [CrtDte]
GO
ALTER TABLE [dbo].[SFATenderDetail] ADD CONSTRAINT [DF_SFATenderDetail_UpdDte] DEFAULT (getdate()) FOR [UpdDte]
GO
ALTER TABLE [dbo].[SFATenderDetail] WITH CHECK ADD CONSTRAINT [FK_SFATenderDetail_SFATenderDetail_001] FOREIGN KEY([SFAProjectId])
REFERENCES [dbo].[SFAProject] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[SFATenderDetail] CHECK CONSTRAINT [FK_SFATenderDetail_SFATenderDetail_001]
GOThis is my Stored Procedure: SFATenderDetail_GetTenderDetailById
CREATE PROCEDURE [dbo].[SFATenderDetail_GetTenderDetailById]
@SFAProjectId int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select
--cast(Id as varchar(10)) as Id,
cast(SFAProjectId as varchar(10)) as ProjectId,
[TenderCode], [ClosingDate],
CONVERT(VARCHAR(10), ClosingDate, 103) + ' ' + substring(convert(varchar(20), ClosingDate, 9), 13, 5)
+ ' ' + substring(convert(varchar(30), ClosingDate, 9), 25, 2) as ClosingDateDisplay,
upper([TenderName]) as TenderName,
cast(ImplementPeriod as varchar(10)) as ImplementPeriod,
ImplementPeriod / 12 as ImplementPeriodYear,
cast(ImplementPeriod / 12 as varchar(10)) + ' Year(s) ' as ImplementPeriodDisplayYear,
ImplementPeriod % 12 as ImplementPeriodMonth,
cast(ImplementPeriod % 12 as varchar(10)) + ' Month(s)' as ImplementPeriodDisplayMonth,
cast(ContractPeriod as varchar(10)) as ContractPeriod,
ContractPeriod / 12 as ContractPeriodYear,
cast(ContractPeriod / 12 as varchar(10)) + ' Year(s) ' as ContractPeriodDisplayYear,
ContractPeriod % 12 as ContractPeriodMonth,
cast(ContractPeriod % 12 as varchar(10)) + ' Month(s)' as ContractPeriodDisplayMonth,
TenderProposedValue,
dbo.F_AddThousandSeparators(convert(varchar, convert(decimal(18, 2), TenderProposedValue), 1)) as TenderProposedValueDisplay,
[ExpectedStartDate],
CONVERT(VARCHAR(10), ExpectedStartDate, 103) + ' ' + substring(convert(varchar(20), ExpectedStartDate, 9), 13, 5)
+ ' ' + substring(convert(varchar(30), ExpectedStartDate, 9), 25, 2) as ExpectedStartDateDisplay,
[ScopeOfWork],
[ImplementPlanning],
[WinningImplementStrategy],
[CrtDte],
CONVERT(VARCHAR(10), [CrtDte], 103) + ' ' + substring(convert(varchar(20), [CrtDte], 9), 13, 5)
+ ' ' + substring(convert(varchar(30), [CrtDte], 9), 25, 2) as CrtDteDisplay,
[UpdDte],
CONVERT(VARCHAR(10), [UpdDte], 103) + ' ' + substring(convert(varchar(20), [UpdDte], 9), 13, 5)
+ ' ' + substring(convert(varchar(30), [UpdDte], 9), 25, 2) as UpdDteDisplay
from [ISSB2021].[dbo].[SFATenderDetail]
Where [SFAProjectId] = @SFAProjectId ;
ENDMy function: F_AddThousandSeparators
CREATE FUNCTION [dbo].[F_AddThousandSeparators](@NumStr varchar(50))
RETURNS Varchar(50)
AS
BEGIN
declare @OutStr varchar(50)
declare @i int
declare @run int
Select @i=CHARINDEX('.',@NumStr)
if @i=0
begin
set @i=LEN(@NumStr)
Set @Outstr=''
end
else
begin
Set @Outstr=SUBSTRING(@NUmStr,@i,50)
Set @i=@i -1
end
Set @run=0
While @i>0
begin
if @Run=3
begin
Set @Outstr=','+@Outstr
Set @run=0
end
Set @Outstr=SUBSTRING(@NumStr,@i,1) +@Outstr
Set @i=@i-1
Set @run=@run + 1
end
RETURN @OutStr
ENDIt produce Execution Plan as following,
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[SFAProjectId],0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[ClosingDate],103)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(20),[ISSB2021].[dbo].[SFATenderDetail].[ClosingDate],9)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[ISSB2021].[dbo].[SFATenderDetail].[ClosingDate],9)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[ImplementPeriod],0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[ImplementPeriod]/(12),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[ContractPeriod],0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[ContractPeriod]/(12),0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[ISSB2021].[dbo].[SFATenderDetail].[TenderProposedValue],1)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[ExpectedStartDate],103)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(20),[ISSB2021].[dbo].[SFATenderDetail].[ExpectedStartDate],9)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[ISSB2021].[dbo].[SFATenderDetail].[ExpectedStartDate],9)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[CrtDte],103)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(20),[ISSB2021].[dbo].[SFATenderDetail].[CrtDte],9)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[ISSB2021].[dbo].[SFATenderDetail].[CrtDte],9)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[ISSB2021].[dbo].[SFATenderDetail].[UpdDte],103)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(20),[ISSB2021].[dbo].[SFATenderDetail].[UpdDte],9)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(30),[ISSB2021].[dbo].[SFATenderDetail].[UpdDte],9)" />
</Warnings>Looks like not the best practice. How to improve my SQL statement ?
Please help
- This topic was modified 2 years, 8 months ago by Adelia.
-
Any chance you can convert that multiline scalar function to an inline table value function? Multi-line scalar functions can perform poorly, and your function has a
WHILEinside it, which are known to perform poorly. Though why do you need to add the thousand separator in your SQL layer at all? Such formatting tasks are almost always served better in the presentation layer.In short, do you need to actually convert all those numerical values into a
varcharfor your application layer, when you could have the application display the values as the way you need, as the database pass strongly typed numerical data?Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A wrote:
Any chance you can convert that multiline scalar function to an inline table value function? Multi-line scalar functions can perform poorly, and your function has a
WHILEinside it, which are known to perform poorly. Though why do you need to add the thousand separator in your SQL layer at all? Such formatting tasks are almost always served better in the presentation layer.In short, do you need to actually convert all those numerical values into a
varcharfor your application layer, when you could have the application display the values as the way you need, as the database pass strongly typed numerical data?Hi Sir,
Great explanation. Let me re-write the Query as I can
-
Can you use FORMAT? I have only ever used it in ad-hoc queries to make numbers easier to read, and I can't vouch for its performance, but it seems to do to the trick.
- Ed B wrote:
Can you use FORMAT? I have only ever used it in ad-hoc queries to make numbers easier to read, and I can't vouch for its performance, but it seems to do to the trick.
I strongly recommend against any and all use of FORMAT. It's one of the fastest ways to slow down an otherwise fast query that I know of and I'm not talking about just a little bit. Even on more modern versions of T-SQL and some nasty fast hardware, FORMAT is usually 20 to 28 times slower than even complex usage of CONVERT and other functions. And, when I say "usually", I mean always at least that bad. I've it be as much as 53 times slower and that's just for one instance of the function in code. If you use 2 instances in the same query, it's a cumulative penalty where the two FORMATs actually are twice as bad as just one.
Don't use FORMAT. It's just not worth it even on a supposed small number of rows. It becomes a part of the "Death by a Thousands Cuts" that most databases and applications seem to suffer from.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
I strongly recommend against any and all use of FORMAT.l.
Noted. I almost didn’t suggest it, because I thought I remembered you saying something similar in the past. I only use it for ad-hoc totals and reports that go to my phone. With very few rows the performance doesn’t matter compared to the convenience of being able to read the number quickly without needing glasses.
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
- Ed B wrote:
Can you use FORMAT? I have only ever used it in ad-hoc queries to make numbers easier to read, and I can't vouch for its performance, but it seems to do to the trick.
This would also defeat the point I mentioned of passing strongly typed numerical data to the application layer.
FORMATreturns a string based data type.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply