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.
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.