Para GURU's (bases de datos)

sercornejov · #1 30-11-2004

Hola.

Desarrolle una aplicación que controla el ingreso de personal en las entidades privadas y públicas. la base de datos está en MSAccess.

el problema está en que se toma una foto del visitante para control y como son muchos (50.000 en un año) pues el archivo de la foto no lo guardo en la base de datos sino en el disco y punteo la ruta en un campo de la tabla.

La cosa es que ya van 50000 archivos de fotos (3-5 Kb) y el proceso comienza a ser lento. La BD solo tiene 20 Mb

No se como mas hacerlo para que sea más rápido.

Que me recomiendan? (base de datos nueva? Cual?)

Saludos desde Colombia

Sinaloense · #2 30-11-2004

Necesitas ser más específico con el equipo (Hardware) en el cual esta la base de datos.

roman · #3 30-11-2004

Yo pienso que esto no debería ser lento así tengas 1000000 de registros, a menos, claro, que cada vez que consultes la tabla mandes llamar a todos los registros. De ser así tendrás que establecer criterios de búsqueda para limitar la petición de registros a sólo unos cuantos.

// Saludos

sercornejov · #4 30-11-2004

bueno:

El equipo mas bajo en el que corre es un Pentium III de 800 Mhz, 128 RAM y disco duro de 40 Gb a 5400 rpm.

en la tabla de los visitantes se guardan 8 campos, tres varchar(50), uno autonumerico, dos de fecha, dos mas tipo entero largo. en uno de los varchar se guarda la ruta de la foto. e.j. "C:\archivos de programa\argos\data\foto\91272235.jpg"

La base de datos tiene en estos momentos 48760 usuarios diferentes (48761 archivos de fotos, pues hay uno que se llama 'nofoto.jpg')

La tabla de visitas tiene 124567 visitas, aunque esta tabla, aparte de una fecha, solo guarda índices para referenciar.

La aplicación, cuando se entra la cédula del visitante, hace un LOCATE en la tabla de visitantes, si el locate funciona, busca el archivo de la foto (la ruta del cual está en la tabla) y si el archivo esxiste se lo asigna a un TImage, sino le asigna el archivo 'nofoto.jpg'.

Los archivos solo tienen entre 3 y 5 K

Entre otras, la pregunta omejor el consejo que quiero es si es mejor seguir manejandolo así como lo tengo, o mejor usar otra base de datos y guardar las fotos en la BD como blob o algo parecido.

El problema se agranda cuando tiene que trabajar en red, pues ahí si que es lento...

He pensado en bases como MySQL, Interbase 6.0 o 7.0 (este último es una opción remota por aquello de la licencia) o Firebird 1.5.

Los equipos nuevos (para dos puntos más que vendí) son: Pentium IV de 2.4 Mh, 256 RAM, D.D. 80 Gb a 7200 rpm

Gracias por contestar...

roman · #5 30-11-2004

Como te dije, no creo que deba alentarse. No sé qué componentes uses para conectarte a Access (¿ADO?) pero más que un LOCATE yo haría una consulta SQL del estilo

Código SQL [-]select visitas where campo_llave = llave de la visita

con lo que sólo mandas traer un registro en caso de que exista. Esto te sirve tanto para insertar una nueva visita o editar una ya existente.

// Saludos

marcoszorrilla · #6 30-11-2004

Cita:

La base de datos tiene en estos momentos 48760 usuarios diferentes (48761 archivos de fotos, pues hay uno que se llama 'nofoto.jpg')

El número de Registros es pequeño para que vaya lenta, supongo que alguna vez la habrás compactado, con lo cual mejora bastante el rendimiento.

Por otra parte, 48761 Registros en una carpeta ahí igual puede estar el problema de la lentitud, lo que tarda quizás es en encontrar la foto, yo haría una prueba con 1.000 fotos solamente, si mejora muchísimo el rendimiento, entonces pondría un campo autoincremento sino lo tiene la tabla, y crearía 10 ó más carpeta si fueran necesarias, repartiendo las fotos en estas, de acuerdo al número ID de la tabla.
\fotos - Sería la carpeta genérica y luego con un simple If contra el ID, el camino quedaría \fotos\5000 - \fotos\10000.......

Es decir el famoso método divide y vencerás.

Si haces la prueba y te da resultados, nos comunicas.

Un Saludo.

Sinaloense · #7 30-11-2004

El equipo mas bajo en el que corre es un Pentium III de 800 Mhz, 128 RAM y disco duro de 40 Gb a 5400 rpm

128 de memoria es muy bajo para trabajar con base de datos con imagenes, teniendo en cuenta que Windows usa al menos 92 y el motor de base de datos access al menos 32, asi que solo te queda la memoria virtual del disco duro la cual no es muy eficiente en un de 5400 rpm.

sercornejov · #8 30-11-2004

gracias a todos.

me suena harto lo de dividir los archivos en varios directorios, pueden ser, inicialmente, 10 (0,1,2,3,4,5,6,7,8,9) de inicio de cédula.

bueno, creo que según lo que dicen, la idea de guardar la foto en la bese de datos debe ser descartada...

Gracias de nuevo.

roman · #9 30-11-2004

Desconozco con exactitud la forma en que estás accediendo a tus tablas pero hace un rato me creé una base con 74000 registros (iban a ser 50000 pero por error puse 500000 y tuve que cortar por lo sano

) cada uno con su respectiva ruta a la foto. Obvio que no me conseguí 74000 fotos sino que utilicé la misma, eso sí 74000 copias (debo recordar borrarlas) y los accesos son muy, muy aceptables.

La conexión a Access la hice con ADO tanto con un ADOQuery para búsqueda e inserción, como con un ADOTable.

¡Ah! Y todas las fotos en el mismo directorio.

// Saludos

sercornejov · #10 01-12-2004

Bueno.

Será la máquina?
Será la manera de acceder a los datos desde el aplicativo?
Yo tambien uso los ADO para la conección, en mi equipo (Pentium IV de 2.8, 512 MB ram ddr 400 Mhz y D.D. de 80 Gb SATA) todo es muy bueno tambien...
La idea es poder hacer algo que funcione mejor...

Gracias...

roman · #11 01-12-2004

Cita:

Empezado por sercornejov

Será la máquina?
Será la manera de acceder a los datos desde el aplicativo?
Yo tambien uso los ADO para la conección, en mi equipo (Pentium IV de 2.8, 512 MB ram ddr 400 Mhz y D.D. de 80 Gb SATA) todo es muy bueno tambien...

Este equipo debiera bastar. Recordemos que estamos hablando de una tabla que almacena las rutas de las imágenes y no las imágenes mismas.

Confirmas que usas ADO pero no das detalles. Quizá sirva si nos pones un pequeño ejemplo de uso: para insertar un registro, para buscar un registro, etc., indicando las componentes exactas que usas (TADOataSet, TAdoCommand, TAdoTable, TAdoQuery, etc.) y los parámetros de conexión. Cosas como el tipo de cursor, etc.

// Saludos

sercornejov · #12 01-12-2004

Los componentes que uso:

para conectar: TADOConnection
Tablas: TADOTable
Queries: TADOQuery

cuando se entra una cedula en un TEdit (normal), al salir (con TAB para pasar al Tedit del nombre del personaje) está este evento:

Código:

procedure TfrmEntrada.edtIDExit(Sender: TObject);
var
  ID:string;
  iti:integer;
  total:double;
  semana,hoy:TDateTime;

begin
  // para saber si el query que trae la historia del personaje está activado

  if not dmppal1.adoqryHisVisTot.Active then
		dmppal1.adoqryHisVisTot.Active:=true;
  // toma la cedula del personaje
  ID:=edtID.Text;
  // inicializa la tabla de visitantes al primer record
  dmppal1.adotblVisitas.First;
  // los visitantes actuales se guardan en una tabla que se llama ADENTRO
  // si la nueva cedula está adentro, no permite una segunda entrada
  // esto es porque la gente se le olvida entregar el sticker a la salida
  nohasalido:=dmppal1.adotblAdentro.Locate('VIS_CC',id,[loCaseInsensitive]);
  if nohasalido then
    begin
      showmessage('El visitante con CC '+dmppal1.adotblAdentroVIS_CC.Value+
      	' no ha salido aún de la Gobernación.  Entro el '+
        dmppal1.adotblAdentroVTA_IN.AsString);
      // oculta los label que muestran el historial del personaje
      lblTotTiempo1.Visible:=false;
      lblTotTiempo2.Visible:=false;
      lblTotSemana1.Visible:=false;
      lblTotSemana2.Visible:=false;
      lblTotHoy1.Visible:=false;
      lblTotHoy2.Visible:=false;
      // deja en blanco el edit de cedula para una nueva entrada
      edtID.Text:='';
      edtVisNombre.Text:='';
      imgVisFoto.Picture.Bitmap:=nil;// el Timage de la foto lo deja nulo
      dbgVisitasEnt.Visible:=False;
      bbtnRegistrar.Enabled:=False;// inhabilita el registro
      sbtnCambiarFotoVis.Visible:=False;
      sbtnCancelVisFoto.Visible:=False;
      dbmmoVisNota.Visible:=false;
      sbtnOKFoto.Visible:=False;
    end;
  // si existe coloca el cursor en el registro del personaje
  existe:=dmppal1.adotblVisitante.Locate('VIS_CC',id,[loCaseInsensitive]);
  if existe then
  	begin
    	if dmppal1.adotblVisitanteVIS_ESTADO.Value=0 then // si esta prohibida la entrada del personaje:
      	begin
          edtID.Font.Color:=clwhite;
          edtID.Font.Style:=[fsBold];
          edtID.Color:=clRed;
          edtVisNombre.Font.Color:=clwhite;
          edtVisNombre.Font.Style:=[fsBold];
          edtVisNombre.Color:=clRed;
          dbmmoVisNota.Visible:=true;
          dbmmoVisNota.Font.Color:=clwhite;
          dbmmoVisNota.Font.Style:=[fsBold];
          dbmmoVisNota.Color:=clRed;
          bbtnRegistrar.Enabled:=false;
        end
        else // si sí puede entrar:
        	begin
            edtID.Font.Color:=clWindowText;
            edtID.Font.Style:=[];
            edtID.Color:=clWindow;
            edtVisNombre.Font.Color:=clWindowText;
            edtVisNombre.Font.Style:=[];
            edtVisNombre.Color:=clWindow;
            dbmmoVisNota.Visible:=true;
            dbmmoVisNota.Font.Color:=clWindowText;
            dbmmoVisNota.Font.Style:=[];
            dbmmoVisNota.Color:=clWindow;
            bbtnRegistrar.Enabled:=true;
          end;
      edtVisNombre.Text:=dmppal1.adotblVisitanteVIS_NOMBRE.Value;
      if FileExists(rutafotos+id+'.jpg') then  // si el archivo existe en el directorio de fotos:
      	begin
      		imgVisFoto.Picture.LoadFromFile(rutafotos+id+'.jpg');
          fotosel:=true;
        end
        else  // si no, coloca un archivo de nofoto.jpg
        	begin 
          	imgVisFoto.Picture.LoadFromFile(rutafotos+'nofoto.jpg');
            fotosel:=false;
          end;
      // se habilitan los controles para cambiar la foto y otros...
      sbtnCambiarFotoVis.Visible:=true;
      sbtnCancelVisFoto.Visible:=true;
      dmppal1.adoqryHisVisTot.Active:=false; // para saber la historia del peronaje
      dmppal1.adoqryHisVisTot.Parameters.Items[0].value:=ID;
      dmppal1.adoqryHisVisTot.Active:=true;
      lblTotTiempo1.Visible:=true;
      lblTotTiempo2.Visible:=true;
      lblTotSemana1.Visible:=true;
      lblTotSemana2.Visible:=true;
      lblTotHoy1.Visible:=true;
      lblTotHoy2.Visible:=true;
      total:=0;
      for iti:=1 to dmppal1.adoqryHisVisTot.RecordCount do
      	begin
        	total:=total+SecondsBetween(dmppal1.adoqryHisVisTotVTA_OUT.Value,dmppal1.adoqryHisVisTotVTA_IN.Value);
          dmppal1.adoqryHisVisTot.Next;
        end;
			lblTotTiempo2.Caption:=FloatToStrF(total/3600,ffGeneral,2,2)+ 'horas';
      semana:=incday(now,-7);
      dmppal1.adoqryHisVisSem.Active:=false;
      dmppal1.adoqryHisVisSem.Parameters.Items[0].Value:=ID;
      dmppal1.adoqryHisVisSem.Parameters.Items[1].Value:=semana;
      dmppal1.adoqryHisVisSem.Active:=true;
      total:=0;
      for iti:=1 to dmppal1.adoqryHisVisSem.RecordCount do
        begin
      		total:=total+SecondsBetween(dmppal1.adoqryHisVisSemVTA_OUT.Value,dmppal1.adoqryHisVisSemVTA_IN.Value);
          dmppal1.adoqryHisVisSem.Next;
      	end;
			lblTotSemana2.Caption:=FloatToStrF(total/3600,ffGeneral,2,2)+ 'horas';
      hoy:=today;
      dmppal1.adoqryHisVisHoy.Active:=false;
      dmppal1.adoqryHisVisHoy.Parameters.Items[0].Value:=ID;
      dmppal1.adoqryHisVisHoy.Parameters.Items[1].Value:=hoy;
      dmppal1.adoqryHisVisHoy.Active:=true;
      total:=0;
      for iti:=1 to dmppal1.adoqryHisVisHoy.RecordCount do
        begin
      		total:=total+SecondsBetween(dmppal1.adoqryHisVisHoyVTA_OUT.Value,dmppal1.adoqryHisVisHoyVTA_IN.Value);
          dmppal1.adoqryHisVisHoy.Next;
      	end;
			lblTotHoy2.Caption:=FloatToStrF(total/3600,ffGeneral,2,2)+ 'horas';
      dbgVisitasEnt.Visible:=true;
		  bbtnRegistrar.Enabled:=true;
    end
    else
    	begin
        edtVisNombre.SetFocus;
        sbtnOKFoto.Visible:=true;
        sbtnCancelVisFoto.Visible:=true;
        bbtnRegistrar.Enabled:=true;
      end;
end;

en el boton de REGISTRAR, que es el que llena las tablas:

Código:

procedure TfrmEntrada.bbtnRegistrarClick(Sender: TObject);
var
	hoy:TDateTime;

begin
	if edtID.Text='' then
    begin
      showmessage('No ha Introducido la cédula');
      exit;
    end;
  if not fotosel then
    begin
      showmessage('No ha tomado la foto');
      exit;
    end;
  if not areasel then
    begin
      showmessage('No ha seleccionado un AREA para el visitante');
      exit;
    end;
	if not empleadosel then
    begin
      showmessage('No ha seleccionado un EMPLEADO para el visitante');
      exit;
    end;
  if not existe then // esta variable es global. si existe es FALSE inserta un registro en visitantes
		begin
      with dmppal1 do
        begin
          hoy:=now;
          // rutina de visitante
          adotblVisitante.Insert;
          adotblVisitanteVIS_CC.Value:=edtID.Text;
          adotblVisitanteVIS_NOMBRE.Value:=edtVisNombre.Text;
          adotblVisitanteVIS_FOTO.Value:=rutafotos+edtID.Text+'.jpg';
          adotblVisitanteVIS_ULTVIS.Value:=hoy;
          adotblVisitanteVIS_ESTADO.Value:=1;
          adotblVisitanteUSU_USUARIO.Value:=adotblUsuarioUSU_USUARIO.Value;
          adotblVisitante.Post;
        end;
    end
    else // si existe es TRUE, solo actualiza la fecha de la última visita
      with dmppal1 do
        begin
          hoy:=now;
          // rutina de visitante
          adotblVisitante.edit;
          adotblVisitanteVIS_ULTVIS.Value:=hoy;
          adotblVisitanteUSU_USUARIO.Value:=adotblUsuarioUSU_USUARIO.Value;
          adotblVisitante.Post;
        end;
  with dmppal1 do // aqui se llena la tabla de VISITAS
  	begin
      // rutina de visita
      adotblVisitas.Insert;
      adotblVisitasVTA_IN.Value:=hoy;
      adotblVisitasVTA_OUT.Value:=hoy;
      adotblVisitasARE_ID.Value:=adoqryAreasEmplARE_ID.Value;
      adotblVisitasEMP_ID.Value:=adoqryAreasEmplEMP_ID.Value;
      adotblVisitasVIS_ID.Value:=adotblVisitanteVIS_ID.Value;
      adotblVisitasUSU_USUARIO.Value:=adotblUsuarioUSU_USUARIO.Value;
      adotblVisitas.Post;
      dmppal1.adotblAdentro.insert; // aqui se llena la tabla de ADENTRO
      dmppal1.adotblAdentroVTA_ID.Value:=dmppal1.adotblVisitasVTA_ID.Value;
      dmppal1.adotblAdentroVTA_IN.Value:=dmppal1.adotblVisitasVTA_IN.Value;
      dmppal1.adotblAdentroVTA_OUT.Value:=dmppal1.adotblVisitasVTA_OUT.Value;
      dmppal1.adotblAdentroVIS_CC.Value:=dmppal1.adotblVisitaslk_VisCC.Value;
      dmppal1.adotblAdentroVIS_NOMBRE.Value:=dmppal1.adotblVisitaslk_Visitante.Value;
      dmppal1.adotblAdentroARE_NOMBRE.Value:=dmppal1.adotblVisitaslk_Area.Value;
      dmppal1.adotblAdentroEMP_NOMBRE.Value:=dmppal1.adotblVisitaslk_Empleado.Value;
      dmppal1.adotblAdentroUSU_USUARIO.Value:=dmppal1.adotblVisitasUSU_USUARIO.Value;
      dmppal1.adotblAdentro.Post;
    end;
    // imprime
    rfrmStikerPeq:=TrfrmStikerPeq.create(self);
    rfrmStikerPeq.qrlblEntrada.Caption:=DateTimetoStr(dmppal1.adotblVisitasVTA_IN.Value);
    rfrmStikerPeq.qrlblVisitante.Caption:=dmppal1.adotblVisitaslk_Visitante.Value;
    rfrmStikerPeq.qrlblAreaVis.Caption:=dmppal1.adotblVisitaslk_Area.Value;
    rfrmStikerPeq.qreanVisCC.BarCode:=dmppal1.adotblVisitaslk_VisCC.Value;
    rfrmStikerPeq.qrStickerPeq.Print;
    rfrmStikerPeq.Close;
    rfrmStikerPeq.Free;
    // Limpiar el formulario
    lblTotTiempo1.Visible:=false;
    lblTotTiempo2.Visible:=false;
    lblTotSemana1.Visible:=false;
    lblTotSemana2.Visible:=false;
    lblTotHoy1.Visible:=false;
    lblTotHoy2.Visible:=false;
    edtID.Text:='';
    edtVisNombre.Text:='';
    imgVisFoto.Picture.Bitmap:=nil;
    dbgVisitasEnt.Visible:=False;
    bbtnRegistrar.Enabled:=False;
    sbtnCambiarFotoVis.Visible:=False;
    sbtnCancelVisFoto.Visible:=False;
    sbtnOKFoto.Visible:=False;
    dbgrdAreEntrada.SelectedRows.Clear;
    dbgrdEmplEntrada.SelectedRows.Clear;
    edtID.Font.Color:=clWindowText;
    edtID.Font.Style:=[];
    edtID.Color:=clWindow;
    edtVisNombre.Font.Color:=clWindowText;
    edtVisNombre.Font.Style:=[];
    edtVisNombre.Color:=clWindow;
    dbmmoVisNota.Font.Color:=clWindowText;
    dbmmoVisNota.Font.Style:=[];
    dbmmoVisNota.Color:=clWindow;
    dbmmoVisNota.Visible:=false;
    areasel:=false;
    empleadosel:=false;
    dmppal1.adotblAdentro.First;
    edtID.SetFocus;
end;

bueno. Pues se que se abusa de los queries para la historia del personaje...
Cuando los tengo en red, la búsqueda del archivo de fotos si es realmente lenta...

Es muy descabellado incluir la foto dentro de la base de datos???

Saludos desde Colombia...

Sergio

roman · #13 01-12-2004

¡Uf!

Ahora mismo no puedo revisar todo pero vuelvo a preguntarte (ahora más específico):

¿Qué valor tienes en la propiedad CursorLocation del ADOTable y del ADOQuery?

El valor por defecto es clUseClient cuya descripción es:

Cita:

Empezado por ayuda de Delphi

A client-side cursor offers more flexibility. All data is retrieved to the local machine and then operated on there, allowing operations not normally supported by servers like sorting and resorting the data and additional filtering. SQL statements are executed at the server, so for statements that restrict the result set with a WHERE clause, only the reduced result set is retrieved to a local cursor.

Es decir, todos los datos viajan al cliente. Con 50000 registros esto puede ser un suicidio. Si éste es el valor que tienes entonces cámbialo por clUseServer y prueba a ver si cambia el rendimiento.

// Saludos

sercornejov · #14 01-12-2004

estan como tu dices en clUseClient. voy a probar con clUseServer

Hice una prueba de 20000 fotos en una IB 7.0 y el archivo queda en 63 GB

El problema es en mayor parte de la cantidad de archivos sueltos en el disco, pero no se como manejar una base de datos (80000 registros en aprox dos años) que pese 200 gb!!!!!

[__hector] · #15 02-12-2004

Si la divides en directorios como se te sugirio mas arriba, es casi seguro que el rendimiento de la aplicacion aumentara. Esa cantidad de registros no es tanta como para que el asunto vaya lento, pero debes probar cada cosa.

Yo te recomendaria: comentar el codigo que presenta la imagen y probar (y asi descartar que la lentitud venga de los queries de la bbdd), colocar en la carpeta 1/5 parte de las imagenes y probar, y por ultimo colocarlas todas con una estructura logica de arbol de directorios y probar cada escenario.

Creo que con el ultimo te ira bien. Esa ha sido mi experiencia.

sercornejov · #16 02-12-2004

Gracias. Probaré....