Tuesday, 7 July 2009

SQL Server 2008 Query performance / execution speed difference between SQL Management Studio and ADO.NET SQL Data Provider for the same query.

Just had a really annoying problem where a stored procedure that I invoked from SSMS runs in 264ms, but from ADO.NET it runs in 1445ms!  Huge difference!

I researched and found that ‘SET ARITHABORT ON’ is set by default under SSMS, but not ADO.NET.  Once I included this in the SP, the execution time became consistently ~265ms.

However, I think there must be a problem with the query itself, so not a permanent fix, however, it’ll do for now, as I need to get beta 1 of my project released and it’s getting late!

I will blog again once I have found the root cause.

Note to self! SSMS and ADO.NET configure their connection contexts differently; and this will usually be the source of query behavioural / timing differences!