查询计划优化器是否可以与连接/过滤的表值函数很好地配合?
在 SQLSERVER 2005 中,我使用表值函数作为对大表中的子集数据执行任意聚合的便捷方法(传递日期范围或此类参数)。
我在较大的查询中使用这些作为连接计算,我想知道查询计划优化器是否在每种情况下都能很好地与它们配合,或者我是否最好在较大的查询中取消此类计算。
- 查询计划优化器是否解除嵌套 表值函数,如果它使 感觉?
- 如果没有,你会做什么 建议避免代码重复 这将通过手动发生 解除它们的嵌套?
- 如果是的话怎么办 您可以从执行中识别出这一点 计划?
代码示例:
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 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.
- Does query plan optimizer unnest
table-valued functions if it make
sense? - If it doesn't, what do you
recommend to avoid code duplication
that would occur by manually
unnesting them? - If it does, how do
you identify that from the execution
plan?
code sample:
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]
I hope the sample makes sense.
Much thanks for your help!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
在这种情况下,它是一个“内联表值函数”
如果它有用(或视图),优化器会简单地扩展(取消嵌套)它。
如果函数被外部查询视为“黑匣子”,最快的方法是比较 SSMS 中显示的 IO 与分析器中的 IO。
Profler 捕获 SSMS 无法捕获的“黑匣子”IO。
Adam Mechanic 的博客文章(他的书在我工作时的抽屉里)
In this case, it's an "inline table valued function"
The optimiser simply expands (unnests) it if it's useful (or view).
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.
Blog post by Adam Mechanic (his book is in my drawer at work)
1)是的,使用你的语法,确实如此。 但如果您碰巧使用返回一个包含条件逻辑的表的 UDF,则不会。
3)优化器不会指出它正在优化查询的哪一部分,因为它可能认为适合将计划的块与您的函数结合起来,或者优化一些位。
1) Yes, using your syntax, it does. If you happened to use a UDF that returned a table which had conditional logic in it, it would not, though.
3) The optimizer won't point out what part of your query it's optimizing, because it may see fit to combine chunks of the plan with your function, or to optimize bits away.