首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >使用实体框架的Linq查询效率

问使用实体框架的Linq查询效率
EN

Stack Overflow用户
提问于 2018-10-01 19:45:13
回答 1查看 82关注 0票数 2

我必须重写一个巨大的SQL查询,该查询使用实体框架和Linq从大约10个表中获取数据。简而言之,查询从主表中获取产品列表,然后从这些产品的其他表中获取其他信息,如下载计数、销售计数、作者等。

我决定把它分解成几个部分,这样其他人就很容易理解和编辑了。

首先,我得到了产品清单。然后,我得到了其他列的列表数据,并在最后一步将所有内容合并在一起。

代码语言:javascript
复制
 var mainTableQuery = from p in context.mains
                      select new
                      {
                         p.ProductID,
                         p.title,
                          p.department_id,
                         p.TotalInStock                                        
                       };
 //Getting the top author by priority for every product ID
 var authorLinkQuery= from authorLink in context.AuthorLinks
                      group authorLink by authorLink.ProductID
                         into groups
                      select groups.OrderBy(p=>p.Priority).FirstOrDefault();

 // Link productIDs with primary author names
 var authorsFinalQuery = from author in context.Authors
                         join authorLink in authorLinkQuery
                         on author.AuthorID equals authorLink.AuthorID
                         select new
                         {
                             authorLink.ProductID,
                             Name = (String.IsNullOrEmpty(author.Company) ? author.FirstName + " " + author.LastName : author.Company)
                         };
 var finalQuery = from main in mainTableQuery
                             join author in authorsFinalQuery
                             on main.ProductID equals author.ProductID                                 
                             select new
                             {
                                 main,
                                 author.Name
                             };

这个最后的查询得到了正确的数据,但需要大约30次。最初的SQL server查询只使用6s。问题是由实体框架生成的SQL服务器查询效率很低。有什么方法可以优化这个吗?

编辑:我试图替换的完整存储过程是:

代码语言:javascript
复制
CREATE PROCEDURE [dbo].[usp_AllProducts] (@ProgramAreaID int,
@CustomDateStart datetime,
@CustomDateEnd datetime,
@ShowNLA nvarchar(50))
AS
BEGIN 
  SET NOCOUNT ON;
  IF @ProgramAreaID = 1000
  BEGIN
    SELECT TOP (100) PERCENT
      BaseInventory.ProductID,
      BaseInventory.ReferenceNumber,
      BaseInventory.Title,
      BaseInventory.StoredBy,
      BaseInventory.TotalInStock,
      BaseInventory.StockAtEDC,
      BaseInventory.Cost,
      BaseInventory.RetailPrice,
      BaseInventory.PubDate,
      BaseInventory.DepartmentCode,
      vwAuthor.PrimaryAuthor,
      ISNULL(Sales30Days.QuantitySold, 0) AS Last30Days,
      ISNULL(SalesPastYear.QuantitySold, 0) AS Last365Days,
      ISNULL(CustomRangeSales.QuantitySold, 0) AS CustomRange,
      ISNULL(SalesPastYear.ZeroStock, '12/31/9999') AS ZeroStock,
      ISNULL(SalesPastYear.ZeroStockFullDate, CONVERT(datetime, '12/31/9999', 102)) AS ZeroStockFullDate,
      BaseInventory.FullPubDate,
      BaseInventory.ProgramArea,
      BaseInventory.Spaces,
      BaseInventory.DigitalFileExists,
      BaseInventory.DownloadCount,
      BaseInventory.DownloadCountCustom,
      ISNULL(SalesPastYear.SpacesSold, 0.000) AS SpacesSold,
      BaseInventory.DateReceived,
      BaseInventory.ProductWeight,
      BaseInventory.NumberOfPages,
      BaseInventory.DigitalFileCount,
      BaseInventory.ActiveDigitalFileCount
    FROM (SELECT TOP (100) PERCENT
      m.ProductWeight,
      m.ProductID,
      m.reference_number AS ReferenceNumber,
      m.title AS Title,
      MAX(sb.StoredBy) AS StoredBy,
      m.TotalInStock,
      m.edc_copies AS StockAtEDC,
      m.pub_cost AS Cost,
      m.RetailPrice,
      m.pub_date AS PubDate,
      d.department_code AS DepartmentCode,
      m.department_id AS DepartmentId,
      dg.department_group AS ProgramArea,
      m.FullPubDate,
      ISNULL(Boxes.Spaces, CASE
        WHEN sb.StoredBy = 'WWW' THEN 0
        ELSE 1
      END) AS Spaces,
      CASE
        WHEN ISNULL(dm.FileID, 0) = 0 THEN 'N/A'
        ELSE 'Active'
      END AS DigitalFileExists,
      ISNULL(DLs.DownloadCount, 0) AS DownloadCount,
      ISNULL(DLsCustom.DownloadCountCustom, 0) AS DownloadCountCustom,
      MAX(history.DateReceived) AS DateReceived,
      ISNULL(m.number_of_pages, 'N/A') AS NumberOfPages,
      (SELECT
        COUNT(*)
      FROM tblDigitalMedia tD
      WHERE tD.ProductID = m.ProductID)
      AS DigitalFileCount,
      (SELECT
        COUNT(*)
      FROM tblDigitalMedia tD
      WHERE tD.ProductID = m.ProductID
      AND tD.Active = 1)
      AS ActiveDigitalFileCount
    FROM dbo.main m
    INNER JOIN dbo.departments d
      ON m.department_id = d.department_id
    INNER JOIN dbo.department_groups dg
      ON d.department_group_id = dg.department_group_id

    /* InventoryTemp should be called InventoryByLocation */
    LEFT OUTER JOIN (SELECT
      m1.ProductID,
      SUM(ISNULL(it.NumberOfBoxes, 0)) + 1 AS Spaces
    FROM dbo.main AS m1
    INNER JOIN dbo.InventoryTemp AS it
      ON m1.ProductID = it.ProductID
    GROUP BY m1.ProductID) AS Boxes
      ON m.ProductID = Boxes.ProductID

    /* JobsTemp should be called JobHistory */
    LEFT OUTER JOIN dbo.JobsTemp history
      ON m.ProductID = history.ProductID

    LEFT OUTER JOIN (
    /* If dates are null then fetch for previous year. */
    SELECT TOP (100) PERCENT
      dm1.ProductID,
      COUNT(dl1.TimeStamp) AS DownloadCountCustom
    FROM dbo.tblDigitalMedia AS dm1
    LEFT OUTER JOIN dbo.tblDigitalMediaDownloads dl1
      ON dm1.FileID = dl1.FileID
    WHERE ((@CustomDateStart IS NOT NULL)
    AND (dl1.TimeStamp >= @CustomDateStart)
    AND (dl1.TimeStamp <= @CustomDateEnd))
    OR ((@CustomDateStart IS NULL)
    AND (dl1.TimeStamp BETWEEN DATEADD(D, -365, GETDATE()) AND GETDATE()))
    GROUP BY dm1.ProductID) AS DLsCustom
      ON m.ProductID = DLsCustom.ProductID
    LEFT OUTER JOIN (
    /* If dates are null then fetch for previous year. */
    SELECT TOP (100) PERCENT
      dm2.ProductID,
      COUNT(dl2.TimeStamp) AS DownloadCount
    FROM dbo.tblDigitalMedia AS dm2
    LEFT OUTER JOIN dbo.tblDigitalMediaDownloads dl2
      ON dm2.FileID = dl2.FileID
    WHERE dl2.TimeStamp BETWEEN DATEADD(D, -365, GETDATE()) AND GETDATE()
    GROUP BY dm2.ProductID) AS DLs
      ON m.ProductID = DLs.ProductID

    LEFT OUTER JOIN dbo.tblDigitalMedia AS dm
      ON m.ProductID = dm.ProductID

    /* InventoryTemp should be called InventoryByLocation */
    LEFT OUTER JOIN dbo.InventoryTemp it2
      ON m.ProductID = it2.ProductID

    INNER JOIN dbo.StoredBy sb
      ON m.StoredByID = sb.StoredByID
    WHERE (m.edc_isle LIKE N'Aisle' + N'%'
    OR m.edc_isle = N'Digital Media'
    OR m.edc_isle = N'Duplicator')
    AND NOT (d.department_id BETWEEN 995 AND 999)

    GROUP BY m.ProductWeight,
             m.ProductID,
             m.reference_number,
             m.title,
             m.TotalInStock,
             dg.department_group_id,
             m.pub_date,
             m.department_id,
             d.department_code,
             m.pub_cost,
             m.RetailPrice,
             m.FullPubDate,
             m.edc_isle,
             sb.StoredBy,
             dg.department_group,
             CASE
               WHEN ISNULL(dm.FileID, 0) = 0 THEN 'N/A'
               ELSE 'Active'
             END,
             ISNULL(DLs.DownloadCount, 0),
             ISNULL(DLsCustom.DownloadCountCustom, 0),
             ISNULL(Boxes.Spaces, CASE
               WHEN sb.StoredBy = 'WWW' THEN 0
               ELSE 1
             END),
             m.edc_copies,
             ISNULL(m.number_of_pages, 'N/A')
    HAVING (NOT (MAX(sb.StoredBy) = @ShowNLA))
    ORDER BY m.FullPubDate) AS BaseInventory /* ***** Table A ***** */

    LEFT OUTER JOIN (SELECT
      dbo.shopping_cart.ProductID,
      SUM(dbo.shopping_cart.quantity) AS QuantitySold
    FROM dbo.orders
    INNER JOIN dbo.shopping_cart
      ON dbo.orders.receiving_number = dbo.shopping_cart.receiving_number
    WHERE dbo.orders.OrderTimeStamp >= DATEADD(DAY, -30, GETDATE())
    AND dbo.orders.OrderTimeStamp < GETDATE()
    AND dbo.shopping_cart.ProductID IS NOT NULL
    GROUP BY dbo.shopping_cart.ProductID) AS Sales30Days  /* ***** Table B ***** */
      ON BaseInventory.ProductID = Sales30Days.ProductID
    LEFT OUTER JOIN (SELECT
      dbo.shopping_cart.ProductID,
      SUM(dbo.shopping_cart.quantity) AS QuantitySold
    FROM dbo.orders
    INNER JOIN dbo.shopping_cart
      ON dbo.orders.receiving_number = dbo.shopping_cart.receiving_number
    WHERE @CustomDateStart IS NOT NULL
    AND dbo.orders.OrderTimeStamp >= @CustomDateStart
    AND dbo.orders.OrderTimeStamp <= @CustomDateEnd
    AND dbo.shopping_cart.ProductID IS NOT NULL
    GROUP BY dbo.shopping_cart.ProductID) AS CustomRangeSales /* ***** CustomRangeSales ***** */
      ON BaseInventory.ProductID = CustomRangeSales.ProductID

    LEFT OUTER JOIN (SELECT TOP (100) PERCENT
      cart3.ProductID,
      SUM(cart3.quantity) AS QuantitySold,
      m3.TotalInStock,
      CASE
        WHEN
          CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)) > ((9999 - YEAR(GETDATE())) * 365) THEN NULL
        ELSE CONVERT(varchar, DATEADD(DAY, CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)), GETDATE()), 101)
      END AS ZeroStock,
      CASE
        WHEN
          CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)) > ((9999 - YEAR(GETDATE())) * 365) THEN NULL
        ELSE DATEADD(DAY, CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)), GETDATE())
      END AS ZeroStockFullDate,
      CONVERT(decimal(18, 3), SUM(cart3.quantity) / ISNULL(CONVERT(decimal(18, 2), AvgInventory.NumberPerBox), SUM(cart3.quantity) + m3.TotalInStock)) AS SpacesSold,
      AvgInventory.NumberPerBox
    FROM dbo.orders AS o1
    INNER JOIN dbo.shopping_cart AS cart3
      ON o1.receiving_number = cart3.receiving_number
    INNER JOIN dbo.main AS m3
      ON cart3.ProductID = m3.ProductID
    LEFT OUTER JOIN (SELECT TOP (100) PERCENT
      ProductID,
      AVG(NumberPerBox) AS NumberPerBox
    FROM dbo.InventoryTemp
    GROUP BY ProductID
    ORDER BY ProductID) AS AvgInventory
      ON m3.ProductID = AvgInventory.ProductID
    WHERE (o1.OrderTimeStamp BETWEEN DATEADD(DAY, -365, GETDATE()) AND GETDATE() + 1)
    GROUP BY cart3.ProductID,
             m3.TotalInStock,
             AvgInventory.NumberPerBox
    HAVING (NOT (cart3.ProductID IS NULL))) AS SalesPastYear /* ***** Table C ***** */
      ON BaseInventory.ProductID = SalesPastYear.ProductID

    LEFT OUTER JOIN (SELECT
      ProductID,
      CASE
        WHEN ((Auth.Company = '') OR
          (Auth.Company IS NULL)) THEN LTRIM(ISNULL(Auth.FirstName, '') + ' ' + ISNULL(Auth.LastName, ''))
        ELSE Auth.Company
      END AS PrimaryAuthor
    FROM dbo.Author AS Auth
    INNER JOIN (SELECT
      AUTHLINK.AuthorID,
      AUTHLINK.Priority,
      AUTHLINK.ProductID
    FROM (SELECT TOP (100) PERCENT
      ProductID,
      MIN(ISNULL(Priority, 0)) AS Priority
    FROM dbo.AuthorLink
    GROUP BY ProductID
    ORDER BY ProductID) AS AuthMinPrty
    INNER JOIN dbo.AuthorLink AS AUTHLINK
      ON (AuthMinPrty.ProductID = AUTHLINK.ProductID)
      AND (ISNULL(AuthMinPrty.Priority, 0) = ISNULL(AUTHLINK.Priority, 0))) AS PrimaryAuthor
      ON Auth.AuthorID = PrimaryAuthor.AuthorID) AS vwAuthor /* Table Author */
      ON BaseInventory.ProductID = vwAuthor.ProductID

    ORDER BY BaseInventory.ReferenceNumber
  END
  ELSE
  BEGIN
    SELECT TOP (100) PERCENT
      BaseInventory.ProductID,
      BaseInventory.ReferenceNumber,
      BaseInventory.Title,
      BaseInventory.StoredBy,
      BaseInventory.TotalInStock,
      BaseInventory.StockAtEDC,
      BaseInventory.Cost,
      BaseInventory.RetailPrice,
      BaseInventory.PubDate,
      BaseInventory.DepartmentCode,
      vwAuthor.PrimaryAuthor,
      ISNULL(Sales30Days.QuantitySold, 0) AS Last30Days,
      ISNULL(SalesPastYear.QuantitySold, 0) AS Last365Days,
      ISNULL(CustomRangeSales.QuantitySold, 0) AS CustomRange,
      ISNULL(SalesPastYear.ZeroStock, '12/31/9999') AS ZeroStock,
      ISNULL(SalesPastYear.ZeroStockFullDate, CONVERT(datetime, '12/31/9999', 102)) AS ZeroStockFullDate,
      BaseInventory.FullPubDate,
      BaseInventory.ProgramArea,
      BaseInventory.Spaces,
      BaseInventory.DigitalFileExists,
      BaseInventory.DownloadCount,
      BaseInventory.DownloadCountCustom,
      ISNULL(SalesPastYear.SpacesSold, 0.000) AS SpacesSold,
      BaseInventory.DateReceived,
      BaseInventory.ProductWeight,
      BaseInventory.NumberOfPages,
      BaseInventory.DigitalFileCount,
      BaseInventory.ActiveDigitalFileCount
    FROM (SELECT TOP (100) PERCENT
      m.ProductWeight,
      m.ProductID,
      m.reference_number AS ReferenceNumber,
      m.title AS Title,
      MAX(sb.StoredBy) AS StoredBy,
      m.TotalInStock,
      m.edc_copies AS StockAtEDC,
      m.pub_cost AS Cost,
      m.RetailPrice,
      m.pub_date AS PubDate,
      d.department_code AS DepartmentCode,
      m.department_id AS DepartmentId,
      dg.department_group AS ProgramArea,
      m.FullPubDate,
      ISNULL(Boxes.Spaces, CASE
        WHEN sb.StoredBy = 'WWW' THEN 0
        ELSE 1
      END) AS Spaces,
      CASE
        WHEN ISNULL(dm.FileID, 0) = 0 THEN 'N/A'
        ELSE 'Active'
      END AS DigitalFileExists,
      ISNULL(DLs.DownloadCount, 0) AS DownloadCount,
      ISNULL(DLsCustom.DownloadCountCustom, 0) AS DownloadCountCustom,
      MAX(history.DateReceived) AS DateReceived,
      ISNULL(m.number_of_pages, 'N/A') AS NumberOfPages,
      (SELECT
        COUNT(*)
      FROM tblDigitalMedia tD
      WHERE tD.ProductID = m.ProductID)
      AS DigitalFileCount,
      (SELECT
        COUNT(*)
      FROM tblDigitalMedia tD
      WHERE tD.ProductID = m.ProductID
      AND tD.Active = 1)
      AS ActiveDigitalFileCount
    FROM dbo.main m
    INNER JOIN dbo.departments d
      ON m.department_id = d.department_id
    INNER JOIN dbo.department_groups dg
      ON d.department_group_id = dg.department_group_id

    /* InventoryTemp should be called InventoryByLocation */
    LEFT OUTER JOIN (SELECT
      m1.ProductID,
      SUM(ISNULL(it.NumberOfBoxes, 0)) + 1 AS Spaces
    FROM dbo.main AS m1
    INNER JOIN dbo.InventoryTemp AS it
      ON m1.ProductID = it.ProductID
    GROUP BY m1.ProductID) AS Boxes
      ON m.ProductID = Boxes.ProductID

    /* JobsTemp should be called JobHistory */
    LEFT OUTER JOIN dbo.JobsTemp history
      ON m.ProductID = history.ProductID

    LEFT OUTER JOIN (
    /* If dates are null then fetch for previous year. */
    SELECT TOP (100) PERCENT
      dm1.ProductID,
      COUNT(dl1.TimeStamp) AS DownloadCountCustom
    FROM dbo.tblDigitalMedia AS dm1
    LEFT OUTER JOIN dbo.tblDigitalMediaDownloads dl1
      ON dm1.FileID = dl1.FileID
    WHERE ((@CustomDateStart IS NOT NULL)
    AND (dl1.TimeStamp >= @CustomDateStart)
    AND (dl1.TimeStamp <= @CustomDateEnd))
    OR ((@CustomDateStart IS NULL)
    AND (dl1.TimeStamp BETWEEN DATEADD(D, -365, GETDATE()) AND GETDATE()))
    GROUP BY dm1.ProductID) AS DLsCustom
      ON m.ProductID = DLsCustom.ProductID
    LEFT OUTER JOIN (
    /* If dates are null then fetch for previous year. */
    SELECT TOP (100) PERCENT
      dm2.ProductID,
      COUNT(dl2.TimeStamp) AS DownloadCount
    FROM dbo.tblDigitalMedia AS dm2
    LEFT OUTER JOIN dbo.tblDigitalMediaDownloads dl2
      ON dm2.FileID = dl2.FileID
    WHERE dl2.TimeStamp BETWEEN DATEADD(D, -365, GETDATE()) AND GETDATE()
    GROUP BY dm2.ProductID) AS DLs
      ON m.ProductID = DLs.ProductID

    LEFT OUTER JOIN dbo.tblDigitalMedia AS dm
      ON m.ProductID = dm.ProductID

    /* InventoryTemp should be called InventoryByLocation */
    LEFT OUTER JOIN dbo.InventoryTemp it2
      ON m.ProductID = it2.ProductID

    INNER JOIN dbo.StoredBy sb
      ON m.StoredByID = sb.StoredByID
    WHERE (m.edc_isle LIKE N'Aisle' + N'%'
    OR m.edc_isle = N'Digital Media'
    OR m.edc_isle = N'Duplicator')
    AND (dg.department_group_id = @ProgramAreaID)

    GROUP BY m.ProductWeight,
             m.ProductID,
             m.reference_number,
             m.title,
             m.TotalInStock,
             dg.department_group_id,
             m.pub_date,
             m.department_id,
             d.department_code,
             m.pub_cost,
             m.RetailPrice,
             m.FullPubDate,
             m.edc_isle,
             sb.StoredBy,
             dg.department_group,
             CASE
               WHEN ISNULL(dm.FileID, 0) = 0 THEN 'N/A'
               ELSE 'Active'
             END,
             ISNULL(DLs.DownloadCount, 0),
             ISNULL(DLsCustom.DownloadCountCustom, 0),
             ISNULL(Boxes.Spaces, CASE
               WHEN sb.StoredBy = 'WWW' THEN 0
               ELSE 1
             END),
             m.edc_copies,
             ISNULL(m.number_of_pages, 'N/A')
    HAVING (NOT (MAX(sb.StoredBy) = @ShowNLA))
    ORDER BY m.FullPubDate) AS BaseInventory /* ***** Table A ***** */

    LEFT OUTER JOIN (SELECT
      dbo.shopping_cart.ProductID,
      SUM(dbo.shopping_cart.quantity) AS QuantitySold
    FROM dbo.orders
    INNER JOIN dbo.shopping_cart
      ON dbo.orders.receiving_number = dbo.shopping_cart.receiving_number
    WHERE dbo.orders.OrderTimeStamp >= DATEADD(DAY, -30, GETDATE())
    AND dbo.orders.OrderTimeStamp < GETDATE()
    AND dbo.shopping_cart.ProductID IS NOT NULL
    GROUP BY dbo.shopping_cart.ProductID) AS Sales30Days  /* ***** Table B ***** */
      ON BaseInventory.ProductID = Sales30Days.ProductID
    LEFT OUTER JOIN (SELECT
      dbo.shopping_cart.ProductID,
      SUM(dbo.shopping_cart.quantity) AS QuantitySold
    FROM dbo.orders
    INNER JOIN dbo.shopping_cart
      ON dbo.orders.receiving_number = dbo.shopping_cart.receiving_number
    WHERE @CustomDateStart IS NOT NULL
    AND dbo.orders.OrderTimeStamp >= @CustomDateStart
    AND dbo.orders.OrderTimeStamp <= @CustomDateEnd
    AND dbo.shopping_cart.ProductID IS NOT NULL
    GROUP BY dbo.shopping_cart.ProductID) AS CustomRangeSales /* ***** CustomRangeSales ***** */
      ON BaseInventory.ProductID = CustomRangeSales.ProductID

    LEFT OUTER JOIN (SELECT TOP (100) PERCENT
      cart3.ProductID,
      SUM(cart3.quantity) AS QuantitySold,
      m3.TotalInStock,
      CASE
        WHEN
          CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)) > ((9999 - YEAR(GETDATE())) * 365) THEN NULL
        ELSE CONVERT(varchar, DATEADD(DAY, CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)), GETDATE()), 101)
      END AS ZeroStock,
      CASE
        WHEN
          CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)) > ((9999 - YEAR(GETDATE())) * 365) THEN NULL
        ELSE DATEADD(DAY, CONVERT(int, m3.TotalInStock / (CONVERT(decimal(18, 10), SUM(cart3.quantity)) / 365)), GETDATE())
      END AS ZeroStockFullDate,
      CONVERT(decimal(18, 3), SUM(cart3.quantity) / ISNULL(CONVERT(decimal(18, 2), AvgInventory.NumberPerBox), SUM(cart3.quantity) + m3.TotalInStock)) AS SpacesSold,
      AvgInventory.NumberPerBox
    FROM dbo.orders AS o1
    INNER JOIN dbo.shopping_cart AS cart3
      ON o1.receiving_number = cart3.receiving_number
    INNER JOIN dbo.main AS m3
      ON cart3.ProductID = m3.ProductID
    LEFT OUTER JOIN (SELECT TOP (100) PERCENT
      ProductID,
      AVG(NumberPerBox) AS NumberPerBox
    FROM dbo.InventoryTemp
    GROUP BY ProductID
    ORDER BY ProductID) AS AvgInventory
      ON m3.ProductID = AvgInventory.ProductID
    WHERE (o1.OrderTimeStamp BETWEEN DATEADD(DAY, -365, GETDATE()) AND GETDATE() + 1)
    GROUP BY cart3.ProductID,
             m3.TotalInStock,
             AvgInventory.NumberPerBox
    HAVING (NOT (cart3.ProductID IS NULL))) AS SalesPastYear /* ***** Table C ***** */
      ON BaseInventory.ProductID = SalesPastYear.ProductID

    LEFT OUTER JOIN (SELECT
      ProductID,
      CASE
        WHEN ((Auth.Company = '') OR
          (Auth.Company IS NULL)) THEN LTRIM(ISNULL(Auth.FirstName, '') + ' ' + ISNULL(Auth.LastName, ''))
        ELSE Auth.Company
      END AS PrimaryAuthor
    FROM dbo.Author AS Auth
    INNER JOIN (SELECT
      AUTHLINK.AuthorID,
      AUTHLINK.Priority,
      AUTHLINK.ProductID
    FROM (SELECT TOP (100) PERCENT
      ProductID,
      MIN(ISNULL(Priority, 0)) AS Priority
    FROM dbo.AuthorLink
    GROUP BY ProductID
    ORDER BY ProductID) AS AuthMinPrty
    INNER JOIN dbo.AuthorLink AS AUTHLINK
      ON (AuthMinPrty.ProductID = AUTHLINK.ProductID)
      AND (ISNULL(AuthMinPrty.Priority, 0) = ISNULL(AUTHLINK.Priority, 0))) AS PrimaryAuthor
      ON Auth.AuthorID = PrimaryAuthor.AuthorID) AS vwAuthor /* Table Author */
      ON BaseInventory.ProductID = vwAuthor.ProductID

    ORDER BY BaseInventory.ReferenceNumber
  END
END /* stored procedure */
GO
EN

回答 1

Stack Overflow用户

发布于 2018-10-01 20:59:20

忽略所有让你以其他方式去做的人,看看你是否能调整你拥有的东西。声称这是4个查询的人是不正确的。LINQ将组合成一个SQL语句。你需要做的是试着找出:

  • 生成的SQL是什么样的(尝试使用捕获它)
  • 效率低下的原因(同样,SQL Server事件探查器,或者粘贴到SSMS中并显示查询计划)

一旦你这样做了,它可能会变得更清楚你需要对你的LINQ做什么,以使它的执行。

为了简化一次需要查看的数量,您可以尝试一件事:孤立地执行每个“内部”查询,并查看它们中是否有一个本身看起来很糟糕。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/52597867

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档