Slow Query Performance in SqlDataReader vs. Query Analyzer / Management Studio

When working on a query for a web application, I noticed that the same query which executes within a second in Query Analyzer, would take nearly a minute when executed by a stored procedure called via SqlDataReader.ExecuteReader().

I was surprised because I had never seen such a huge difference in query execution times.

The query performs a LIKE operation on an indexed computed NVARCHAR column.

Fortunately, this SO question gave a hint at the SET ARITHABORT setting which seems to take a different value in Query Analyzer and .Net libraries.

The answer points to a deleted page which itself seems to be the answer to other questions and has thankfully been preserved in the Web Archive. (You need to Select All or disable CSS to read the text). The ARITHABORT setting is by itself a source of many questions on SO.

MSDN describes the aspect causing performance differences in my case as:

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.

For more information, see also the MSDN article SET Options That Affect Results.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: