SQL Server:将多联接查询分解为较小的查询并创建一个函数

Question

我有一个复杂的查询，其中包含多个连接，它在我的应用程序中多次运行。我想通过将这个查询分解为函数内部的较小部分来将此查询编写为函数。作为一个新手，我对SQL Server的了解有限。

以下是查询：

SELECT 
     ts.lable as label,
     ts.percentage as rate
FROM 
     TaxSet ts 
JOIN 
     UserInfo u ON u.userID = ? 
                AND u.countryID = ts.countryId 
                AND (ts.stateId IS NULL OR ts.stateId = 0 OR LEN(ts.stateId) < 1)
JOIN
     Users us ON u.userID = us.id
JOIN
     Users p ON us.parentID = p.id
             AND ts.ispID = p.id
JOIN
     ProductType pt ON ts.productTypeID = pt.id
WHERE 
     startDate <= getutcdate() 
     AND getutcdate() <= endDate 
     AND pt.identifier = ?
     AND ts.id NOT IN (SELECT eu.ispTaxSettingId 
                       FROM ExemptUsers eu 
                       WHERE eu.ExemptUserId = ?)

现在，我如何通过将这个查询分解成更小的查询来编写函数。

提前谢谢。

Answer 1

请问你为什么要把它分成函数？我重新格式化了您的代码，并暂时将其放入存储过程中。我的想法是，您希望传递标识符和用户ID，它们是查询的参数。

我已经修改了查询并删除了 Not In 语句。这已被 LEFT JOIN 替换为 u.id = eu 上的豁免用户。豁免用户 ID，然后是对 WHERE 子句的补充，以确保 eu。ExemptUser ID 为 NULL。这基本上是一种更清晰的说法"如果表中存在 userID，则 ExemptUsers 不会为该用户返回结果"。

此外，我已经删除了对用户 p 的联接，因为我看不到它以任何方式被使用，除非您想确保用户有父级？

 CREATE PROCEDURE wsp_StoredProcName 
(@UserID int,
@Identifier int)
AS
BEGIN

    SELECT 
        ts.lable as label,
        ts.percentage as rate
    FROM 
         TaxSet ts 
            INNER JOIN UserInfo u ON u.userID = ts.UserID 
                AND u.countryID = ts.countryId  
            INNER JOIN Users us on u.userID = us.id
            INNER JOIN ProductType pt on ts.productTypeID = pt.id
            LEFT JOIN ExemptUsers eu on u.id = eu.ExemptUserID
    WHERE 
         (
            ts.UserID = @UserID
            and pt.identifier = @Identifier
            and startDate <= getutcdate() 
            and getutcdate() <= endDate 
            AND eu.ExemptUserID IS NULL
            and 
            (
            ts.stateId is null or ts.stateId = 0 or len(ts.stateId) < 1
            )
        )
END

Answer 2

毕竟，

您可以从 TaxSet 中选择必须满足某些条件的内容：日期范围、状态、与特定用户及其父级的关系、与特定产品类型的关系以及特定豁免用户的不存在。因此，在整个查询中使用 EXISTS 和 NOT EXIST，以便让读者和 dbms 清楚地了解您想要实现的目标。查询越直接，优化程序通常越容易处理它。

select 
  ts.lable as label,
  ts.percentage as rate
from taxset ts 
where getutcdate() between ts.startdate and ts.enddate 
and (stateid is null or stateid between 0 and 9)
and exists 
(
  select *
  from users u
  join userinfo ui on ui.userid = u.id
  where u.id = ?
  and ui.countryid = ts.countryid
  and u.parentid = ts.ispid
)
and exists
(
  select * 
  from producttype pt 
  where pt.identifier = ?
  and pt.id = ts.producttypeid
)
and not exists
(
  select *
  from exemptusers eu 
  where eu.exemptuserid = ?
  and eu.isptaxsettingid = ts.id
);

正如其他人所提到的：当将查询拆分为较小的部分并分别执行这些部分时，它通常会变慢而不是更快。这是因为 dbms 是在内部以最有效的方式做到这一点的。当然，有时 dbms 的优化器无法找到一个好的执行计划。您可能需要查看执行计划并检查您是否认为该计划合适。