Queries with Multiple Execution Plans

Posts   
 
    
tnero
User
Posts: 18
Joined: 09-Apr-2013
# Posted on: 09-Apr-2020 12:34:58   

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

Attachments
Filename File size Added on Approval
Screenshot_1.png 21,674 09-Apr-2020 12:35.05 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2020 15:58:12   

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.

Frans Bouma | Lead developer LLBLGen Pro
tnero
User
Posts: 18
Joined: 09-Apr-2013
# Posted on: 09-Apr-2020 16:56:08   

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)

Attachments
Filename File size Added on Approval
Projects.zip 66,383 09-Apr-2020 16:56.20 Approved
tnero
User
Posts: 18
Joined: 09-Apr-2013
# Posted on: 09-Apr-2020 17:13:13   

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

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Apr-2020 10:18:50   

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).

Frans Bouma | Lead developer LLBLGen Pro