Are there obvious reasons why calling a stored procedure using my entity model will result in much slower performance than directly invoking it?
First, I do not expect the SP to work at exactly the same speed, and I know that there are different things that EF should do that will not be called when directly accessing the SP.
Aside, I have a query that returns three column columns. It runs very quickly when I execute it through Enterprise Manager. If I run it through EF, then it takes about six seconds. Of course, the results are displayed in a complex type, but when I executed the query through SQL Server Profiler, it is clear that the delay occurs on the SQL server:
In diagram 1, it indicates that SQL is being called from Enterprise Manager, 2 indicates that it is being called through my application using EF.
Is there something obvious I'm doing wrong here? I would expect a delay, perhaps a second or two, but the difference seems too big.
EDIT:
It appears that the stored procedure also starts slowly when called through ADO.Net. My colleague seems to think that this is due to the poor execution plan that .Net caches. By editing the stored procedure and saving it, it seemed again that everything that was in the cache was cleared, and both calling ADO.Net and EF to the stored procedure worked well.
Has anyone else come across something like this?
source
share