Stored procedure against forests

Question

I am using ASP.NET Core 1.0 and the site will be large (about 5,000 visitors per hour are expected). I have read many times that stored procedures are very fast and safe (in terms of SQL injection, etc.). But the code is EF first, if so easy that I want to use it, but the CRUD methods are hidden.

What will be the disadvantages (if any) of using scaffolding from EF to SP in terms of performance and safety?

Answer 1

I like to think of the choice between writing stored procedures and writing requests in EF as a similar (in some way) difference between programming in assembler and writing programs that run in a managed environment (like .NET and Java). Applications written for managed environments are always slower than written in assembly language, but, on the other hand, it is usually easier (and therefore cheaper) to write complex applications using managed languages. With EF linq, you will write your queries much faster, and they will usually be simpler than their sql equivalents, and therefore easier to maintain. Another important factor is that applications change rapidly, new functionalities are added, old ones are overwritten and together with these process tables and other structures of DB structures. When that happensyour SP will no longer be able to work correctly. When do you know that they need to be fixed? Only when you start them. When do you know that you need to fix the EF query because the table is changing? At compile time. And the performance lost? As a rule, these days we have fast and fairly cheap cars, so we don’t care. I examined once the execution time of an EF request and its equivalent sql - simple sql reduced the execution time, for example 5% - this is nothing for me. And since sql queries are much more complex, there is a high probability that your query will be spelled incorrectly so that it is unnecessary slower. And finally, if you need really excellent performance in your application for some complex query, write it as SP and easily execute it using EF.When do you know that they need to be fixed? Only when you start them. When do you know that you need to fix the EF query because the table is changing? At compile time. And the performance lost? As a rule, these days we have fast and fairly cheap cars, so we don’t care. I examined once the execution time of an EF request and its equivalent sql - simple sql reduced the execution time, for example 5% - this is nothing for me. And since sql queries are much more complex, there is a high probability that your query will be spelled incorrectly so that it is unnecessary slower. And finally, if you need really excellent performance in your application for some complex query, write it as SP and easily execute it using EF.When do you know that they need to be fixed? Only when you start them. When do you know that you need to fix the EF query because the table is changing? At compile time. And the performance lost? As a rule, these days we have fast and fairly cheap cars, so we don’t care. I examined once the execution time of an EF request and its equivalent sql - simple sql reduced the execution time, for example 5% - this is nothing for me. And since sql queries are much more complex, there is a high probability that your query will be spelled incorrectly so that it is unnecessary slower. And finally, if you need really excellent performance in your application for some complex query, write it as SP and easily execute it using EF.what do you need to fix the EF query because the table is changing? At compile time. And the performance lost? As a rule, these days we have fast and fairly cheap cars, so we don’t care. I examined once the execution time of an EF request and its equivalent sql - simple sql reduced the execution time, for example 5% - this is nothing for me. And since sql queries are much more complex, there is a high probability that your query will be spelled incorrectly so that it is unnecessary slower. And finally, if you need really excellent performance in your application for some complex query, write it as SP and easily execute it using EF.what do you need to fix the EF query because the table is changing? At compile time. And the performance lost? As a rule, these days we have fast and fairly cheap cars, so we don’t care. I examined once the execution time of an EF request and its equivalent sql - simple sql reduced the execution time, for example 5% - this is nothing for me. And since sql queries are much more complex, there is a high probability that your query will be spelled incorrectly so that it is unnecessary slower. And finally, if you need really excellent performance in your application for some complex query, write it as SP and easily execute it using EF.therefore we do not care. I examined once the execution time of an EF request and its equivalent sql - simple sql reduced the execution time, for example 5% - this is nothing for me. And since sql queries are much more complex, there is a high probability that your query will be spelled incorrectly so that it is unnecessary slower. And finally, if you need really excellent performance in your application for some complex query, write it as SP and easily execute it using EF.therefore we do not care. I examined once the execution time of an EF request and its equivalent sql - simple sql reduced the execution time, for example 5% - this is nothing for me. And since sql queries are much more complex, there is a high probability that your query will be spelled incorrectly so that it is unnecessary slower. And finally, if you need really excellent performance in your application for some complex query, write it as SP and easily execute it using EF.if you need really excellent performance in your application for some complex query - write it as SP and easily execute it using EF.if you need really excellent performance in your application for some complex query - write it as SP and easily execute it using EF.

EF sql-, SQL- SP.

Answer 2

SQL, SQL-. "" SQL- . , Entity Framework, , SQL.

Entity Framework . , , , , .

Stack Exchange Dapper, , Entity, . SQL. . Dapper readme. , , .

. - , . , - , .

, Entity Framework - , . , - . , ().

Dapper SQL-?

, , . Dapper . .

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
  )

, , SQL-:

connection.Execute(@"insert MyTable(colA, colB) values ('" + a + "', '" + b + "')")

Entity Framework?

, , , ExecuteQuery, .

?

, SQL-, SQL .

, , ORM SQL-: http://www.troyhunt.com/2012/12/stored-procedures-and-orms-wont-save.html

( ) EF SP ?

, . , CRUD.

, SQL, Entity Framework. , , , SQL Entity . , , , . CRUD , .

, Entity Framework , , - . , . .

Answer 3

You can use scaffolding to create databases and views and replace calls with built-in methods with your own methods.