Linq methods add vs. stored procedure add

Question

I am using Entity Framework and I have a question about performance.

Here is my linq add method:

CarTable newCar = new CarTable()
            {
                CarPlate = plate,
                CarModel = model,
                CarColor = color,
                CarImage = image
            };

entity.CarTable.Add(newCar);
entity.SaveChanges();

And here is a stored procedure for the same purpose:

CREATE PROC prCreateCar
    (@plate NVARCHAR(20), 
     @model NVARCHAR(50), 
     @color NVARCHAR(10), 
     @image NVARCHAR(MAX))
AS
BEGIN
     INSERT INTO CarTable
     VALUES (@plate, @model, @color, @image)
END

And calling it from asp.net with this:

entity.prCreateCar('34 F5 3498','Renault Clio','Blue','Images/Cars/clio.png');

Both of them working fine but here is my question.. which one gives better performance? I heard that stored procedures are faster but is it true? If it is true, is it faster in all conditions like SELECT, DELETE, UPDATE and INSERT ?

measure it yourself. See http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures and http://stackoverflow.com/questions/752064/performance-difference-between-linq-and-stored-procedures — Habib, Apr 05 '16 at 14:31
It will depend on the **actual RDBMS** you're using underneath Entity Framework - but if you're doing it this way, for SQL Server, I assume performance will be very comparable. Both approaches ultimately result in a parametrized query that needs to be executed - and both ways, if done properly, will behave very much alike. — marc_s, Apr 05 '16 at 14:36
But one word of caution: *why* are you using `nvarchar(max)` for your image!?!?!? That's a **binary** set of data - which would be better served by using `varbinary(max)` (instead of a string-based datatype) — marc_s, Apr 05 '16 at 14:36
It holds image's path so it is not a binary set of data and thank you for your reply, It is the answer I am looking for :) @marc_s — Batuhan Ozdal, Apr 05 '16 at 14:40
EF has some overhead, but an insert like this is not even worth to be measured (in case of many inserts, you wouldn't do the bulk operations neither with EF nor with a procedure like that anyway - that procedure is not different from using a plain dynamic SQL). — Cetin Basoz, Apr 05 '16 at 14:41
The answer to all three questions is "it depends". Use whichever method makes you the most productive, and deal with performance issues once you can identify that it is a measurable bottleneck. — D Stanley, Apr 05 '16 at 14:44
How can I make a better insert ? Can you send a link for a example or something ? :) @CetinBasoz — Batuhan Ozdal, Apr 05 '16 at 14:47
OK, so in that case the **name** of the parameter is not very optimal - if it really only contains a *path* to an image, I'd recommend `@ImagePath` or something like this, to make it crystal clear that this is not the actual image itself.... — marc_s, Apr 05 '16 at 15:06

score 0 · Accepted Answer · answered Apr 05 '16 at 17:05

For such simple queries there likely won't be much of a difference. If you have more complex queries (joins across lots of different tables/sub queries/etc.) that merely use different parameters performance of Stored Procedures usually is better as the Database only needs to create an execution plan once and can then cache it, while it would be recreated for each regular SQL query.

If you have to do lots of inserts in a row, e.g. when importing data from an outside source (catalog data or something similar) you might want to use BULK INSERT statements. There are also extensions to EF that allow using that feature in a way similar to regular EF operations - I have no practical experience with those though. One example would be EntityFramework.BulkInsert

Linq methods add vs. stored procedure add

1 Answers1