May 14, 2013 at 1:38 am
Hello
I have a simple linq query :
from c in list
where DATE >= DA && DATE <= A
select c.ID).Distinct().Count()
When i run this query, with profiler i see that the execution time is about 10 seconds.
But if i get the generated sql query and launch it in management studio, execution time is 1 second!!!
I've seen (with profiler) the execution plan generated by the first query and it's different by the execution plan of the same query launched in ssms
Any idea???
Thanks
Andrea
May 14, 2013 at 3:37 am
One reason for it can be that the application and SSMS are using different set options for the connections. Most of the time it is the setting of arithabort. By default it is set to off, but SSMS set it on. Try to trun it on on SSMS (with the command - set arithabort off), and check the query plan. If you won't see any difference, try to compare the set option that the application uses with the set options of you ssms window. You can do it with sys.dm_exec_sessions DMV
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 14, 2013 at 4:04 am
Great!
This is the reason. I tried to execute the same query in ssms setting arithabort of and i've had the same execution plan as the profiler.
Do you know how can i try to set Arithabort on with EF?
I'm trying with Context.ExecuteStoreCommand but doesn't work
Even if i set Arithabort on via Database options, it doesn't work
Thanks a lot
May 14, 2013 at 5:36 am
You can try to set the option arithabort on in your application when you open the connection. There is also the possibility of changing it on server level, by modifying the configuration option – "user options", but take into consideration that if you do that, you should test the effect of it for everything that is running on the server.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply