Slow performance of SqlDataReader
I've query executing ~2 secs in MSSMS (returning 25K of rows)
Same query used in .NET (sqlReader) exetuting few minutes!
I've also tried to execute only reader
(commented all code in while loop just leaving reader.Read() ) - still same!
Any idea what's up?
I'm not DBA and not priviledged to play with Profiler - will ask my DBA and let all know.
In the meantime I'm noticed essential performance boost after adding "WITH RECOMPILE" param to SP I'm talking
So, from my perspective it seems to be the case with execution plan... What do you think?
[EDIT] Also what I've checked was performing below query from QA and .NET
select @@options
My understanding is it shall return same value for both environements. (If not differnet ex.plans will be used) Am I right?
[EDIT2] I've read (from http://www.sqldev.net/misc/fn_setopts.htm) that ARITHABOIRT=ON in QA (in .NET it is off)
Does enybody know how to force ARITHABOIRT=ON for every .NET connections?
Answer
I would set up a trace in SQL Server Profiler to see what SET options settings the connection is using when connecting from .NET code, and what settings are being used in SSMS. By SET options settings, I mean
ARITHABORT
ANSI_NULLS
CONCAT_NULL_YIELDS_NULL
//etc
Take a look at MSDN for a table of options
I have seen the problem before where the options were different (in that case, ARITHABORT) and the performance difference was huge.