Foros del Web » Bases de Datos » SQL Server »

Tabla con millones de registros

Estas en el tema de Tabla con millones de registros en el foro de SQL Server en Foros del Web. Hola tengo una tabla en SQLSERVER 2000 con mas 20 millones de registros y bastantes campos (columnas). En esta tabla utilizo un campo id autonumerico ...
  #1 (permalink)  
Antiguo 03/06/2008, 01:53
 
Fecha de Ingreso: octubre-2005
Mensajes: 13
Antigüedad: 8 años, 10 meses
Puntos: 0
Tabla con millones de registros

Hola

tengo una tabla en SQLSERVER 2000 con mas 20 millones de registros y bastantes campos (columnas). En esta tabla utilizo un campo id autonumerico y un campo timestamp.

Esta tabla no tiene creado ningún índice, excepto el del campo id que será único.

Estoy cargando datos en la tabla, y aunque tarda un tiempo los datos se guardan bien.

El problema lo tendré cuando haya terminado de cargar todos los datos y empiece a crear indices para mejorar el rendimiento. Al hacer una consulta me tarda dos horas y media en responder.

¿Como haceis para trabajar con tablas con este tamaño?
  #2 (permalink)  
Antiguo 03/06/2008, 12:52
Avatar de iislas
Colaborador
 
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.082
Antigüedad: 7 años, 2 meses
Puntos: 160
Respuesta: Tabla con millones de registros

Crearles indices, ya que de lo contrario, estas recorriendo los 20 millones de registros de forma SECUENCIAL (uno-tras-de-otro)
  #3 (permalink)  
Antiguo 03/06/2008, 12:57
 
Fecha de Ingreso: febrero-2008
Mensajes: 100
Antigüedad: 6 años, 6 meses
Puntos: 0
Exclamación Respuesta: Tabla con millones de registros

Hola, podriais mostrar un ejemplo de una tabla creda con indices, es que encontre ucha teoria pero pocos ejemplos. Saludos y gracias.
  #4 (permalink)  
Antiguo 03/06/2008, 13:15
Avatar de Andres95
Colaborador
 
Fecha de Ingreso: diciembre-2004
Mensajes: 1.802
Antigüedad: 9 años, 9 meses
Puntos: 38
Respuesta: Tabla con millones de registros

Tomado de la ayuda de SQL, "indexes, creating"

Código:
Examples
A. Use a simple index
This example creates an index on the au_id column of the authors table.

SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'au_id_ind')
   DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
   ON authors (au_id)
GO

B. Use a unique clustered index
This example creates an index on the employeeID column of the emp_pay table that enforces uniqueness. This index physically orders the data on disk because the CLUSTERED clause is specified. 

SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'emp_pay')
   DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'employeeID_ind')
   DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
 employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
   VALUES (1, 500, .10)
INSERT emp_pay 
   VALUES (2, 1000, .05)
INSERT emp_pay 
   VALUES (3, 800, .07)
INSERT emp_pay
   VALUES (5, 1500, .03)
INSERT emp_pay
   VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
   ON emp_pay (employeeID)
GO

C. Use a simple composite index
This example creates an index on the orderID and employeeID columns of the order_emp table.

SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'order_emp')
   DROP TABLE order_emp
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'emp_order_ind')
   DROP INDEX order_emp.emp_order_ind
GO
USE pubs
GO
CREATE TABLE order_emp
(
 orderID int IDENTITY(1000, 1),
 employeeID int NOT NULL,
 orderdate datetime NOT NULL DEFAULT GETDATE(),
 orderamount money NOT NULL
)

INSERT order_emp (employeeID, orderdate, orderamount)
   VALUES (5, '4/12/98', 315.19)
INSERT order_emp (employeeID, orderdate, orderamount)
   VALUES (5, '5/30/98', 1929.04)
INSERT order_emp (employeeID, orderdate, orderamount)
   VALUES (1, '1/03/98', 2039.82)
INSERT order_emp (employeeID, orderdate, orderamount)
   VALUES (1, '1/22/98', 445.29)
INSERT order_emp (employeeID, orderdate, orderamount)
   VALUES (4, '4/05/98', 689.39)
INSERT order_emp (employeeID, orderdate, orderamount)
   VALUES (7, '3/21/98', 1598.23)
INSERT order_emp (employeeID, orderdate, orderamount)
   VALUES (7, '3/21/98', 445.77)
INSERT order_emp (employeeID, orderdate, orderamount)
   VALUES (7, '3/22/98', 2178.98)
GO
SET NOCOUNT OFF
CREATE INDEX emp_order_ind
   ON order_emp (orderID, employeeID)

D. Use the FILLFACTOR option
This example uses the FILLFACTOR clause set to 100. A FILLFACTOR of 100 fills every page completely and is useful only when you know that index values in the table will never change.

SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'zip_ind')
   DROP INDEX authors.zip_ind
GO
USE pubs
GO
CREATE NONCLUSTERED INDEX zip_ind
   ON authors (zip)
   WITH FILLFACTOR = 100

E. Use the IGNORE_DUP_KEY
This example creates a unique clustered index on the emp_pay table. If a duplicate key is entered, the INSERT or UPDATE statement is ignored.

SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME = 'emp_pay')
   DROP TABLE emp_pay
GO
USE pubs
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'employeeID_ind')
   DROP INDEX emp_pay.employeeID_ind
GO
USE pubs
GO
CREATE TABLE emp_pay
(
 employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
   VALUES (1, 500, .10)
INSERT emp_pay 
   VALUES (2, 1000, .05)
INSERT emp_pay 
   VALUES (3, 800, .07)
INSERT emp_pay
   VALUES (5, 1500, .03)
INSERT emp_pay
   VALUES (9, 750, .06)
GO
SET NOCOUNT OFF
GO
CREATE UNIQUE CLUSTERED INDEX employeeID_ind
   ON emp_pay(employeeID)
   WITH IGNORE_DUP_KEY

F. Create an index with PAD_INDEX
This example creates an index on the author's identification number in the authors table. Without the PAD_INDEX clause, SQL Server creates leaf pages that are 10 percent full, but the pages above the leaf level are filled almost completely. With PAD_INDEX, the intermediate pages are also 10 percent full.



Note  At least two entries appear on the index pages of unique clustered indexes when PAD_INDEX is not specified.


SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'au_id_ind')
   DROP INDEX authors.au_id_ind
GO
USE pubs
CREATE INDEX au_id_ind
   ON authors (au_id)
   WITH PAD_INDEX, FILLFACTOR = 10

G. Create an index on a view
This example will create a view and an index on that view. Then, two queries are included using the indexed view.

USE Northwind
GO

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF 
GO 
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

--Create view.
CREATE   VIEW V1 
WITH   SCHEMABINDING 
AS 
   SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT 
   FROM   dbo.[Order Details] od, dbo.Orders o 
   WHERE   od.OrderID=o.OrderID 
   GROUP BY   OrderDate, ProductID
GO

--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
GO

--This query will use the above indexed view.
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev, OrderDate, ProductID
FROM   dbo.[Order Details] od, dbo.Orders o
WHERE   od.OrderID=o.OrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34)
   AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC

--This query will use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev
FROM   dbo.[Order Details] od, dbo.Orders o
WHERE   od.OrderID=o.OrderID AND DATEPART(mm,OrderDate)= 3
   AND DATEPART(yy,OrderDate) = 1998
GROUP BY OrderDate
ORDER BY OrderDate ASC
Saludos!
__________________
La sencillez y naturalidad son el supremo y último fin de la cultura...
--
MCTS : SQL Server 2008, .NET Framework 3.5, ASP.NET Applications.
  #5 (permalink)  
Antiguo 03/06/2008, 13:39
 
Fecha de Ingreso: febrero-2008
Mensajes: 100
Antigüedad: 6 años, 6 meses
Puntos: 0
Exclamación Respuesta: Tabla con millones de registros

Hola de nuevo, muchas gracias por el ejemplo, la duda que tengo, es, los indices que tengo que hacerlos, al crear la tabla o después de crearla.

Se que se recomienda no hacer indices de todas las columnas, sólo de aquellas que sus datos sean muy diversos. Pero es que no logro entender exactamente como se haría.

Aqui te dejo la tabla en cuestión a ver si me orientas un poco más.

Código PHP:
CREATE TABLE `medias` (
  `
aidint(11NOT NULL auto_increment,
  `
fechachar(50) default NULL,
  `
horachar(50) default NULL,
  `
placachar(50) default NULL,
  `
modelochar(50) default NULL,
  `
numplacachar(50) default NULL,
  `
potenciadouble default NULL,
  `
media3double default NULL,
  `
media4double default NULL,
  `
tablachar(50) default NULL,
  
PRIMARY KEY  (`aid`)
ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=156 
Gracias por todos y saludos.
  #6 (permalink)  
Antiguo 04/06/2008, 12:12
Avatar de iislas
Colaborador
 
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.082
Antigüedad: 7 años, 2 meses
Puntos: 160
Respuesta: Tabla con millones de registros

A ver, por principio de cuentas, NO PUEDES TENER HIJOS, si aun no tienes relaciones sexuales con alguna mujer/hombre, que este sana(sano) y disponible a tenerlos contigo.

Por tanto, NO puedes crear indices, SI AUN NO ESTAN SUS TABLAS.

Hay una teoria en la creacion de INDICES, que a muchos no les gusta, y dice:

"Todas las columnas, que van despues de un WHERE, son candidatas a ser indices"

Por otro lado, el ANALIZADOR DE CONSULTAS, tienes su propio Wizard (asistente) para creacion de indices.

Saludos.
  #7 (permalink)  
Antiguo 04/06/2008, 14:48
 
Fecha de Ingreso: febrero-2008
Mensajes: 100
Antigüedad: 6 años, 6 meses
Puntos: 0
Exclamación Respuesta: Tabla con millones de registros

Hola, ya me he creado indices en mi taba, siguiendo tus consejos, todos los indices son aquellas columnas que van detras de WHERE:

Código PHP:
CREATE TABLE `medias` ( 
  `
aidint(11NOT NULL auto_increment
  `
fechachar(50) default NULL
  `
horachar(50) default NULL
  `
placachar(50) default NULL
  `
modelochar(50) default NULL
  `
numplacachar(50) default NULL
  `
potenciadouble default NULL
  `
media3double default NULL
  `
media4double default NULL
  `
tablachar(50) default NULL
  
PRIMARY KEY  (`aid`), 
  
KEY `indicemedia3` (`media3`), 
  
KEY `indicemedia4` (`media4`), 
  
KEY `indicepotencia` (`potencia`), 
  
KEY `indicefecha` (`fecha`), 
  
KEY `indicehora` (`hora`) 
ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=156 
Antiguamente hacia las consultas asi:

Código PHP:
$result = @mysql_query("SELECT * FROM medias WHERE (hora <'$alto' AND hora >'$bajo') AND (numplaca='$mono' OR numplaca='$poli' OR numplaca='$cis') ORDER BY hora"); 
Y ahora asi:

Código PHP:
$result = @mysql_query("SELECT * FROM medias FORCE INDEX (indicehora)
WHERE (hora <'$alto' AND hora >'$bajo') AND (numplaca='$mono' OR numplaca='$poli' OR numplaca='$cis') ORDER BY hora"
); 
Y me tarda exactamente lo mismo.

Cual es mi fallo??? SAludos
  #8 (permalink)  
Antiguo 04/06/2008, 15:39
Avatar de iislas
Colaborador
 
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.082
Antigüedad: 7 años, 2 meses
Puntos: 160
Respuesta: Tabla con millones de registros

¿FORCE INDEX?, que yo sepa esto no existe en SQL SERVER.................

Ademas, en SQL Server, es mucho mejor que el tome la decision de que index (camino) tomar......
  #9 (permalink)  
Antiguo 04/06/2008, 17:16
 
Fecha de Ingreso: febrero-2008
Mensajes: 100
Antigüedad: 6 años, 6 meses
Puntos: 0
Respuesta: Tabla con millones de registros

HOla, FORCE lo puse por que lo vi en un manual, y si no lo pongo me da error.

Como me recomiendas que lo haga ....??

Saludos.
  #10 (permalink)  
Antiguo 05/06/2008, 05:31
 
Fecha de Ingreso: octubre-2005
Mensajes: 13
Antigüedad: 8 años, 10 meses
Puntos: 0
Respuesta: Tabla con millones de registros

Hola,

como dije al principio tengo la tabla con millones de registros, y al ejecutar una consulta me tarda 18 minutos en devolverme el resultado. Si le paso el asistente para optimizacion de indices, me dice que no hay ninguna recomendación de indices.

De momento iré problando las distintas consultas que deba hacer hasta que el asistente me diga que debo crear algún indice.

Cada vez que creo un indice, se pasa muchisimo tiempo creandolo y hay veces que no termina de crearlo porque se queda sin espacio en el disco duro.
  #11 (permalink)  
Antiguo 05/06/2008, 05:49
 
Fecha de Ingreso: febrero-2008
Mensajes: 100
Antigüedad: 6 años, 6 meses
Puntos: 0
Respuesta: Tabla con millones de registros

Hola, yo tengo un problema parecido al tuyo, tengo una BD de unos 2 gigas y además ira creciendo.

Un par de post más arriba puse los indices que cree, pero no observo resultado, incluso, no se si ni si quiera están bien.

Otra cosa que he hecho es paginar las consultas, que eso si parece que me acelera un poco el tema.

Para ello use una movida que crearon los de php llamada paginator, aquí te dejo el enlace que lo explican todo:

http://taringa.net/posts/ebooks-tutoriales/1261574/Paginator-versi%C3%B3n-1_6---Paginacion-para-PHP-y-MySQL.html

Suerte y si encuentras otra solución factible, me cuentas.

Saludos.
  #12 (permalink)  
Antiguo 05/06/2008, 10:22
Avatar de iislas
Colaborador
 
Fecha de Ingreso: julio-2007
Ubicación: Mexico, D.F.
Mensajes: 6.082
Antigüedad: 7 años, 2 meses
Puntos: 160
Respuesta: Tabla con millones de registros

dos comentarios

SELECT * FROM MYTABLA con MILLONES de registros, es claro que se va a tardar, debo FILTRAR o PAGINAR los resultados.

SQL Server, maneja ESTADISTICOS DE ACCESO y lleva toda la historia para definir que indice es mejor para ser accesada la tabla, por lo tanto, NO ES NECESARIO INDICAR EL INDICE a ocupar, aunque si lo permite, posiblemente nos de menor tiempo de respuesta.

FROM TABLA (INDEX = myindex)
WHERE COLX = '2' --- y esta columna genero el indice colxindex
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 21:50.
SEO by vBSEO 3.3.2