Una tabla por cada año y UNION

jeje, Si no es una cuestión de liar a nadie :-), de lo que se trata es de
que quede claro.

1.- En el Caso de Pedro, yo creo que no le está dando ningún beneficio
particionar independientemente de cual sea su índice clustered ya que si
tiene 4 años con una carga más o menos equivalente por día 1 dia no será
nunca más de un 10-11% que es generalmente el umbral que SQL elige para
pasar de un index seek + bookmark a un table o index scan.
2.- En cualquier caso también es muy prudente lo que dice Jesús, que sin
saber que consultas ejecutas es difícil saber si te conviene o no (aunque yo
me moje y te diga que no te sirve).
3.- Si unes las tablas y detectas una caida de rendimiento un cambio en la
estrategia de indexación (ya sea cambiar el índice clustered, ya sea crear
nuevos índices) generalmente resolverá esa merma de rendimiento.

Si os parece eso podría ser el resumen.

Solo como parentesis y para ir en contra de lo que yo digo, cuando los
índices pasan a tener más niveles en el arbol B, esto puede suponer una
caida de rendimiento ya que hay que hacer más lecturas y en este caso las
tablas particionadas tendrían un pelín de ventaja.. aunque también hay otros
argumentos para lo contrario.

-
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)?



"SqlRanger" escribió en el mensaje
news:%

Pos eso. No líes al personal Miguelón.

En este caso particular, en el que se hace:

select * from LaTabla where fecha = UnaFecha

Como hay muchos registros para una fecha dada, un índice no agrupado sobre
la fecha no sirve para nada y se hace una exploración completa de la
tabla.
Así que si tenemos la tabla particionada por fechas tendremos un aumento
del
rendimiento igual al número de tablas porque sólo se recorría una de las
tablas, como dije en mi primer mensaje de este hilo.

Ahora en esta consulta, si el índice por fecha es agrupado, es una
tontería
tener la tabla particionada por fechas ya que como mucho la diferencia
entre
particionarla y no particionarla es de leer una página más, una nimiedad.
Además entonces esta consulta se ejecuta muy rápido.

Entonces todo parece claro. Ponemos un índice agrupado en la fecha y no
particionamos, ¿punto y final?. Pues no, qué pasa con las demás consultas
¿Qué otras consultas se hacen?. Podemos haber perjudicado el rendimiento
de
las otras consultas, y lo que hemos hecho es desvestir a un santo para
vestir a otro.

Por ejemplo la consulta:

select * from LaTabla where DocNo = UnDocNo

se vería perjudicada, ya que ahora el índice (DocNo, AccNo) no es
agrupado
y seguramente se tendría que recorrer toda la tabla porque hay muchos
registros con el mismo DocNo, pero eso yo no lo sé. Como no tengo toda la
información no puedo decir qué es lo mejor en este caso.

Sin embargo una consulta como esta:

select * from LaTabla where DocNo=UnDocNo and AccNo = UnAccNo

Se vería perjudicada en muy poca medida, se usaría el índice no agrupado
(DocNo, AccNo) que es único y la sobrecarga sería un bookmark lookup
adicional (4 páginas más leídas como mucho).

¿Qué consultas se hacen? ¿Qué selectividad tienen los índices? Yo qué sé.
Si
lo supiera ya habría dado una respuesta concreta.

Saludos,

Jesús López
MVP

"Miguel Egea" escribió en el mensaje
news:

Jesús, no estoy de acuerdo en lo que se puede sobreentender de lo que

dices.

El resumen es que si SQL no necesita hacer un tableScan en ninguna

consulta

no se ve perjudicado, es decir si la indexación (ya sea clustered o no
clustered) es adecuada es muy probable que de exactamente igual que estén
particionadas como juntas.


-
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)?


"SqlRanger" escribió en el mensaje
news:%
> Eso depende del tipo de consultas que se hagan sobre la tabla. Si la

tabla

> sólo se consulta por la fecha y por la clave primaria completa, podrías
> prescindir de particionar la tabla, teniendo un índice agrupado para la
> fecha y un índice no agrupado para la clave primaria. Teniendo un
> índice
> agrupado por la fecha, las consultas que tengan como criterio la fecha
> verán
> aumentado su rendimiento de forma espectacular.
>
> Ten en cuenta además que el particionamiento de la tabla no sólo puede
> aumentar el rendimiento de ciertas consultas, sino que también es
> beneficioso para ciertas operaciones, como copias de seguridad de

archivos

> y
> grupos de archivos, si cada tabla anual la pones en un archivo

diferente,

> no
> tienes que hacer copias de seguridad periódicas de ellas, con tener una
> copia es suficiente ya que no cambian los datos.
>
> Saludos
>
> Jesús López
> MVP
>
>
> "Pedro Jose Caceres" escribió en el
> mensaje
> news:u3vOK$
>> Ok... muy buena la discusión y se lo agradezco.
>>
>> Podemos concluir que para mi caso:
>> Si tengo un indice por la fecha no tengo necesidad de tener las

tablas

>> particionadas por año por más grandes que éstas sean ???
>> Díganme que si ! diganme que si!!! :))) seria un sueño para mi tener

una

>> sola tabla para movimientos y que no me afecte el registro diario
> intensivo
>> de transacciones.
>>
>>
>> Gracias
>>
>>
>>
>> "SqlRanger" wrote in message
>> news:
>> > Comentarios en línea,
>> >
>> > Saludos
>> >
>> > Jesús López
>> > MVP
>> >
>> >
>> > "Javier Loria" escribió en el mensaje
>> > news:
>> > > Hola:
>> > > >Porque algunos dicen que es inversamente proporcional?
>> > > No se, pero ese es un "Mito Urbano", probablemente

proveniente

> de
>> > SQL
>> > > 6.5 o de otras plataformas.Disculpa lo largo del mensaje pero es
>> > > mejor
>> que
>> > > lo pruebes.
>> > > El siguiente codigo te genera una tabla parecidas al esquema

que

> nos
>> > > comentaste, la columna blanco es solo para simular otras columnas.
>> > > TABLAS INDEPENDIENTES ==>> >> > > CREATE TABLE Mov2001(
>> > > Id INT NOT NULL PRIMARY KEY CLUSTERED
>> > > , Fecha SMALLDATETIME NOT NULL
>> > > , Nombre CHAR(20) NOT NULL
>> > > , Blanco CHAR(100) DEFAULT('')
>> > > )
>> > >
>> > > CREATE TABLE Mov2002(
>> > > Id INT NOT NULL PRIMARY KEY CLUSTERED
>> > > , Fecha SMALLDATETIME NOT NULL
>> > > , Nombre CHAR(20) NOT NULL
>> > > , Blanco CHAR(100) DEFAULT('')
>> > > )
>> > >
>> > > CREATE TABLE Mov2003(
>> > > Id INT NOT NULL PRIMARY KEY CLUSTERED
>> > > , Fecha SMALLDATETIME NOT NULL
>> > > , Nombre CHAR(20) NOT NULL
>> > > , Blanco CHAR(100) DEFAULT('')
>> > > )
>> > > CREATE NONCLUSTERED INDEX IN_FechaMov2001
>> > > ON Mov2001(Fecha)
>> > > CREATE NONCLUSTERED INDEX IN_FechaMov2002
>> > > ON Mov2002(Fecha)
>> > > CREATE NONCLUSTERED INDEX IN_FechaMov2003
>> > > ON Mov2003(Fecha)
>> > > GO
>> > >
>> > > CREATE VIEW Movimientos AS
>> > > SELECT * FROM Mov2001 -- Pendiente eliminar *'s
>> > > UNION ALL
>> > > SELECT * FROM Mov2002
>> > > UNION ALL
>> > > SELECT * FROM Mov2003
>> > >
>> > > FIN DE TABLAS INDEPENDIENTES ==>> >> > > Para simular la carga hice un script que genera n movimientos
>> > > diarios,
> y
>> > que
>> > > probe con 400 diarios que son solo 146,000 filas anuales, pero
>> > > que
> bien
>> > > podrias cambiar a 15000 filas diarias
>> > > LLENADO DE TABLAS ==>> >> > > DECLARE @NumeroDiario INT
>> > > SET @
>> > >
>> > > INSERT MOV2001(ID, Fecha, Nombre)
>> > > SELECT ((N1.Numero-1)*@NumeroDiario)+N2.Numero
>> > > , DATEADD(dd,N1.Numero-1,'2001/01/01')
>> > > , CONVERT(CHAR(10),DATEADD(dd,N1.Numero-1,'2001/01/01'),112)
>> > > + RIGHT(' '
>> > > +CAST(((N1.Numero-1)*@NumeroDiario)+N2.Numero AS

VARCHAR(7)),7)

>> > > FROM Numeros AS N1
>> > > CROSS JOIN Numeros AS N2
>> > > WHERE N1.Numero<366
>> > > AND N2.Numero<=@NumeroDiario
>> > >
>> > > INSERT Mov2002(ID, Fecha, Nombre)
>> > > SELECT Id+@NumeroDiario*365 , DATEADD(year, 1, Fecha)
>> > > , Nombre
>> > > FROM Mov2001
>> > >
>> > > INSERT Mov2003(ID, Fecha, Nombre)
>> > > SELECT Id+@NumeroDiario*365*2
>> > > , DATEADD(year, 1, Fecha)
>> > > , Nombre
>> > > FROM Mov2002
>> > > GO
>> > >
>> > > FIN DE LLENADO DE TABLAS ==>> >> > > La consulta usa una tabla de numeros muy sencilla con una
>> > > unica
>> > columna
>> > > que tiene los numeros del 1 en adelante, en tu caso deberia ser
>> > > por
>> > > lo
>> > menos
>> > > hasta 15000.
>> > > Los resultados son:
>> > > ==>> >> > > SELECT * FROM Mov2003 WHERE Fecha='2003-07-01'

reads

> 0,
>> > > read-ahead reads 0.
>> > > ==>> >> > > SELECT * FROM Movimientos WHERE Fecha='2003-07-01'
>> > > reads
>> > > 0,
>> > > read-ahead reads 0.
>> > > 0,
>> > > read-ahead reads 0.
>> > > 0,
>> > > read-ahead reads 0.
>> > > ==>> >> > > La diferencia entre usar la vista y la tabla es casi nula
>> > > (solo
> lee
>> el
>> > > encabezado del indice y la descarta). No hay cambios en el

desempeno

>> > > importantes por usar una u otra.
>> >
>> > Si hubiéramos puesto una restricción CHECK en las tablas que

asegurara

> que
>> > la fecha sólo puede ser del año correspondiente SQL Server no habría
>> tenido
>> > que ir al índice a comprobarlo y la diferencia entre tabla y vista
> habría
>> > sido completamente nula.
>> >
>> >
>> >
>> > > Si borras todo y creas una unica tabla de movimientos:
>> > > TABLA UNICA ==>> >> > > CREATE TABLE Movimientos(
>> > > Id INT NOT NULL
>> > > PRIMARY KEY CLUSTERED
>> > > , Fecha SMALLDATETIME NOT NULL
>> > > , Nombre CHAR(20) NOT NULL
>> > > , Blanco CHAR(100) DEFAULT('')
>> > > )
>> > >
>> > > == FIN DE TABLA UNICA ==>> >> > > CREATE NONCLUSTERED INDEX IN_FechaMovimientos
>> > > ON Movimientos(Fecha)
>> > >
>> > > DECLARE @NumeroDiario INT
>> > > SET @
>> > >
>> > > INSERT MOVIMIENTOS(ID, Fecha, Nombre)
>> > > SELECT ((N1.Numero-1)*@NumeroDiario)+N2.Numero AS Numero
>> > > ,DATEADD(dd,N1.Numero-1,'2001/01/01') AS Fecha
>> > > , CONVERT(CHAR(10),DATEADD(dd,N1.Numero-1,'2001/01/01'),112)
>> > > + RIGHT(' '
> +CAST(((N1.Numero-1)*@NumeroDiario)+N2.Numero
>> AS
>> > > VARCHAR(7)),7) as Nombre
>> > > FROM Numeros AS N1
>> > > CROSS JOIN Numeros AS N2
>> > > WHERE N1.Numero<366
>> > > AND N2.Numero<=@NumeroDiario
>> > >
>> > > INSERT Movimientos(ID, Fecha, Nombre)
>> > > SELECT Id+@NumeroDiario*365
>> > > , DATEADD(year, 1, Fecha)
>> > > , Nombre
>> > > FROM Movimientos
>> > > UNION ALL
>> > > SELECT Id+@NumeroDiario*365*2
>> > > , DATEADD(year, 1, Fecha)
>> > > , Nombre
>> > > FROM Mov2002
>> > > GO
>> > > SELECT * FROM Movimientos WHERE Fecha='2003-07-01'
>> > >

reads

> 0,
>> > > read-ahead reads 0.
>> > > ==>> >> >
>> > Creo que aquí hay un error. La tabla es Movimientos, no Mov2003. El
> número
>> > de logical reads debería haber sido el triple. Ya que la consulta

hace

> una
>> > exploración completa de la tabla (no usa el índice de la fecha) y la
> tabla
>> > es tres veces más grande.
>> >
>> > > Veras que la consulta es exactamente igual que si usaras cada
> tabla
>> > por
>> > > separado. La estructura de indices de SQL hace que normalmente sea
>> > > mas
>> > > eficiente tener una sola tabla a n tablas iguales EXCEPTO cuando

cada

>> una
>> > de
>> > > las tabla va a un disco independiente. En cuyo caso la vista es lo
>> > > recomendable.
>> >
>> >
>> > No estoy de acuerdo.Creo que has cometido algún error en las
>> > pruebas.
>> >
>> > Para el caso de la consulta que estamos evaluando,. Será más

eficiente

> (el
>> > número de tablas más eficiente) particionar la tabla cuando el
>> > índice
>> sobre
>> > la Fecha es un índice no agrupado y la consulta devuelve un número
>> > de
>> > registros lo suficientemente grande como para que SQL Server

determine

> que
>> > es mejor una exploración de la tabla que usar el índice. La razón es
>> simple,
>> > SQL Server sólo tendrá que hacer la exploración completa de una de

las

>> > tablas, las otras tablas no las mirará ya que hay una restricción

CHECK

>> que
>> > se lo indica.
>> >
>> > Si el índice sobre la columna Fecha es agrupado, la diferencia entre
>> > particionar la tabla y no particionarala sería mínima, como mucho,
> habría
>> > una diferencia de una lectura lógica más con tabla única. Me estoy
>> > refiriendo, por supuesto, a consultas en las que se obtienen todos

las

>> > columnas y en la cláusula where está la columna de partición.
>> >
>> > > Es posible definir un JOB anual que agregue una tabla y

modifique

> la
>> > > vista actual si se requiere.
>> > >
>> > >
>> > > Javier Loria
>> > > Costa Rica
>> > > Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
>> > > que pueda ser copiado y pegado al Query Analizer.
>> > > La version de SQL y Service Pack tambien ayuda
>> > >
>> > > "Pedro Jose Caceres" wrote in message
>> > > news:
>> > > > > Hola:
>> > > > > El problema de esas tabla es que producen una perdida de
>> desempeno
>> > > > > importante, asi que considera la posiblidad de unirlas en una
>> > > > > sola
>> > > tabla,
>> > > > >
>> > > >
>> > > > Muchas gracias por la respuesta. Pero
>> > > > Ok.. entonces por qué algunos dicen que el tamaño de las tablas

es

>> > > > inversamente proporcional al buen desempeño ?
>> > > > A saber, son tablas de varios millones de registros (entre 4 y 5
>> > millones)
>> > > y
>> > > > son de operaciones diarias de uso intensivo y lo de la
>> > > > separacion
>> viene
>> > > > porque los nuevos registros son casi invariablemente de fecha
>> > > > del
> año
>> > > > actual, por tanto las demas no se utilizan a menos que sea en
> reportes
>> y
>> > > > consultas historicas.
>> > > > Como le dije a Miguel arriba, la razon de partirlas fue

basicamente

>> > > buscando
>> > > > mayor eficiencia en el registro diario de operaciones y creo que
>> > > > lei
>> por
>> > > ahi
>> > > > que esa era una forma de eficientizarlo.
>> > > > La verdad que a veces uno leyendo tantas opiniones distintas no
>> > > > sabe
>> por
>> > > > donde irse. :)
>> > > >
>> > > > >a
>> > > > > menos que cada una de ellas tenga su propio disco duro o

arreglo

> de
>> > > > discos.
>> > > > > Adicionalmente si no quieres fusionarlas lo mejor seria,
> usando
>> la
>> > > > > recomendacion de Miguel, crear una vista del tipo:
>> > > > > >> >> > > > > CREATE VIEW Movimientos(Columna1, )
>> > > > > FROM
>> > > > > SELECT Columna1, Columna2 FROM Mov2001
>> > > > > UNION ALL -- *** ALL es muy importante
>> > > > > SELECT Columna1, Columna2 FROM Mov2002
>> > > > > UNION ALL
>> > > > >
>> > > >
>> > > >
>> > > > El problema es que la cerrar cada año, como dije arriba hay que
> crear
>> > una
>> > > > nueva tabla MovAAAA entonces implicaria modificar la vista ?

esto

> se
>> > > podria
>> > > > hacer en t-sql tambien ?
>> > > >
>> > > >
>> > > > > >> >> > > > > Luego podras hacer un SELECT:
>> > > > > >> >> > > > > SELECT Columna1, ... FROM Movimientos
>> > > > > WHERE ITEM='xyz' FECHA>='01/04/2001'
>> > > > > >> >> > > > > Que sera muy rapido, porque por las estadisticas de SQL el
> sabra
>> > que
>> > > > > ninguna de las tablas, excepto la de Mov2004 tiene datos que
> cumplan
>> > la
>> > > > > condicion de las fechas..
>> > > > > Saludos,
>> > > > >
>> > > > >
>> > > > > Javier Loria
>> > > > > Costa Rica
>> > > > > Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
>> > > > > que pueda ser copiado y pegado al Query Analizer.
>> > > > > La version de SQL y Service Pack tambien ayuda
>> > > > >
>> > > > > "Pedro Jose Caceres" wrote in
>> > > > > message
>> > > > > news:#
>> > > > > > Tengo una tabla de movimientos para cada año, MOV2003,
>> > > > > > MOV2004,
>> > etc.
>> > > > > > Cuando se cierra un año por tanto, tengo que crear la nueva
> tabla
>> > > > > > correspondiente al nuevo año. Puedo hacer el CREATE TABLE
>> > > > > > en
>> t-sql
>> > > sin
>> > > > > > tener que sacar los usuarios del sistema ? Como puedo tomar

la

>> > > > estructura
>> > > > > de
>> > > > > > otra de las tablas ya existentes para que se cree igual ?
>> > > > > >
>> > > > > > Otra cosa es que para en una consulta ver movimientos que
> abarque
>> > dos
>> > > o
>> > > > > mas
>> > > > > > años, hago una UNION, preparando dinamicamente la

instruccion

>> > SELECT
>> > > > > desde
>> > > > > > la aplicacion y enviandola por odbc al servidor. Por ej. si

la

>> lista
>> > > de
>> > > > > > movimientos abarca va desde el 01/04/2001 al 31/03/2004,

estoy

>> > armando
>> > > > > desde
>> > > > > > el front-end la union asi::
>> > > > > >
>> > > > > >
>> > > > > > SELECT columna1, .., from MOV2001 WHERE ITEM='ELITEM'
>> > > > FECHA>
/04/2001
>> > > > > > UNION
>> > > > > > SELECT columna1, .., from MOV2002 WHERE ITEM='ELITEM'
>> > > > > > UNION
>> > > > > > SELECT columna1, .., from MOV2003 WHERE ITEM='ELITEM'
>> > > > > > UNION
>> > > > > > SELECT columna1, .., from MOV2004 WHERE FECHA<1/03/2004
>> > > > > > ORDER BY FECHA
>> > > > > >
>> > > > > >
>> > > > > > Consideran que sea esta la mejor forma para hacer esto ?
>> > > > > >
>> > > > > > De no ser, que otras opciones hay para extraer esta

informacion

>> > quizas
>> > > > en
>> > > > > un
>> > > > > > SP del mismo servidor?
>> > > > > >
>> > > > > > Gracias
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>

Hola Jesus:
a) La razon sobre la que no use el CHECK fue simple, no ayuda al
argumento y complica las cosas para nuestro amigo Pedro. Pero tienes razon
el CHECK hace que la vista y las tablas particionadas obtengan exactamente
el mismo rendimiento, porque se ahorran 2 logical reads en cada una de las
tablas, con esto en lugar de ser 1269+2+2 Logical reads son unicamente 1269.
b) Es correcto que en mi posteo dice Mov2003 cuando era Movimientos. Mil
disculpas por esto, no es justificacion pero ocurrio porque cuando copie el
codigo en el Outlook Espress le di algo de formato y copie y pegue. Si
corres el script veras que el resultado es el mismo 1269 logical reads.
c) En cuanto a :

No estoy de acuerdo.Creo que has cometido algún error en las pruebas.

Para el caso de la consulta que estamos evaluando,. Será más eficiente

(el

número de tablas más eficiente) particionar la tabla cuando el índice

sobre

la Fecha es un índice no agrupado y la consulta devuelve un número de
registros lo suficientemente grande como para que SQL Server determine

que

es mejor una exploración de la tabla que usar el índice. La razón es

simple,

SQL Server sólo tendrá que hacer la exploración completa de una de las
tablas, las otras tablas no las mirará ya que hay una restricción CHECK

que

se lo indica.

Disculpa por no ser mas cortez, pero lo anterior es simplemente una
tonteria de marca mayor. Si no fueras MVP, si no firmaras SQLRanger, si no
fueras un viejo participante de este foro, no seria tan duro contigo; pero
traes confusion a un tema que Miguel Egea y yo hemos tratado de aclarar y
donde me he tomado el tiempo de escribir el codigo de T-SQL para que
cualquiera pueda hacer pruebas.
Me recuerdas a un verso de economistas:
Si puedes hacer algunas acrobacias
con un poco de matematicas
suficientemente lejos llegaras.

Si tu idea no es defendible
no la hagas compresible
o la gente lo descubrira

y llamaras la atencion
solo si no haces mencion
sobre lo que trata la cosa
Te pido por favor, para aclarar cualquier duda, que escribas en codigo
SQL un ejemplo que en el contexto de Jose que nos demuestre a todos como el
particionamiento de la tabla de movimientos ayuda el desempeno y que le
ayuden a obtener "mayor eficiencia en el registro diario de operaciones".
Saludos,


Javier Loria
Costa Rica
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda

"SqlRanger" wrote in message
news:

Comentarios en línea,

Saludos

Jesús López
MVP


"Javier Loria" escribió en el mensaje
news:
> Hola:
> >Porque algunos dicen que es inversamente proporcional?
> No se, pero ese es un "Mito Urbano", probablemente proveniente de
SQL
> 6.5 o de otras plataformas.Disculpa lo largo del mensaje pero es mejor

que

> lo pruebes.
> El siguiente codigo te genera una tabla parecidas al esquema que nos
> comentaste, la columna blanco es solo para simular otras columnas.
> TABLAS INDEPENDIENTES ==> > CREATE TABLE Mov2001(
> Id INT NOT NULL PRIMARY KEY CLUSTERED
> , Fecha SMALLDATETIME NOT NULL
> , Nombre CHAR(20) NOT NULL
> , Blanco CHAR(100) DEFAULT('')
> )
>
> CREATE TABLE Mov2002(
> Id INT NOT NULL PRIMARY KEY CLUSTERED
> , Fecha SMALLDATETIME NOT NULL
> , Nombre CHAR(20) NOT NULL
> , Blanco CHAR(100) DEFAULT('')
> )
>
> CREATE TABLE Mov2003(
> Id INT NOT NULL PRIMARY KEY CLUSTERED
> , Fecha SMALLDATETIME NOT NULL
> , Nombre CHAR(20) NOT NULL
> , Blanco CHAR(100) DEFAULT('')
> )
> CREATE NONCLUSTERED INDEX IN_FechaMov2001
> ON Mov2001(Fecha)
> CREATE NONCLUSTERED INDEX IN_FechaMov2002
> ON Mov2002(Fecha)
> CREATE NONCLUSTERED INDEX IN_FechaMov2003
> ON Mov2003(Fecha)
> GO
>
> CREATE VIEW Movimientos AS
> SELECT * FROM Mov2001 -- Pendiente eliminar *'s
> UNION ALL
> SELECT * FROM Mov2002
> UNION ALL
> SELECT * FROM Mov2003
>
> FIN DE TABLAS INDEPENDIENTES ==> > Para simular la carga hice un script que genera n movimientos diarios, y
que
> probe con 400 diarios que son solo 146,000 filas anuales, pero que bien
> podrias cambiar a 15000 filas diarias
> LLENADO DE TABLAS ==> > DECLARE @NumeroDiario INT
> SET @
>
> INSERT MOV2001(ID, Fecha, Nombre)
> SELECT ((N1.Numero-1)*@NumeroDiario)+N2.Numero
> , DATEADD(dd,N1.Numero-1,'2001/01/01')
> , CONVERT(CHAR(10),DATEADD(dd,N1.Numero-1,'2001/01/01'),112)
> + RIGHT(' '
> +CAST(((N1.Numero-1)*@NumeroDiario)+N2.Numero AS VARCHAR(7)),7)
> FROM Numeros AS N1
> CROSS JOIN Numeros AS N2
> WHERE N1.Numero<366
> AND N2.Numero<=@NumeroDiario
>
> INSERT Mov2002(ID, Fecha, Nombre)
> SELECT Id+@NumeroDiario*365 , DATEADD(year, 1, Fecha)
> , Nombre
> FROM Mov2001
>
> INSERT Mov2003(ID, Fecha, Nombre)
> SELECT Id+@NumeroDiario*365*2
> , DATEADD(year, 1, Fecha)
> , Nombre
> FROM Mov2002
> GO
>
> FIN DE LLENADO DE TABLAS ==> > La consulta usa una tabla de numeros muy sencilla con una unica
columna
> que tiene los numeros del 1 en adelante, en tu caso deberia ser por lo
menos
> hasta 15000.
> Los resultados son:
> ==> > SELECT * FROM Mov2003 WHERE Fecha='2003-07-01'
> read-ahead reads 0.
> ==> > SELECT * FROM Movimientos WHERE Fecha='2003-07-01'
> read-ahead reads 0.
> read-ahead reads 0.
> read-ahead reads 0.
> ==> > La diferencia entre usar la vista y la tabla es casi nula (solo lee

el

> encabezado del indice y la descarta). No hay cambios en el desempeno
> importantes por usar una u otra.

Si hubiéramos puesto una restricción CHECK en las tablas que asegurara que
la fecha sólo puede ser del año correspondiente SQL Server no habría

tenido

que ir al índice a comprobarlo y la diferencia entre tabla y vista habría
sido completamente nula.



> Si borras todo y creas una unica tabla de movimientos:
> TABLA UNICA ==> > CREATE TABLE Movimientos(
> Id INT NOT NULL
> PRIMARY KEY CLUSTERED
> , Fecha SMALLDATETIME NOT NULL
> , Nombre CHAR(20) NOT NULL
> , Blanco CHAR(100) DEFAULT('')
> )
>
> == FIN DE TABLA UNICA ==> > CREATE NONCLUSTERED INDEX IN_FechaMovimientos
> ON Movimientos(Fecha)
>
> DECLARE @NumeroDiario INT
> SET @
>
> INSERT MOVIMIENTOS(ID, Fecha, Nombre)
> SELECT ((N1.Numero-1)*@NumeroDiario)+N2.Numero AS Numero
> ,DATEADD(dd,N1.Numero-1,'2001/01/01') AS Fecha
> , CONVERT(CHAR(10),DATEADD(dd,N1.Numero-1,'2001/01/01'),112)
> + RIGHT(' ' +CAST(((N1.Numero-1)*@NumeroDiario)+N2.Numero

AS

> VARCHAR(7)),7) as Nombre
> FROM Numeros AS N1
> CROSS JOIN Numeros AS N2
> WHERE N1.Numero<366
> AND N2.Numero<=@NumeroDiario
>
> INSERT Movimientos(ID, Fecha, Nombre)
> SELECT Id+@NumeroDiario*365
> , DATEADD(year, 1, Fecha)
> , Nombre
> FROM Movimientos
> UNION ALL
> SELECT Id+@NumeroDiario*365*2
> , DATEADD(year, 1, Fecha)
> , Nombre
> FROM Mov2002
> GO
> SELECT * FROM Movimientos WHERE Fecha='2003-07-01'
>
> read-ahead reads 0.
> ==>
Creo que aquí hay un error. La tabla es Movimientos, no Mov2003. El número
de logical reads debería haber sido el triple. Ya que la consulta hace una
exploración completa de la tabla (no usa el índice de la fecha) y la tabla
es tres veces más grande.

> Veras que la consulta es exactamente igual que si usaras cada tabla
por
> separado. La estructura de indices de SQL hace que normalmente sea mas
> eficiente tener una sola tabla a n tablas iguales EXCEPTO cuando cada

una

de
> las tabla va a un disco independiente. En cuyo caso la vista es lo
> recomendable.


No estoy de acuerdo.Creo que has cometido algún error en las pruebas.

Para el caso de la consulta que estamos evaluando,. Será más eficiente (el
número de tablas más eficiente) particionar la tabla cuando el índice

sobre

la Fecha es un índice no agrupado y la consulta devuelve un número de
registros lo suficientemente grande como para que SQL Server determine que
es mejor una exploración de la tabla que usar el índice. La razón es

simple,

SQL Server sólo tendrá que hacer la exploración completa de una de las
tablas, las otras tablas no las mirará ya que hay una restricción CHECK

que

se lo indica.

Si el índice sobre la columna Fecha es agrupado, la diferencia entre
particionar la tabla y no particionarala sería mínima, como mucho, habría
una diferencia de una lectura lógica más con tabla única. Me estoy
refiriendo, por supuesto, a consultas en las que se obtienen todos las
columnas y en la cláusula where está la columna de partición.

> Es posible definir un JOB anual que agregue una tabla y modifique la
> vista actual si se requiere.
>
>
> Javier Loria
> Costa Rica
> Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
> que pueda ser copiado y pegado al Query Analizer.
> La version de SQL y Service Pack tambien ayuda
>
> "Pedro Jose Caceres" wrote in message
> news:
> > > Hola:
> > > El problema de esas tabla es que producen una perdida de

desempeno

> > > importante, asi que considera la posiblidad de unirlas en una sola
> tabla,
> > >
> >
> > Muchas gracias por la respuesta. Pero
> > Ok.. entonces por qué algunos dicen que el tamaño de las tablas es
> > inversamente proporcional al buen desempeño ?
> > A saber, son tablas de varios millones de registros (entre 4 y 5
millones)
> y
> > son de operaciones diarias de uso intensivo y lo de la separacion

viene

> > porque los nuevos registros son casi invariablemente de fecha del año
> > actual, por tanto las demas no se utilizan a menos que sea en reportes

y

> > consultas historicas.
> > Como le dije a Miguel arriba, la razon de partirlas fue basicamente
> buscando
> > mayor eficiencia en el registro diario de operaciones y creo que lei

por

> ahi
> > que esa era una forma de eficientizarlo.
> > La verdad que a veces uno leyendo tantas opiniones distintas no sabe

por

> > donde irse. :)
> >
> > >a
> > > menos que cada una de ellas tenga su propio disco duro o arreglo de
> > discos.
> > > Adicionalmente si no quieres fusionarlas lo mejor seria, usando

la

> > > recomendacion de Miguel, crear una vista del tipo:
> > > > > > > CREATE VIEW Movimientos(Columna1, )
> > > FROM
> > > SELECT Columna1, Columna2 FROM Mov2001
> > > UNION ALL -- *** ALL es muy importante
> > > SELECT Columna1, Columna2 FROM Mov2002
> > > UNION ALL
> > >
> >
> >
> > El problema es que la cerrar cada año, como dije arriba hay que crear
una
> > nueva tabla MovAAAA entonces implicaria modificar la vista ? esto se
> podria
> > hacer en t-sql tambien ?
> >
> >
> > > > > > > Luego podras hacer un SELECT:
> > > > > > > SELECT Columna1, ... FROM Movimientos
> > > WHERE ITEM='xyz' FECHA>='01/04/2001'
> > > > > > > Que sera muy rapido, porque por las estadisticas de SQL el sabra
que
> > > ninguna de las tablas, excepto la de Mov2004 tiene datos que cumplan
la
> > > condicion de las fechas..
> > > Saludos,
> > >
> > >
> > > Javier Loria
> > > Costa Rica
> > > Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
> > > que pueda ser copiado y pegado al Query Analizer.
> > > La version de SQL y Service Pack tambien ayuda
> > >
> > > "Pedro Jose Caceres" wrote in message
> > > news:#
> > > > Tengo una tabla de movimientos para cada año, MOV2003, MOV2004,
etc.
> > > > Cuando se cierra un año por tanto, tengo que crear la nueva tabla
> > > > correspondiente al nuevo año. Puedo hacer el CREATE TABLE en

t-sql

> sin
> > > > tener que sacar los usuarios del sistema ? Como puedo tomar la
> > estructura
> > > de
> > > > otra de las tablas ya existentes para que se cree igual ?
> > > >
> > > > Otra cosa es que para en una consulta ver movimientos que abarque
dos
> o
> > > mas
> > > > años, hago una UNION, preparando dinamicamente la instruccion
SELECT
> > > desde
> > > > la aplicacion y enviandola por odbc al servidor. Por ej. si la

lista

> de
> > > > movimientos abarca va desde el 01/04/2001 al 31/03/2004, estoy
armando
> > > desde
> > > > el front-end la union asi::
> > > >
> > > >
> > > > SELECT columna1, .., from MOV2001 WHERE ITEM='ELITEM'
> > FECHA>
/04/2001
> > > > UNION
> > > > SELECT columna1, .., from MOV2002 WHERE ITEM='ELITEM'
> > > > UNION
> > > > SELECT columna1, .., from MOV2003 WHERE ITEM='ELITEM'
> > > > UNION
> > > > SELECT columna1, .., from MOV2004 WHERE FECHA<1/03/2004
> > > > ORDER BY FECHA
> > > >
> > > >
> > > > Consideran que sea esta la mejor forma para hacer esto ?
> > > >
> > > > De no ser, que otras opciones hay para extraer esta informacion
quizas
> > en
> > > un
> > > > SP del mismo servidor?
> > > >
> > > > Gracias
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Hola Javier,

Mi único error ha sido pensar que te habías equivocado (un error al copiar y
pegar) y no comprobarlo. Lo lamento, anoche estaba muy cansado.

Sin embargo afirmas que particionar una tabla no puede aumentar el
rendimiento y ahí, lo siento mucho, pero estás equivocado. También afirmas
que yo confundo a la gente, y siento decirte esto, pero eres tú quien
confunde a la gente haciendo afirmaciones que no son ciertas.

Aquí tienes el ejemplo que lo demuestra:


= tabla particionada =
Con las tablas particionadas y usando tu script para crear las tablas y
rellenarlas, excepto que se obtienen los movimientos para una semana
completa en vez de un único día.

SELECT * FROM Mov2003 WHERE Fecha BETWEEN '20030701' AND '20030707'

Tabla 'Mov2003'. Número de exploraciones 1, lecturas lógicas 2477, lecturas
físicas 0, lecturas anticipadas 0

SELECT * FROM Movimientos WHERE Fecha BETWEEN '20030701' AND '20030707'

Tabla 'Mov2003'. Número de exploraciones 1, lecturas lógicas 2477, lecturas
físicas 0, lecturas anticipadas 0.
Tabla 'Mov2002'. Número de exploraciones 1, lecturas lógicas 2, lecturas
físicas 0, lecturas anticipadas 0.
Tabla 'Mov2001'. Número de exploraciones 1, lecturas lógicas 2, lecturas
físicas 0, lecturas anticipadas 0.


tabla única ==Para tabla única usando el mismo script para crear la tabla y lo siguiente
para llenarla:

INSERT INTO MOVIMIENTOS
SELECT * FROM MOV2001
UNION ALL
SELECT * FROM MOV2002
UNION ALL
SELECT * FROM MOV2003

La consulta:

SELECT * FROM Movimientos WHERE Fecha BETWEEN '20030701' AND '20030707'

Tabla 'Movimientos'. Número de exploraciones 1, lecturas lógicas 7426,
lecturas físicas 0, lecturas anticipadas 0.
7431

Conclusión: ==
Tienes un aumento del rendimiento del 300% en IOs para tabla particionada.
en esta consulta.

Saludos:

Jesús López
MVP

continuación:

Tienes un aumento del rendimiento del 300% en IOs para tabla
particionada.
en esta consulta.

La explicación de esto es:

Será más eficiente (el número de tablas más eficiente) particionar la tabla
cuando el índice
sobre la Fecha es un índice no agrupado y la consulta devuelve un número de
registros lo suficientemente grande como para que SQL Server determine que
es mejor una exploración de la tabla que usar el índice. La razón es simple,
SQL Server sólo tendrá que hacer la exploración completa de una de las
tablas, las otras tablas no las mirará ya que hay una restricción CHECK
que se lo indica.

¿Lo entiendes ?


Saludos:

Jesús López
MVP

Hola Jesus:
Muchas gracias por la respuesta, has sido muy profesional, exacto y
comprensible. Este tipo de posteos nos permiten intercambiar conocimientos y
en lo personal me sirven para crecer porque me obligan a replantearme mis
"paradigmas". Revisemos ahora tu propuesta:
a) Cuando usamos:
SELECT * FROM Mov2003 WHERE Fecha='20030701' o su equivalente en la
tabla de Movimientos el desempeno es el mismo con o sin tabla particionada.
b) Cuando usamos:
SELECT * FROM Mov2003 WHERE Fecha BETWEEN '20030701' AND '20030707' o su
equivalente en la tabla de movimientos el desempeno es mucho mejor en la
tabla particionada que en la tabla completa (3 veces).
PORQUE?: Muy sencillo en el segundo caso no se esta usado el indice, o
sea en algun punto entre 3/4 dias el servidor decide que es muy lento leer
el indice y que es mejor leer la tabla directamente. Se puede observar esto
con el boton de Estimacion de plan de ejecucion que refleja lo siguiente :
a) Las consulta WHERE Fecha='20030701' o incluso WHERE Fecha BETWEEN
'20030701' AND '20030702' producen en ambas tablas un indice Index-Seek y
luego un bookmark lookup, traducido al "cristiano", se usa el indice Fecha.
b) Rangos mas grandes (4 dias) producen siempre CLUSTERED INDEX SCAN que
basicamente significa que no se usa el indice fecha y se lee directamente la
tabla.
Entonces podemos reexpresar tu propuesta como:
SI NO SE USAN INDICES,
LA TABLA PARTICIONADA,
ES PROPORCIONALMENTE
MAS RAPIDA A LA TABLA COMPLETA
Y si esta es tu propuesta, este punto te lo concedo.

Revisemos ahora mi propuesta:
Mi argumento es que en realidad el particionamiento por las tablas por
ano puede producir perdidas en el desempeno.
a) Es claro, por lo menos para mi, que si usamos indices, la diferencia
entre usar las tabla particionadas y no particionarlas es 0 o despreciable.
Ejemplo si en lugar de usar la Fecha como rango hubieramos usado un rango de
ID's del tipo WHERE ID BETWEEN 364401 AND 366000 los desempenos son casi
identicos entre las tablas sin particionar y particionadas.
b) En algunos casos se requerira unir en el WHERE no solo de Fechas sino
otras columnas. Por ejemplo que pasa si quisieramos construir un "KARDEX" de
producto que requiere una condicion de producto y un ordenamiento por fecha.
Si asumieramos que Nombre es el codigo del producto, y creamos los indices
en todas las tablas (Nombre, Fecha) consultas del tipo:
" WHERE Nombre LIKE '20010701 72404' ORDER BY Fecha"
producen un rendimiento mayor en la tabla completa y un 50% en las
particionadas.
En algunas ocasiones se usaran UNION ALL para combinar los resultados y
si estos son suficientemente grandes van a TempDB, no construi un ejemplo
para esto pero lo he visto en sistemas en produccion.
Es mi opinion: que el "Patron de Particionamiento" tenia sentido en
sistemas de datos asociados con archivos, pero en MS-SQL deberia
considerarse un ANTI-PATRON, ya que hace el mantenimiento de las tablas y el
mantenimiento del codigo, mas dificil, adicionalmente puede producir
problemas de rendimiento y evita el uso los indices, que son precisamente la
forma de mejorar el acceso a las tablas.

Una vez mas muchas gracias por tu respuesta tan profesional.
De ahora en adelante escribire sobre tablas particionadas:
En general, a menos que las tablas residan en discos duros/servidores
independientes, o que no se esten usando indices; el particionamiento de las
tablas no mejora el desempeno y con frecuencia lo reduce.
Saludos,


Javier Loria
Costa Rica
Se aprecia la inclusion de DDL (CREATE, INSERTS, etc.)
que pueda ser copiado y pegado al Query Analizer.
La version de SQL y Service Pack tambien ayuda

"SqlRanger" wrote in message
news:

Hola Javier,

Mi único error ha sido pensar que te habías equivocado (un error al copiar

y

pegar) y no comprobarlo. Lo lamento, anoche estaba muy cansado.

Sin embargo afirmas que particionar una tabla no puede aumentar el
rendimiento y ahí, lo siento mucho, pero estás equivocado. También afirmas
que yo confundo a la gente, y siento decirte esto, pero eres tú quien
confunde a la gente haciendo afirmaciones que no son ciertas.

Aquí tienes el ejemplo que lo demuestra:


= tabla particionada =>
Con las tablas particionadas y usando tu script para crear las tablas y
rellenarlas, excepto que se obtienen los movimientos para una semana
completa en vez de un único día.

SELECT * FROM Mov2003 WHERE Fecha BETWEEN '20030701' AND '20030707'

Tabla 'Mov2003'. Número de exploraciones 1, lecturas lógicas 2477,

lecturas

físicas 0, lecturas anticipadas 0

SELECT * FROM Movimientos WHERE Fecha BETWEEN '20030701' AND '20030707'

Tabla 'Mov2003'. Número de exploraciones 1, lecturas lógicas 2477,

lecturas

físicas 0, lecturas anticipadas 0.
Tabla 'Mov2002'. Número de exploraciones 1, lecturas lógicas 2, lecturas
físicas 0, lecturas anticipadas 0.
Tabla 'Mov2001'. Número de exploraciones 1, lecturas lógicas 2, lecturas
físicas 0, lecturas anticipadas 0.


tabla única ==> Para tabla única usando el mismo script para crear la tabla y lo siguiente
para llenarla:

INSERT INTO MOVIMIENTOS
SELECT * FROM MOV2001
UNION ALL
SELECT * FROM MOV2002
UNION ALL
SELECT * FROM MOV2003

La consulta:

SELECT * FROM Movimientos WHERE Fecha BETWEEN '20030701' AND '20030707'

Tabla 'Movimientos'. Número de exploraciones 1, lecturas lógicas 7426,
lecturas físicas 0, lecturas anticipadas 0.
7431

Conclusión: ==>
Tienes un aumento del rendimiento del 300% en IOs para tabla particionada.
en esta consulta.

Saludos:

Jesús López
MVP