sql-server - rapidas - plan de ejecución y optimización de consultas sql server 2008 r2

¿El optimizador del plan de consulta funciona bien con las funciones unidas/filtradas con valores de tabla?

Question

En SQLSERVER 2005, estoy usando la función de valor de tabla como una forma conveniente de realizar una agregación arbitraria en datos de subconjuntos de una tabla grande (rango de fechas de paso o parámetros similares).

Estoy usando tesis dentro de consultas más grandes como cálculos unificados y me pregunto si el optimizador de planes de consulta funciona bien con ellos en cada condición o si es mejor dejar de lado tal cálculo en mis consultas más grandes.

1. ¿Tiene el optimizador del plan de consultas funciones simples de valores de tabla si tiene sentido?
2. Si no es así, ¿qué recomiendas para evitar la duplicación de código que ocurriría anulándolos manualmente?
3. Si lo hace, ¿cómo lo identifica desde el plan de ejecución?

muestra de código:

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]

Espero que la muestra tenga sentido.

Muchas gracias por tu ayuda!

Answer 1

1) Sí, usando tu sintaxis, lo hace. Si por casualidad usó una UDF que devolvió una tabla que tenía lógica condicional, no lo haría.

3) El optimizador no le indicará qué parte de su consulta está optimizando, ya que puede combinar los fragmentos del plan con su función u optimizar los bits.

Answer 2

En este caso, se trata de una "función de tabla en línea valorada". El optimizador simplemente lo expande (lo desmarca) si es útil (o ver).

Si la consulta externa trata la función como "caja negra", la forma más rápida es comparar IO que se muestra en SSMS frente a IO en el generador de perfiles. Profler captura el IO de "caja negra" que el SSMS no capta.

Publicación del blog por Adam Mechanic (su libro está en mi cajón en el trabajo)