Entity Framework or Stored Procedure

Question

Entity Framework or Stored Procedure

Navigation

#1 by (5 votes)
#2 by (5 votes)

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 procedure:

-- =============================================
-- 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

For examples I can call EF consuming the procedure and I can do lambda, my doubt, in a scenario that I need only quick consultation are the two the same thing? If so, what would be the gain of having a precompiled procedure?

I'm using Dapper and put the same SELECT of the procedure and validated that well faster than using EF for queries. Even in the course I did with Eduardo Pires he recommends using Dapper for queries and DELETE INSERT UPDATE using 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 question is the use of Procedure with EF is same thing as doing EF with lambda? Is there any gain?

Does the use of Dapper simulating the content of the Procedure have any gain?

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

sql entity-framework

asked by anonymous 22.01.2015 / 15:22

2 answers

5

I wonder if Stored Procedures really are better at performance than EF.

Short Version : YMMV, Your mileage may vary (or, your experience may be different 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
Internally developed Micro-ORM layer

During our performance tests, we evaluated the possibility of creating packages for CRUD operations and querying, using parametric queries and direct statements . Assume, for the right purposes, that we have a competent DBA and that the database is properly optimized and the development team is following the schema recommendations (indexing, field types, etc.) / p>

These were our results, for 1 million calls, in a table containing 780,000 records, with pipeline set to 20 parallel calls. Ignore the negative aspect of security presented by the concatenation of parameters in the third method - it was used for performance measurement 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.

Performance for direct statements was expected, as execution plans are recompiled for each call. The surprise was that parameterized queries were faster.

After analysis, the reason is obvious: while both SPs and PQs undergo and reuse of execution plans, PQs do not involve packets, thus eliminating an extra layer of per-scope scope evaluation.

Performance for change 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 in an analogous way, using precompiled and cached plans by DBMS .

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

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

22.01.2015 / 16:14

How to make a database connection using the Singleton project pattern Search date 7 days before the current date php

score 5 · Accepted Answer

In the original question there was exactly that part in the content. For historical reasons, I'm keeping this piece of the old question because it's relevant to the rest.

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

Are. Dapper, another object-relational mapping Framework, places this in a Github presentation file, that can be read here .

Simply comparing the execution of a Stored Procedure with an integer procedure from an ORM is not very fair. In order to execute a Stored Procedure inside a system, you need to instantiate some objects that will communicate with the database, send the command, and sometimes collect the execution result, which can be a status code or results in the form of tables. The ORM, in turn, solves a number of procedures that you would have to do manually. Obviously it will not be that fast.

Now, eliminating an ORM just for performance is priceless. For most systems, the performance difference is not even noticed, especially if you are developing applications with the latest versions of Frameworks.

My question is use of Procedure with EF is same thing to do EF with lambda? Is there any gain?

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

The gain is in code readability. It's much more interesting for the programmer to build code-level logic than to have to abstract technology-dependent queries for performance.

Other: Does the Dapper use simulating the content of the Procedure have any gain?

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