locked
Does Linq use more bandwith than a store procodure? RRS feed

  • Question

  • User-1350042179 posted

    Hi

    When I use linq, I see in sql profiler than a big query is executed...

    exec sp_executesql N'SELECT 
        [GroupBy3].[A1] AS [C1]
        FROM ( SELECT 
            COUNT(1) AS [A1]
            FROM ( SELECT 
                [Project3].[C1] AS [C1], 
                CASE WHEN ( EXISTS (SELECT 
                    1 AS [C1]
                    FROM   (SELECT [Extent19].[IdRequerimientoAlmacen] AS [IdRequerimientoAlmacen], [Extent19].[IdBien] AS [IdBien], [Extent19].[IdMaterial] AS [IdMaterial]
                        FROM  [Adm].[RequerimientoAlmacenComponente] AS [Extent19]
                        INNER JOIN [Adm].[Bien] AS [Extent20] ON [Extent19].[IdBien] = [Extent20].[Id]
                        WHERE ''TIPB0004'' = [Extent20].[IdTipoBien] ) AS [Filter5]
                    LEFT OUTER JOIN [Adm].[RequerimientoAlmacen] AS [Extent21] ON [Filter5].[IdRequerimientoAlmacen] = [Extent21].[Id]
                    WHERE ( NOT ((''ESRA0002'' = [Extent21].[IdEstado]) AND ([Extent21].[IdEstado] IS NOT NULL))) AND ( NOT ((''ESRA0005'' = [Extent21].[IdEstado]) AND ([Extent21].[IdEstado] IS NOT NULL))) AND ([Filter5].[IdBien] = [Project3].[IdBien]) AND ([Filter5].[IdMaterial] = [Project3].[IdMaterial]) AND (([Extent21].[IdAlmacenOrigen] = [Project3].[IdAlmacen]) OR (([Extent21].[IdAlmacenOrigen] IS NULL) AND ([Project3].[IdAlmacen] IS NULL)))
                )) THEN [Project3].[C2] ELSE 0 END AS [C2]
                FROM ( SELECT 
                    [Project2].[C1] AS [C1], 
                    [Project2].[IdBien] AS [IdBien], 
                    [Project2].[IdMaterial] AS [IdMaterial], 
                    [Project2].[IdAlmacen] AS [IdAlmacen], 
                    (SELECT 
                        SUM( CAST( [Filter3].[Cantidad] AS int)) AS [A1]
                        FROM   (SELECT [Extent16].[IdRequerimientoAlmacen] AS [IdRequerimientoAlmacen], [Extent16].[IdBien] AS [IdBien], [Extent16].[IdMaterial] AS [IdMaterial], [Extent16].[Cantidad] AS [Cantidad]
                            FROM  [Adm].[RequerimientoAlmacenComponente] AS [Extent16]
                            INNER JOIN [Adm].[Bien] AS [Extent17] ON [Extent16].[IdBien] = [Extent17].[Id]
                            WHERE ''TIPB0004'' = [Extent17].[IdTipoBien] ) AS [Filter3]
                        LEFT OUTER JOIN [Adm].[RequerimientoAlmacen] AS [Extent18] ON [Filter3].[IdRequerimientoAlmacen] = [Extent18].[Id]
                        WHERE ( NOT ((''ESRA0002'' = [Extent18].[IdEstado]) AND ([Extent18].[IdEstado] IS NOT NULL))) AND ( NOT ((''ESRA0005'' = [Extent18].[IdEstado]) AND ([Extent18].[IdEstado] IS NOT NULL))) AND ([Filter3].[IdBien] = [Project2].[IdBien]) AND ([Filter3].[IdMaterial] = [Project2].[IdMaterial]) AND (([Extent18].[IdAlmacenOrigen] = [Project2].[IdAlmacen]) OR (([Extent18].[IdAlmacenOrigen] IS NULL) AND ([Project2].[IdAlmacen] IS NULL)))) AS [C2]
                    FROM ( SELECT 
                        [GroupBy1].[A1] AS [C1], 
                        [GroupBy1].[K1] AS [IdBien], 
                        [GroupBy1].[K2] AS [IdMaterial], 
                        [GroupBy1].[K4] AS [IdAlmacen]
                        FROM ( SELECT 
                            [Project1].[IdBien] AS [K1], 
                            [Project1].[IdMaterial] AS [K2], 
                            [Project1].[Tipo] AS [K3], 
                            [Project1].[IdAlmacen] AS [K4], 
                            [Project1].[Nombre] AS [K5], 
                            [Project1].[Denominacion] AS [K6], 
                            [Project1].[IdTipoBien] AS [K7], 
                            [Project1].[Valor] AS [K8], 
                            [Project1].[C1] AS [K9], 
                            [Project1].[C2] AS [K10], 
                            [Project1].[C3] AS [K11], 
                            [Project1].[C4] AS [K12], 
                            [Project1].[IdSociedad] AS [K13], 
                            SUM( CAST( [Project1].[Cantidad] AS int)) AS [A1]
                            FROM ( SELECT 
                                [Filter1].[IdBien] AS [IdBien], 
                                [Filter1].[IdMaterial1] AS [IdMaterial], 
                                [Filter1].[Cantidad1] AS [Cantidad], 
                                [Filter1].[Tipo] AS [Tipo], 
                                [Extent7].[IdAlmacen] AS [IdAlmacen], 
                                [Extent8].[Nombre] AS [Nombre], 
                                [Extent9].[Denominacion] AS [Denominacion], 
                                [Extent12].[IdTipoBien] AS [IdTipoBien], 
                                [Extent15].[Valor] AS [Valor], 
                                CASE WHEN ( CAST( [Filter1].[Codigo1] AS nvarchar(max)) IS NULL) THEN N'''' ELSE  CAST( [Filter1].[Codigo1] AS nvarchar(max)) END + N''- ('' + CASE WHEN ([Extent9].[Denominacion] IS NULL) THEN N'''' ELSE [Extent9].[Denominacion] END + N'')'' AS [C1], 
                                CASE WHEN ([Filter1].[CodigoUnico] IS NULL) THEN N'''' ELSE [Filter1].[CodigoUnico] END AS [C2], 
                                CASE WHEN ([Extent10].[Valor] IS NULL) THEN N'''' ELSE [Extent10].[Valor] END + N''/'' + CASE WHEN ([Extent11].[Valor] IS NULL) THEN N'''' ELSE [Extent11].[Valor] END AS [C3], 
                                CASE WHEN ([Extent12].[Tipo] IS NULL) THEN N'''' ELSE [Extent12].[Tipo] END + N'' ('' + CASE WHEN ([Extent13].[Valor] IS NULL) THEN N'''' ELSE [Extent13].[Valor] END + N''/'' + CASE WHEN ([Extent14].[Valor] IS NULL) THEN N'''' ELSE [Extent14].[Valor] END + N'')'' AS [C4], 
                                [Filter1].[IdSociedad1] AS [IdSociedad]
                                FROM            (SELECT [Extent1].[IdBien] AS [IdBien], [Extent1].[IdMaterial] AS [IdMaterial1], [Extent1].[IdUbicacionAlmacen] AS [IdUbicacionAlmacen], [Extent1].[Cantidad] AS [Cantidad1], [Extent2].[Tipo] AS [Tipo], [Extent4].[Codigo] AS [Codigo1], [Extent5].[IdSociedad] AS [IdSociedad1], [Extent5].[IdPropiedad] AS [IdPropiedad1], [Extent5].[CodigoUnico] AS [CodigoUnico]
                                    FROM     [Inv].[BienSerieComponenteAlmacen] AS [Extent1]
                                    INNER JOIN [Adm].[Bien] AS [Extent2] ON [Extent1].[IdBien] = [Extent2].[Id]
                                    LEFT OUTER JOIN [Adm].[ContratoBienMaterial] AS [Extent3] ON [Extent1].[IdContratoBienMaterial] = [Extent3].[Id]
                                    INNER JOIN [Int].[Material] AS [Extent4] ON [Extent1].[IdMaterial] = [Extent4].[Id]
                                    INNER JOIN [Int].[MaterialAura] AS [Extent5] ON [Extent4].[Id] = [Extent5].[IdMaterial]
                                    WHERE ([Extent1].[IdBienSerieComponente] IS NULL) AND ([Extent5].[CodigoUnico] IS NOT NULL) ) AS [Filter1]
                                LEFT OUTER JOIN [Inv].[UbicacionAlmacen] AS [Extent6] ON [Filter1].[IdUbicacionAlmacen] = [Extent6].[Id]
                                LEFT OUTER JOIN [Inv].[UbicacionAlmacen] AS [Extent7] ON [Filter1].[IdUbicacionAlmacen] = [Extent7].[Id]
                                LEFT OUTER JOIN [Inv].[Almacen] AS [Extent8] ON [Extent7].[IdAlmacen] = [Extent8].[Id]
                                LEFT OUTER JOIN [Int].[Material] AS [Extent9] ON [Filter1].[IdMaterial1] = [Extent9].[Id]
                                LEFT OUTER JOIN [Adm].[ParametroValor] AS [Extent10] ON [Filter1].[IdSociedad1] = [Extent10].[Id]
                                LEFT OUTER JOIN [Adm].[ParametroValor] AS [Extent11] ON [Filter1].[IdPropiedad1] = [Extent11].[Id]
                                LEFT OUTER JOIN [Adm].[Bien] AS [Extent12] ON [Filter1].[IdBien] = [Extent12].[Id]
                                LEFT OUTER JOIN [Adm].[ParametroValor] AS [Extent13] ON [Extent12].[IdAgrupador] = [Extent13].[Id]
                                LEFT OUTER JOIN [Adm].[ParametroValor] AS [Extent14] ON [Extent12].[IdFamilia] = [Extent14].[Id]
                                LEFT OUTER JOIN [Adm].[ParametroValor] AS [Extent15] ON [Extent12].[IdTipoBien] = [Extent15].[Id]
                                WHERE ([Filter1].[IdBien] = @p__linq__0) AND (@p__linq__1 IS NULL OR [Filter1].[IdMaterial1] = @p__linq__2) AND ((@p__linq__3 IS NULL) OR ([Extent6].[IdAlmacen] = @p__linq__4) OR (([Extent6].[IdAlmacen] IS NULL) AND (@p__linq__4 IS NULL)))
                            )  AS [Project1]
                            GROUP BY [Project1].[IdBien], [Project1].[IdMaterial], [Project1].[Tipo], [Project1].[IdAlmacen], [Project1].[Nombre], [Project1].[Denominacion], [Project1].[IdTipoBien], [Project1].[Valor], [Project1].[C1], [Project1].[C2], [Project1].[C3], [Project1].[C4], [Project1].[IdSociedad]
                        )  AS [GroupBy1]
                    )  AS [Project2]
                )  AS [Project3]
            )  AS [Project5]
            WHERE ([Project5].[C1] - [Project5].[C2]) > 0
        )  AS [GroupBy3]',N'@p__linq__0 uniqueidentifier,@p__linq__1 uniqueidentifier,@p__linq__2 uniqueidentifier,@p__linq__3 uniqueidentifier,@p__linq__4 uniqueidentifier',@p__linq__0='D5B0866D-F2E0-4279-8D87-D4F8AE9ADA31',@p__linq__1=NULL,@p__linq__2=NULL,@p__linq__3=NULL,@p__linq__4=NULL

    But when I use sp.. I see that only the sp is executed...

    exec sp_executesql N'[Adm].[BuscaBienSerieMaterialBienPaginado] @IdMaterial, @IdAlmacen, @CodigoUnico, @IdBien, @IdPropiedad, @IdMaterialAsociado, @evaluarFiltroCar, @valores, @num, @nroPagina, @tamanioPagina',N'@IdMaterial uniqueidentifier,@IdAlmacen uniqueidentifier,@CodigoUnico char(18),@IdBien uniqueidentifier,@IdPropiedad char(8),@IdMaterialAsociado uniqueidentifier,@evaluarFiltroCar bit,@valores varchar(8000),@num varchar(1),@nroPagina int,@tamanioPagina int',@IdMaterial=NULL,@IdAlmacen=NULL,@CodigoUnico=NULL,@IdBien='D5B0866D-F2E0-4279-8D87-D4F8AE9ADA31',@IdPropiedad=NULL,@IdMaterialAsociado=NULL,@evaluarFiltroCar=0,@valores='',@num='0',@nroPagina=1,@tamanioPagina=10

    So, in this case. I think that procedures use less bandwith because less info is moved around the network..

    In this case procedures are better since above statements are moved from the web server and the database server..

    Monday, June 17, 2019 3:24 PM

Answers

All replies

  • User-821857111 posted

    I think that procedures use less bandwith because less info is moved around the network..
    Correct. ORMs are a trade off between productivity at the relatively tiny expense of a bit of performance. However, in most cases, the expense will be insignificant and is unlikely to be noticed. If it is, you can either write a stored procedure instead and call that using EF, or not use EF at all, and drop down to plain ADO.NET data readers. 

    Monday, June 17, 2019 3:50 PM
  • User1120430333 posted

    So, in this case. I think that procedures use less bandwith because less info is moved around the network..
    In this case procedures are better since above statements are moved from the web server and the database server..

    A human can make a bad stored procedure that executes poorly. The issues comes no matter how the T-SQL is executed or formed  or pused to the database server, becuase the data has to accessed via the C# or VB code using a container of a  datatable  with the datatable  using boxing and unboxing or custom object in a collection, and they slow things down. 

    Monday, June 17, 2019 4:04 PM
  • User1520731567 posted

    Hi neoaguil17,

    Depending on different your needs.

    On the other hand, Linq makes it easier for developers to improve efficiency and maintain code.

    If all you're doing is simple INSERT, UPDATE, and DELETE statements,

    LINQ is the way to go (in my opinion) and all the optimization is done for you,

    for more complex work I would say to stick with stored procedures.

    Best Regards.

    Yuki Tao

    Tuesday, June 18, 2019 8:22 AM
  • User-1350042179 posted

    This article give better details:

    Network traffic: sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex. However, LINQ's abstraction allows Microsoft to improve this over time.

    https://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 18, 2019 2:19 PM
  • User-821857111 posted

    This article give better details:

    Network traffic: sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex. However, LINQ's abstraction allows Microsoft to improve this over time.

    https://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures

    While you seem to be preoccupied with the size of a SQL query generated by an ORM (your example comes in about 8kb), it's worth considering that the data coming back "over the wire" is likely to be a lot larger. Your general concerns are unfounded, in my opinion.

    Tuesday, June 18, 2019 3:27 PM