Entity Framework or Stored Procedure

Asked

Viewed 718 times

2

When to use Entity Framework example:

var registros = db.Tabela.AsQueryable();
registros = registros.Where(r =>
(intTipo == 0 || r.IdTipo == intTipo) &&
r.IdArea == intIdArea &&
r.DataInicio <= DateTime.Now &&
(r.DataFim == null || r.DataFim >= DateTime.Now) &&
r.Aprovado == true &&
r.Homologado == true &&
r.Excluido == false);

When to use Previous:

-- =============================================
-- Author:      Anderson
-- Create date: 22/01/2015
-- Description: Teste
-- =============================================
CREATE PROCEDURE TestedePerformace 
    @intTipo int = 0, 
    @intIdArea int = 0
AS
BEGIN
    SELECT * FROM TABELA  R
    WHERE ( R.INTTIPO = @intTipo OR R.INTTIPO = 0 )
    AND r.IdArea == @intIdArea 
    AND ( r.DataInicio <= DateTime.Now or r.DataInicio = null)
    AND r.Aprovado = 1
    AND r.homologado = 1
    AND r.excluido = 0
END
GO

By the examples I can call the EF consuming the Procedure and I can do lambda, my doubt, in a scenario that I need only quick consultation the two are the same thing? If yes what would be the gain of having a pre-compiled precedent?

I’m using Dapper and put the same SELECT of the project and I saw that much faster than using the EF for consultations. Even in the course I did with Eduardo Pires he recommends the use of Dapper for queries and DELETE INSERT UPDATE use EF with Lambda.

A table with 800 record

//Begin timing
  stopWatch.Start();


  var listaEF     = autorizacaoRepository.All();
  var listaDapper = autorizacaoRepositoryReader.All();
  //Stop timing
  stopWatch.Stop();

            // Write result 
            //Dapper TotalSeconds = 0.0016825
            //EF     TotalSeconds = 3.928575
            //var time = stopWatch.Elapsed;

My doubt is using Procedure with EF is even thing to do EF with lambda? Is there any gain?

Another use of Dapper simulating Procedure content has some gain?

I think I set the example and I just want to contribute to community, because I know that there are many people who defend only one and the other, but when we know better to use everyone in each situation everyone wins.

2 answers

6


In the original question there was exactly this part in the content. For historical reasons, I am keeping this piece of the old question because it is pertinent to the rest.

I wonder if Stored Procedures really are better at performing than EF. Could you help me?

They are. Dapper, another object-relational mapping framework, puts this in a presentation file of them on Github, that can be read here.

Simply comparing the execution of a Stored Procedure with an entire ORM procedure is not very fair. For the execution of a Stored Procedure within a system, it is necessary to instantiate some objects that will make the communication with the bank, send the command, and sometimes collect the result of the execution, which can be a status code or results in the form of tables. The ORM, in turn, solves a lot of procedures that you would have to do manually. Obviously it won’t be so fast.

Now, eliminating a ORM just for performance is precious. For the vast majority of systems, the difference in performance is not even noticed, especially if you are developing applications with the latest versions of Frameworks.

My doubt is using Procedure with EF is even thing to do EF with lambda? Is there any gain?

Using predicate expressions (alias lambda, as you like to say) makes the Entity Framework mount a textual query according to the provider it is connected to. Usually it is in Microsoft SQL Server, but it can be any other (Oracle, Mysql, etc.).

The gain is in code readability. It becomes much more interesting for the programmer to assemble a logic at the code level than having to abstract queries dependent on technology by the question of performance.

Other: the use of Dapper simulating Procedure content has some gain?

The gain is that it translates the results to an object quickly, which I see as the great differential of the Dapper. In the matter of execution, it is identical to the pure execution of the native connection object and commands.

5

I would like to know if Stored Procedures are really better at performing than EF.

Short Version: YMMV, Your Mileage may Vary (or, your experience may differ from mine.)

Long Version: Performance, in a database, depends on a myriad of factors. In this answer I will mention my personal experience.

I have the following target environment:

  • Oracle 12c
  • Dapper
  • Micro-ORM layer developed internally

During our performance tests, we evaluated the possibility of creating packages for CRUD and query operations, the use of parameterized queries and statements direct. Assume, for the proper purpose, that we have a competent DBA and that the database is correctly optimized and the development team is following the recommendations of schema (indexing, field types, etc).

These were our results, for 1 million calls, in a table containing 780,000 records, with pipeline set to 20 calls in parallel. Ignore the negative safety aspect presented by parameter concatenation in the third method - it was used to measure performance only:

Seek

Método               Chamada
[pkg].[proc]         exec smp.fetch_usr
Parametrized Query   SELECT * FROM tbl_user WHERE ID = :parm
Direct Statement     "SELECT * FROM tbl_user WHERE ID = " + parm

Método               Média
[pkg].[proc]         ~18 s.
Parametrized Query   ~17 s.
Direct Statement     ~218 s.

The performance for statements was expected, since the execution plans are recompiled at each call. The surprise was to find that darlings parameterized were faster.

After analysis, the reason is obvious: while both Sps and Pqs suffer caching and reuse of execution plans, Pqs do not involve packages, thus eliminating an extra layer of evaluation of permissions by scope.

Performance for modification operations was similar:

Update

Método               Chamada
[pkg].[proc]         exec smp.upd_usr 
Parametrized Query   UPDATE tbl_user SET desc = :parm2 WHERE ID = :parm1
Direct Statement     "UPDATE tbl_user SET desc ='"+ parm2 + "' WHERE ID = "+ parm1

Método               Média
[pkg].[proc]         ~21 s.
Parametrized Query   ~23 s.
Direct Statement     ~317 s.

Again, Pqs and Sps behaved similarly, using pre-compiled and cached execution plans by DBMS.

As a consequence of the above analysis, it was decided by the development team to adopt darlings parameterized as an interface method between our ORM and the database.

Finally, answering your question: my experience with Entity Framework is limited. However, according to the content of this original OS reply, EF supports the use of Pqs from version 6. You can thus benefit from the EF model and Pqs performance.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.