Performance of stored procedures & Entity Framework
Are there any obvious reasons why calling a stored procedure via my entity model would result in far slower performance than calling it direcly?
Firstly, I don't expect the SP to run at exactly the same speed and I know that there are various things that EF has to do that wouldn't be called when accessing the SP directly.
That aside, I have a query that returns three columns of strings. It runs pretty much instantly when I execute it via Enterprise Manager. If I run it via EF then it takes about six seconds. Granted, the results are being mapped into a complex type but when I've ran the query through SQL Server Profiler it's clear to see that the delay happens on the SQL server:
On the diagram, 1 indicates the SQL being called from Enterprise Manager, 2 indicates it being called via my app using EF.
Is there anything obvious I'm doing wrong here? I'd expect a delay of maybe a second or two, but the difference seems too great.
EDIT:
It seems that the stored procedure also runs slowly when called via ADO.Net. My colleague seems to think it's something to do with a bad execution plan that .Net is caching. By editing the stored procedure and saving it again it seemed to clear whatever was in the cache and both the ADO.Net and EF call to the stored procedure work well.
Has anyone else encountered anything like this before?
Take a look at this thread on SQL Server forum. It's a bit similar and might give some clues. In short, you may have different SQL Server execution environment options in SSMS and ADO.NET leading to different execution plans. Clearing the SQL Server plan cache should help.
- How to loop through all distinct triplets of an array such that they are of the format (a, b, b)? Length of array <= 10^6
- Speedwise, how does Pharo compare to Python3?
- size of a struct in Go without initialising a variable of struct
- Performance Optimization for Minimax Algorithm in Tic-Tac-Toe with Variable Board Sizes and Win Conditions
- Performance issue using HPX for parallelization in C++ code
- Parallel streams slower than sequential streams when generating many random numbers
- How to calculate the gradient of an n-dimensional array of data in R
- Substring Without any Allocation Using Span<T>
- In the C if-else statement, should the condition which is more likely to be true come first?
- SQL PERFORMANCE - alternative to OR statement
- GitHub REST API: Get issue based only on title
- How to accelerate the cross-correlation computation of two 2D matrices in Python?
- Parse JSON efficiently
- ADB stopping at <waiting for devices>
- How to increase the performance of a Database?
- std::find_if vs manual loop, which one is better?
- Redundant/Better Performance Code VS Optimized/Less Performance Code
- A Faster way of Directory walking instead of os.listdir?
- How to prevent automatic handling of newlines killing performance in Perl text output?
- Efficiently count lists with certain properties
- How to explain pandas higher performances compared to numpy with 500k+ rows?
- Series of if vs else-if statements benchmarking
- Will the C++20 spaceship operator be used for equality/inequality comparisons?
- When you have an AMD CPU, can you speed up code that uses the Intel-MKL?
- Debug vs. Release performance
- C Program Runs Surprisingly Slow
- ConcurrentDictionary.AddOrUpdate method in C# how efficient is it?
- Generating all permutations efficiently
- How to efficiently count the number of keys/properties of an object in JavaScript
- Optimizing The Exact Prime Number Theorem