https://www.brentozar.com/blitzcache/multiple-plans/

Im optimising highly used queries and noticed this (please see in the attachment)

LLBLGen is specifying the decimal with different precisions according to the value.

How to prevent this?

The code that is causing this is:

 ent.Fields[(int)BlahFieldIndex.Money].ExpressionToApply = (BlahFields.Money + moneyDecimal);

where moneyDecimal is a c# decimal

Version 5.6.1

If it's always the same field, it won't have different precisions, so these must be different fields, could you check that please? (like run the same code with multiple values, show us the real code and the real SQL query that's been executed with parameter types.

Reproduced in the example project attached.

as the decimal increases so does the precision:

exec sp_executesql N'UPDATE [TestDB].[dbo].[FactPlayerGame] SET [PlayCount]=([TestDB].[dbo].[FactPlayerGame].[PlayCount] + @p2), [Stake]=([TestDB].[dbo].[FactPlayerGame].[Stake] + @p4) WHERE ( ( ( ( ( ( [TestDB].[dbo].[FactPlayerGame].[DateId] = @p5 AND [TestDB].[dbo].[FactPlayerGame].[BrandId] = @p6) AND [TestDB].[dbo].[FactPlayerGame].[PlayerId] = @p7) AND [TestDB].[dbo].[FactPlayerGame].[BrandGameId] = @p8) AND [TestDB].[dbo].[FactPlayerGame].[ChannelTypeId] = @p9)))',N'@p2 int,@p4 decimal(2,2),@p5 bigint,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 tinyint',@p2=1,@p4=0,@p5=1,@p6='FB5A6A0A-120E-4C59-A5F4-9C778D6CCF81',@p7='5E9A9853-1911-4920-9F0E-A05D1FE85E02',@p8='71983B2A-F684-42A2-B420-3AE65B4653E8',@p9=1

@p4=0 @p4 decimal(2,2)

exec sp_executesql N'UPDATE [TestDB].[dbo].[FactPlayerGame] SET [PlayCount]=([TestDB].[dbo].[FactPlayerGame].[PlayCount] + @p2), [Stake]=([TestDB].[dbo].[FactPlayerGame].[Stake] + @p4) WHERE ( ( ( ( ( ( [TestDB].[dbo].[FactPlayerGame].[DateId] = @p5 AND [TestDB].[dbo].[FactPlayerGame].[BrandId] = @p6) AND [TestDB].[dbo].[FactPlayerGame].[PlayerId] = @p7) AND [TestDB].[dbo].[FactPlayerGame].[BrandGameId] = @p8) AND [TestDB].[dbo].[FactPlayerGame].[ChannelTypeId] = @p9)))',N'@p2 int,@p4 decimal(5,2),@p5 bigint,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 tinyint',@p2=1,@p4=294.00,@p5=1,@p6='FB5A6A0A-120E-4C59-A5F4-9C778D6CCF81',@p7='5E9A9853-1911-4920-9F0E-A05D1FE85E02',@p8='71983B2A-F684-42A2-B420-3AE65B4653E8',@p9=1

@p4=294.00 ,@p4 decimal(5,2)

I get the same out of ServiceStack ORM Lite and i don't see the problem nor how you would actually fix this other than always specify a decimal as matching SQL Money types precisions?

Let me investigate further...

Thanks for your fast response

Analyzing the code path your example follows it will end up in the parameter creation method which creates a parameter for a given value but without any type information. So it will take the decimal and create a parameter for 'Decimal' without precision/scale information and will set the Value property of the parameter with the value set in the expression.

It can't use the field's type as it doesn't know if that's relevant. (there's no analysis of the left side of the expression, which might be an expression of itself resulting in a different type than the field's).

So the parameter's precision and scale you're seeing is the result of setting the Value property of the SqlParameter object to a decimal value. This is indeed not ideal.

If you want to correct this, you can use a derived class from the 'Expression' class in our runtime framework and override ToQueryText(bool). First call the base method to create the string and the parameters. The parameters are in the property 'Parameters'. You can modify them at that point, e.g. always set the precision to 28 and the scale to 2 if they're of type decimal. Then simply return the string returned by the base method.

In your query, use an instance of your expression class instead of the one in the framework (you have to use the Expression's constructor tho, but you can create a method for this to make it easier to use it).