Consulta en vista particionada

Hola,
Tengo dos vistas particionadas A y B, formadas por tablas que llamaremos
A[x] y B[x] respectivamente. Cada tabla A[x] está relacionada con su tabla
correspondiente B[x] Os posteo el script de creación:

Inicio
script --
create table A1 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA1 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A1 CHECK ( Part = 1 ) )
create table A2 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA2 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A2 CHECK ( Part = 2 ) )
create table A3 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA3 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A3 CHECK ( Part = 3 ) )
GO

create view A as (
select * From A1 union all
select * From A2 union all
select * From A3 )
GO

insert into a values ( 1, 1, 'Registro A 1' )
insert into a values ( 2, 1, 'Registro A 2' )
insert into a values ( 1, 2, 'Registro A 1' )
insert into a values ( 2, 2, 'Registro A 2' )
insert into a values ( 1, 3, 'Registro A 1' )
insert into a values ( 2, 3, 'Registro A 2' )


create table B1 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB1 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B1 CHECK ( Part = 1 ),
CONSTRAINT FK_B1 FOREIGN KEY ( PK, Part ) REFERENCES A1 ( PK, Part ) )
create table B2 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB2 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B2 CHECK ( Part = 2 ),
CONSTRAINT FK_B2 FOREIGN KEY ( PK, Part ) REFERENCES A2 ( PK, Part ) )
create table B3 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB3 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B3 CHECK ( Part = 3 ),
CONSTRAINT FK_B3 FOREIGN KEY ( PK, Part ) REFERENCES A3 ( PK, Part ) )
GO

create view B as (
select * From B1 union all
select * From B2 union all
select * From B3 )
GO

insert into b values ( 1, 1, 'Registro B 1' )
insert into b values ( 2, 1, 'Registro B 2' )
insert into b values ( 1, 2, 'Registro B 1' )
insert into b values ( 2, 2, 'Registro B 2' )
insert into b values ( 1, 3, 'Registro B 1' )
insert into b values ( 2, 3, 'Registro B 2' )
Fin
script --

Ejecuto la siguiente consulta:

select * From a
inner join b on b.pk = a.pk and b.part = a.part
where a.part = 1

y si miramos el plan de ejecución, vemos como de las vistas A y B, sólo se
consultan A1 y B1. Perfecto porque es precisamente lo que se exige a una
vista particionada.

Ahora viene mi sorpresa: Ejecuto el siguiente código:

declare @part int
select @part = 1

select *
From a
inner join b on b.pk = a.pk and b.part = a.part
where a.part = @part

y en el plan de ejecución se recorren todas las tablas de las vistas A y

¿ Alguien sabría decir por qué ?

Muchísimas gracias. Se agradecerá cualquier comentario :)

Xavi

Hola,
Tengo dos vistas particionadas A y B, formadas por tablas que llamaremos
A[x] y B[x] respectivamente. Cada tabla A[x] está relacionada con su tabla
correspondiente B[x] Os posteo el script de creación:

Inicio
script --
create table A1 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA1 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A1 CHECK ( Part = 1 ) )
create table A2 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA2 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A2 CHECK ( Part = 2 ) )
create table A3 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA3 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A3 CHECK ( Part = 3 ) )
GO

create view A as (
select * From A1 union all
select * From A2 union all
select * From A3 )
GO

insert into a values ( 1, 1, 'Registro A 1' )
insert into a values ( 2, 1, 'Registro A 2' )
insert into a values ( 1, 2, 'Registro A 1' )
insert into a values ( 2, 2, 'Registro A 2' )
insert into a values ( 1, 3, 'Registro A 1' )
insert into a values ( 2, 3, 'Registro A 2' )


create table B1 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB1 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B1 CHECK ( Part = 1 ),
CONSTRAINT FK_B1 FOREIGN KEY ( PK, Part ) REFERENCES A1 ( PK, Part ) )
create table B2 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB2 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B2 CHECK ( Part = 2 ),
CONSTRAINT FK_B2 FOREIGN KEY ( PK, Part ) REFERENCES A2 ( PK, Part ) )
create table B3 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB3 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B3 CHECK ( Part = 3 ),
CONSTRAINT FK_B3 FOREIGN KEY ( PK, Part ) REFERENCES A3 ( PK, Part ) )
GO

create view B as (
select * From B1 union all
select * From B2 union all
select * From B3 )
GO

insert into b values ( 1, 1, 'Registro B 1' )
insert into b values ( 2, 1, 'Registro B 2' )
insert into b values ( 1, 2, 'Registro B 1' )
insert into b values ( 2, 2, 'Registro B 2' )
insert into b values ( 1, 3, 'Registro B 1' )
insert into b values ( 2, 3, 'Registro B 2' )
Fin
script --

Ejecuto la siguiente consulta:

select * From a
inner join b on b.pk = a.pk and b.part = a.part
where a.part = 1

y si miramos el plan de ejecución, vemos como de las vistas A y B, sólo se
consultan A1 y B1. Perfecto porque es precisamente lo que se exige a una
vista particionada.

Ahora viene mi sorpresa: Ejecuto el siguiente código:

declare @part int
select @part = 1

select *
From a
inner join b on b.pk = a.pk and b.part = a.part
where a.part = @part

y en el plan de ejecución se recorren todas las tablas de las vistas A y

¿ Alguien sabría decir por qué ?

Muchísimas gracias. Se agradecerá cualquier comentario :)

Xavi

Hola,
Tengo dos vistas particionadas A y B, formadas por tablas que llamaremos
A[x] y B[x] respectivamente. Cada tabla A[x] está relacionada con su tabla
correspondiente B[x] Os posteo el script de creación:

Inicio
script --
create table A1 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA1 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A1 CHECK ( Part = 1 ) )
create table A2 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA2 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A2 CHECK ( Part = 2 ) )
create table A3 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA3 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A3 CHECK ( Part = 3 ) )
GO

create view A as (
select * From A1 union all
select * From A2 union all
select * From A3 )
GO

insert into a values ( 1, 1, 'Registro A 1' )
insert into a values ( 2, 1, 'Registro A 2' )
insert into a values ( 1, 2, 'Registro A 1' )
insert into a values ( 2, 2, 'Registro A 2' )
insert into a values ( 1, 3, 'Registro A 1' )
insert into a values ( 2, 3, 'Registro A 2' )


create table B1 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB1 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B1 CHECK ( Part = 1 ),
CONSTRAINT FK_B1 FOREIGN KEY ( PK, Part ) REFERENCES A1 ( PK, Part ) )
create table B2 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB2 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B2 CHECK ( Part = 2 ),
CONSTRAINT FK_B2 FOREIGN KEY ( PK, Part ) REFERENCES A2 ( PK, Part ) )
create table B3 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB3 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B3 CHECK ( Part = 3 ),
CONSTRAINT FK_B3 FOREIGN KEY ( PK, Part ) REFERENCES A3 ( PK, Part ) )
GO

create view B as (
select * From B1 union all
select * From B2 union all
select * From B3 )
GO

insert into b values ( 1, 1, 'Registro B 1' )
insert into b values ( 2, 1, 'Registro B 2' )
insert into b values ( 1, 2, 'Registro B 1' )
insert into b values ( 2, 2, 'Registro B 2' )
insert into b values ( 1, 3, 'Registro B 1' )
insert into b values ( 2, 3, 'Registro B 2' )
Fin
script --

Ejecuto la siguiente consulta:

select * From a
inner join b on b.pk = a.pk and b.part = a.part
where a.part = 1

y si miramos el plan de ejecución, vemos como de las vistas A y B, sólo se
consultan A1 y B1. Perfecto porque es precisamente lo que se exige a una
vista particionada.

Ahora viene mi sorpresa: Ejecuto el siguiente código:

declare @part int
select @part = 1

select *
From a
inner join b on b.pk = a.pk and b.part = a.part
where a.part = @part

y en el plan de ejecución se recorren todas las tablas de las vistas A y
B.
¿ Alguien sabría decir por qué ?

Muchísimas gracias. Se agradecerá cualquier comentario :)

Xavi

Bueno, los misterios del optimizador de consultas son inescrutables, creo
que lo que pasa es que cuando pones una variable el sistema crea un plan
de ejecución genérico que podría volver a usar para cualquier valor sin
recompilar la consulta. digamos que el plan de ejecución óptimo con @a=1
no es el que saca pero si lo es con valor genérico.

Por otra parte en el momento en el que se genera el plan de ejecución no
se evalua el valor de las variables, fijate que si se hiciese habría que
haber ejecutado las sentencias anteriores (en un caso genérico eso puede
ser tremendamente costoso).

Sin embargo si se pone un valor fijo el sistema compara con una constante,
tira de checks y ve el mejor plan para ese valor.

Espero que se entienda.


-
Miguel Egea Gómez
Microsoft SQL-Server MVP
Webmaster de PortalSql.Com
¿Te interesa participar en las reuniones
del grupo de Usuarios de SQL-Server y .NET
Se harán en levante de España, (Alicante o Murcia)?


"XaviF" escribió en el mensaje
news:

Hola,
Tengo dos vistas particionadas A y B, formadas por tablas que llamaremos
A[x] y B[x] respectivamente. Cada tabla A[x] está relacionada con su
tabla
correspondiente B[x] Os posteo el script de creación:

Inicio
script --
create table A1 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA1 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A1 CHECK ( Part = 1 ) )
create table A2 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA2 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A2 CHECK ( Part = 2 ) )
create table A3 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKA3 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_A3 CHECK ( Part = 3 ) )
GO

create view A as (
select * From A1 union all
select * From A2 union all
select * From A3 )
GO

insert into a values ( 1, 1, 'Registro A 1' )
insert into a values ( 2, 1, 'Registro A 2' )
insert into a values ( 1, 2, 'Registro A 1' )
insert into a values ( 2, 2, 'Registro A 2' )
insert into a values ( 1, 3, 'Registro A 1' )
insert into a values ( 2, 3, 'Registro A 2' )


create table B1 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB1 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B1 CHECK ( Part = 1 ),
CONSTRAINT FK_B1 FOREIGN KEY ( PK, Part ) REFERENCES A1 ( PK, Part ) )
create table B2 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB2 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B2 CHECK ( Part = 2 ),
CONSTRAINT FK_B2 FOREIGN KEY ( PK, Part ) REFERENCES A2 ( PK, Part ) )
create table B3 ( PK int, Part int, Value varchar(20),
CONSTRAINT PK_PKB3 PRIMARY KEY CLUSTERED ( PK, Part ) ON [PRIMARY],
CONSTRAINT CK_B3 CHECK ( Part = 3 ),
CONSTRAINT FK_B3 FOREIGN KEY ( PK, Part ) REFERENCES A3 ( PK, Part ) )
GO

create view B as (
select * From B1 union all
select * From B2 union all
select * From B3 )
GO

insert into b values ( 1, 1, 'Registro B 1' )
insert into b values ( 2, 1, 'Registro B 2' )
insert into b values ( 1, 2, 'Registro B 1' )
insert into b values ( 2, 2, 'Registro B 2' )
insert into b values ( 1, 3, 'Registro B 1' )
insert into b values ( 2, 3, 'Registro B 2' )
Fin
script --

Ejecuto la siguiente consulta:

select * From a
inner join b on b.pk = a.pk and b.part = a.part
where a.part = 1

y si miramos el plan de ejecución, vemos como de las vistas A y B, sólo
se
consultan A1 y B1. Perfecto porque es precisamente lo que se exige a una
vista particionada.

Ahora viene mi sorpresa: Ejecuto el siguiente código:

declare @part int
select @part = 1

select *
From a
inner join b on b.pk = a.pk and b.part = a.part
where a.part = @part

y en el plan de ejecución se recorren todas las tablas de las vistas A y
B.
¿ Alguien sabría decir por qué ?

Muchísimas gracias. Se agradecerá cualquier comentario :)

Xavi