Una tabla por cada año y UNION

Generalmente es un mito que se debe a la mala programación. Si tus índices y
estadísticas son adecuados será muy raro que SQL haga un Scan de tu tabla si
no lo necesita. Sin embargo si todo no está fino y SQL tiene que hacer scans
de la tabla a menudo, es evidente que será más rápido si hay menos
registros.

En resumen, el tamaño que tiene que ver con el desempeño (aunque no es del
todo cierto lo que digo) no es el de la tabla sino el del resultado que
esperas (o que sql necesita para obtener el resultado que tu necesitas).



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


"Pedro Jose Caceres" escribió en el mensaje
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:

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 =ÞCLARE @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 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.
== 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.
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
> >
> >
>
>

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

4) Pon una restricción CHECK sobre la fecha para asegurar que es del año
correspondiente

Saludos:

Jesús López
MVP

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'
> 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>