You are correct in assessment of the field def....a catch all... so how in CODE based upon what I supplied can it be fixed?

Thanks.

Is changing the datatype on design.attValue to int the only way around the issue?

Thanks will do... sorry for the delays in getting this info...

What about comment: from --frederico_fonseca

"

you are never going to go past that error - you are converting from a nvarchar(255)...

not sure how to upload the execution plan.

Still can't get pass this error:

Type conversion in expression (CONVERT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" in query plan choice, The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size:...

Table def's attached.

CREATE TABLE [dbo].[bom](
[design] [nvarchar](120) NOT NULL,
[version] [int] NOT NULL,
[subdesign] [nvarchar](120) NOT NULL,
[designAlternative] [int] NOT NULL,
[subDesignAlternative] [int] NOT NULL,
 CONSTRAINT [PK_bom] PRIMARY KEY CLUSTERED 
(
[design] ASC,
[version]...

I removed the Distinct and tried to cast on attvalue but I'm still getting this error in query plan.

Type conversion in expression (CONVERT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" in query plan choice,...

thanks for update  I'm checking on the why for Distinct..

How to fix this in Query PLan:

Type conversion in expression (CONVERT_IMPLICIT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" in query plan choice.

Design Table:

attValue is Nvarchar(255)

attribute...

I found the offending query when I look at the Query Plan.

Type conversion in expression (CONVERT_IMPLICIT(int,[d2].[attValue],0)) may affect "CardinalityEstimate" in query plan choice.

How do I go about fixing..

THanks.

            
March 7, 2022 at 11:36 pm
            
#3995356

            
            
                                                
        

I see 3 pieces of information in Query Plan for this delete operation.

Delete -- Cost 0%

(@1 nvarchar(4000),@2 nvarchar(4000))D

DELETE [mx_bom] WHERE [PlantId]=@1 AND [ParentPart]=@2

Table - Delete Cost 86%

no real text here...

I ran this query to show me the execution count of the nightly process. The query that was number 1 of the list was a

(@1 nvarchar(4000),@2 nvarchar(4000))

DELETE [bom]

WHERE [PlantId]=@1 AND...

I checked this table it has no FK, or triggers, and character fields are defined as Nvarchar.   There are NO indexes or a PK set of this table.

Are you thinking...

I did want to mention that this machine is a 2 node(8 cores per) machine and the Cost Threshold for Parallelism is set to 50.

Maxdop on server basis is still...

I found another implicit conversion happening on this statement and looking for suggestion on how to improve qry or just the

is not null

WHERE d1.attvalue IS NOT NULL  -- where is...

Thanks for response I'm going to try one int to nvarchar in tonight processing and see what happens.  -- Test of course...