使用子选择计数挂起的预订

Question

这个查询可以改进吗？有办法消除重复的函数调用吗？

-- a Special Group has many Items which have many Bookings
create function f_BookingsForSpecialGroup(@specialGroupId varchar(99))
returns table as
    return select * from v_Booking where ITEM_CODE in
        (select ITEM_CODE from v_Item i where i.SPECIAL_GROUP_ID = @specialGroupId);
go


create view v_SpecialGroup as
select (NON_BAD_BOOKINGS - PAID_BOOKINGS) as PENDING_BOOKINGS, * from
(
select
    (select count(*) from f_BookingsForSpecialGroup(g.SPECIAL_GROUP_ID) where IS_BAD=0) as NON_BAD_BOOKINGS,
    (select count(*) from f_BookingsForSpecialGroup(g.SPECIAL_GROUP_ID) where IS_PAID=1) as PAID_BOOKINGS,
    *
from SPECIAL_GROUP g
) g
go

视图v_SpecialGroup从不由应用程序直接查询；它用于构建其他视图，这些视图根据需要选择单个列。(您可以将v_SpecialGroup看作一个“基本视图”，它的存在完全是为了增强SPECIAL_GROUP表。我分析了这个策略，如果你不选择更贵的栏目，你就不用付钱，但我当然错了。)

Answer 1

在我看来，视图将返回SPECIAL_GROUP表中的所有列，以及一个额外的列计数预订，这些预订既不是is_bad也不是is_paid。

如果是这样，您可以使用一个公共表表达式*来稍微简化逻辑：

create view v_SpecialGroup as

with bookings_by_group as (
    select  i.SPECIAL_GROUP_ID,
            count(case when is_bad = 0 then 1 end) as NON_BAD_BOOKINGS,
            count(case when is_paid = 1 then 1 end) as PAID_BOOKINGS
    from    v_booking b join v_item i on b.ITEM_CODE = i.ITEM_CODE
    group by i.SPECIAL_GROUP_ID)
select  NON_BAD_BOOKINGS - PAID_BOOKINGS as PENDING_BOOKINGS,
        g.*
from    bookings_by_group b join SPECIAL_GROUP g on b.SPECIAL_GROUP_ID = g.SPECIAL_GROUP_ID

ps。还可能进一步压缩逻辑：

count(case when is_bad = 0 and is_paid != 1 then 1 end) as PENDING_BOOKINGS

这将取决于您是否有is_bad和is_paid的预订。

* Server必须是>= 2008R2

Answer 2

是的，有一种方法可以消除重复的函数调用，但是关于它是否会提高性能，您需要对其进行基准测试。该方法将结果数据写入临时表，这意味着它在填充函数时只调用一次，但反过来意味着它必须将该表写入该会话的tempdb。下面是这样写它的方式：

if object_id('tempdb..#SpecialBookings') is not null
    drop table tempdb..#SpecialBookings;

select *
into #SpecialBookings
from SPECIAL_GROUP as grp
    cross apply f_BookingsForSpecialGroup(grp.SPECIAL_GROUP_ID) as bookings
where (bookings.IS_BAD = 0 or bookings.IS_PAID = 1);

select (grpCounted.NON_BAD_BOOKINGS - grpCounted.PAID_BOOKINGS) as PENDING_BOOKINGS, * 
from 
(
    select
        (select count(*) from #SpecialBookings where IS_BAD=0) as NON_BAD_BOOKINGS,
        (select count(*) from #SpecialBookings where IS_PAID=1) as PAID_BOOKINGS,
        *
    from #SpecialBookings
) as grpCounted;

if object_id('tempdb..#SpecialBookings') is not null
    drop table tempdb..#SpecialBookings;

select *

您真的需要SPECIAL_GROUP表和f_BookingsForSpecialGroup中的所有字段吗？如果您确实需要它们，那么就足够公平，但否则，您不应使用select *。

别名

如您所见，我将您的表别名重命名了一些，以使您的代码更易于阅读。尽可能避免使用单字母或其他无意义的别名，因为它们会使代码变得不那么清晰。

Answer 3

一种方法是有一个函数，返回一个由两个INTs组成的表，对应于每个COUNTs：

BadCount INT, PaidCount INT

这个函数将根据一个SPECIAL_GROUP_ID计算两个计数，因此它应该如下所示：

CREATE FUNCTION dbo.ufnGetContactInformation(@SPECIAL_GROUP_ID INT)
RETURNS @CountInfo TABLE 
(

    ContactID int PRIMARY KEY NOT NULL, 
    FirstName nvarchar(50) NULL, 
)
-- function body comes here

但是，请记住，对于从SPECIAL_GROUP返回的每一行，都将调用您的函数(如果我没记错的话，估计/实际计划在分析器中明显可见，估计/实际计划不会显示函数调用)，因此性能可能会受到影响。

此外，应该避免*，因为它可能导致性能问题(选择所有列可能会抑制索引的使用)，也可能导致意外的结果(更改表结构而不进行过程重新编译，意味着*实际上不会为您带来所有列)。

如果可能的话，请提供来自f_BookingsForSpecialGroup的代码--也许可以重写为更多的集基。