Monday, January 21, 2013

Slow Query Performance in SqlDataReader vs. Management Studio

Today I was working on a query for a web application, I noticed that the query which executes within a second in Management Studio, was taking nearly a minute when executed by a stored procedure called via SqlDataReader.ExecuteReader(). I was surprised to see the result as I had not seen such huge difference of time in query execution. The query performs a LIKE operation on an indexed computed NVARCHAR column. 

The SET ARITHABORT setting which seems to take a different value in Management Studio and .Net libraries. The ARITHABORT setting is by itself a source of many questions.

The setting of SET ARITHABORT is set at execute or run time and not at parse time.

SET ARITHABORT must be ON when creating or manipulating indexes on computed columns or indexed views.

After adding a SET ARITHABORT ON statement to the stored procedure, the query performed the same in both conditions.

No comments:

Post a Comment