The results set is only 16 records with 12 fields but it's all aggregated data, which is the only reason I can see the ARITHABORT setting causing an issue either way. When run with it off it's slower than with it on and the report in question 'home made' in ASP.net
When re-reading http://www.sommarskog.se/query-plan-mysteries.html it mentions that the ARITHABORT could cause paramater sniffing issues:
In practice, the most important SET option is ARITHABORT, because the default for this option is different in an application and in SQL Server Management Studio. This explains why you can spot a slow query in your application, and then run it at good speed in SSMS. The application uses a plan which was compiled for a different set of sniffed parameter values than the actual values, whereas when you run the query in SSMS, it is likely that there is no plan for ARITHABORT ON in the cache, so SQL Server will build a plan that fits with your current parameter values.
You have also understood that you can verify that this is the case by running this command in your query window:
SET ARITHABORT OFF
and with great likelihood, you will now get the slow behaviour of the application also in SSMS. If this happens, you know that you have a performance problem related to parameter sniffing.
So, in theory, my initial thought to split out the query so each IF has it's own proc (as mentioned by Grant at SQL in the city London 😉 )may well resolve the problem as each part will have it's own plan..... but I'm still in a quest for knowledge about this one 🙂
Thanks again for you help Dave!