Thanks Dave,

As you mention the different paramaters would cause a difference, that makes sense. The query executed in SSMS was about 10 seconds faster than through the .net programme. As the previous post mentions, the SET ARITHABORT ON; setting is the difference between the default running through .net and SSMS. - It does seem to be an interesting item.

If I'm honest the whole query is massivly complicated (IF...IF...ELSE etc, UNIONS galore and subqueries everywhere....plus the odd function thrown in here and there) so I'm breaking it down so each of the key parts can have a proper plan and variables won't be included in the results plans. I think this will resolve the slow running anyway, along wtih some analysis of stats and indexes, but it doesn't really answer my ever deepening need for an understanding of why SET ARITHABORT ON; has the potential to increase query performance.

I've already made quite a few adjustments to the indexes and stats and the query is now working much better with SET ARITHABORT ON; or SET ARITHABORT OFF; but this still leaves the question open on why it would be faster wtih it set as on???

Thanks again!