查询计划优化器是否适用于连接/过滤表值函数? [英] Does query plan optimizer works well with joined/filtered table-valued functions?

问题描述

在 SQLSERVER 2005 中，我使用表值函数作为对大表中的子集数据执行任意聚合的便捷方法(传递日期范围或此类参数).

In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters).

我在较大的查询中使用这些作为连接计算的论文，我想知道查询计划优化器是否在每种情况下都能很好地与它们配合使用，或者我是否最好在较大的查询中取消这种计算.

I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm better to unnest such computation in my larger queries.

1. 查询计划优化器是否取消嵌套表值函数，如果它使感觉?
2. 如果没有，你怎么办建议避免代码重复这将通过手动发生解除它们的嵌套?
3. 如果有，怎么办你从执行中确定计划?

代码示例:

create table dbo.customers (
    [key] uniqueidentifier
    , constraint pk_dbo_customers
        primary key ([key])
)
go

/* assume large amount of data */
create table dbo.point_of_sales (
    [key] uniqueidentifier
    , customer_key uniqueidentifier
    , constraint pk_dbo_point_of_sales
        primary key ([key])
)
go

create table dbo.product_ranges (
    [key] uniqueidentifier
    , constraint pk_dbo_product_ranges
        primary key ([key])
)
go

create table dbo.products (
    [key] uniqueidentifier
    , product_range_key uniqueidentifier
    , release_date datetime
    , constraint pk_dbo_products 
        primary key ([key])
    , constraint fk_dbo_products_product_range_key 
        foreign key (product_range_key) 
        references dbo.product_ranges ([key])
)
go

.

/* assume large amount of data */
create table dbo.sales_history (
    [key] uniqueidentifier
    , product_key uniqueidentifier
    , point_of_sale_key uniqueidentifier
    , accounting_date datetime
    , amount money
    , quantity int
    , constraint pk_dbo_sales_history
        primary key ([key])
    , constraint fk_dbo_sales_history_product_key
        foreign key (product_key)
        references dbo.products ([key])
    , constraint fk_dbo_sales_history_point_of_sale_key
        foreign key (point_of_sale_key)
        references dbo.point_of_sales ([key])
)
go

create function dbo.f_sales_history_..snip.._date_range
(
    @accountingdatelowerbound datetime,
         @accountingdateupperbound datetime
)
returns table as
return (
    select
                  pos.customer_key
        , sh.product_key
        , sum(sh.amount) amount
        , sum(sh.quantity) quantity
    from 
        dbo.point_of_sales pos
        inner join dbo.sales_history sh 
            on sh.point_of_sale_key = pos.[key]
    where
                  sh.accounting_date between 
                      @accountingdatelowerbound and 
                      @accountingdateupperbound
    group by
                  pos.customer_key
                  , sh.product_key
)
go

-- TODO: insert some data

-- this is a table containing a selection of product ranges
declare @selectedproductranges table([key] uniqueidentifier)

-- this is a table containing a selection of customers
declare @selectedcustomers table([key] uniqueidentifier)

declare @low datetime
    , @up datetime

-- TODO: set top query parameters

.

select
         saleshistory.customer_key
         , saleshistory.product_key
         , saleshistory.amount
         , saleshistory.quantity
from
         dbo.products p
         inner join @selectedproductranges productrangeselection 
             on p.product_range_key = productrangeselection.[key]
         inner join @selectedcustomers customerselection on 1 = 1
         inner join 
         dbo.f_sales_history_..snip.._date_range(@low, @up) saleshistory
             on saleshistory.product_key = p.[key]
             and saleshistory.customer_key = customerselection.[key]

我希望样本有意义.

非常感谢您的帮助！

推荐答案

在这种情况下，它是一个内联表值函数"如果它有用(或查看)，优化器会简单地扩展(取消嵌套)它.

In this case, it's an "inline table valued function" The optimiser simply expands (unnests) it if it's useful (or view).

如果函数被外部​​查询视为黑匣子"，最快的方法是比较 SSMS 中显示的 IO 与探查器中的 IO.Profler 捕获了 SSMS 没有的黑匣子"IO.

If the function is treated as "black box" by the outer query, the quickest way is to compare IO shown in SSMS vs IO in profiler. Profler captures "black box" IO that SSMS does not.

博文作者:Adam Mechanic(他的书在我工作的抽屉里)

Blog post by Adam Mechanic (his book is in my drawer at work)

这篇关于查询计划优化器是否适用于连接/过滤表值函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！