Previously , we were using older version of LLBLGen pro (v2.5 to be precise). We did an upgrade to v4.0.

After upgrade we found that in the select queries, SET ARITHABORT ON; is missing, due to which we are experiencing performance problems.

In the older version, this was available in SELECT, INSERT, DELETE and UPDATE Queries, but in the newer version, this is only available in INSERT, DELETE and UPDATE Queries.

Is there a workaround to get this in SELECT sql queries as well or we need to change the SD.LLBLGen.Pro.DQE.SqlServer library source code.

You do have the boolean set on the SQL Server DQE to enable ARithabort ?

i have already set the boolean ArithAbortOn to True, even after this, its not working for select queries.

while comparing the source code of library , i found the above comment.

in v4.0 //Flag to signal the DQE to generate SET ARITHABORT ON statements prior to INSERT, DELETE and UPDATE Queries

in v2.5 //// Flag to signal the DQE to generate SET ARITHABORT ON statements prior to SELECT, INSERT, DELETE and UPDATE Queries.

i believe then its been blocked and now it wont be possible to set Arithabort for select queries ,until i write additional code in library.

Any comments.

The ARITHABORT flag will explicitly add SET ARITHABORT ON/OFF; statements in the query, if it's set to true. It's a way to switch it ON for a DML query when it's set to OFF by default in SQL Server.

It can be switched on by default in SQL Server: In SSMS, right-click the database after connecting -> properties -> Connections -> Check Arithmetic Abort.

This will make sure the setting is always on, if you want to.

But I'd recommend reading this: http://www.sommarskog.se/query-plan-mysteries.html to get to the bottom of this setting.

I checked the comment line on ArithAbort, and it's the same since 2004, the one in v4's code. From v2.5's DQE:

/// Flag to signal the DQE to generate SET ARITHABORT ON statements prior to INSERT, DELETE and UPDATE Queries.

It's never used in SELECT queries. The setting was added to make sure inserts / updates in tables which were used in indexed views succeeded without errors. Microsoft suggests with SQL Server 2012 to enable Arithmetic Abort on logon, so this setting isn't useful in newer sql server versions: http://msdn.microsoft.com/en-us/library/ms190306.aspx

I am attaching the source code for DynamicQueryEngine.cs which we were previously using. I have applied the same code changes as done in function CreateSelectDQ.

if (ArithAbortOn && !callFromSubQuery) { queryText.Append("SET ARITHABORT ON; "); }

After the change, now Arithabort is coming before select queries. May be this library source code is even older than v2.5, but the code changes does solve my problem.

Please comment if there is no impact of this code fix.

We'll not re-add it to v4's code, as the easiest way to do so is through setting the Arithmetic error setting on the connection at the db level (see previous post). Adding it to the DQE will perhaps break applications so we can't do that mid-version (i.e. the users who set it to true but don't expect it on select queries, as is the case since 2004).

The code snippet you refer to is indeed in your DQE (which has other customizations) but isn't in ours (v2.5), so I think it's been added manually by the person who customized the code in other places as well.

I'd recommend to set the setting to true at the connection properties level in the DB (see previous post) however please read that entire article before opting to do so. It shouldn't be a mandatory setting to switch ON to get expected performance levels.