Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Queries with Multiple Execution Plans
 

Pages: 1
Bugs & Issues
Queries with Multiple Execution Plans
Page:1/1 

  Print all messages in this thread  
Poster Message
tnero
User



Location:

Joined on:
09-Apr-2013 17:01:33
Posted:
11 posts
# Posted on: 09-Apr-2020 12:34:58. Goto attachments  
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:


Code:

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


where moneyDecimal is a c# decimal

Version 5.6.1

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
tnero
User



Location:

Joined on:
09-Apr-2013 17:01:33
Posted:
11 posts
# Posted on: 09-Apr-2020 16:56:08. Goto attachments  
Reproduced in the example project attached.

as the decimal increases so does the precision:

Code:

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)

Code:

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)
  Top
tnero
User



Location:

Joined on:
09-Apr-2013 17:01:33
Posted:
11 posts
# 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


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38047 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.