Big databases

[Robby Stephenson]

Hi Leo,

On Saturday 03 March 2007, Leopold Palomo Avellaneda wrote:
> I'm trying to make some king of collection with all the files from some
> cds that I have of backups, collections of several files, etc. The idea
> is to have some kind of register of all the files that I have. When I
> need I file, search in the database, show me the volume (a cd/dvd) and
> the path and then, taking it.

Right, sounds like a typical use case for most disk catalog programs. I just 
came across one called CdFly http://cdfly.sf.net that looks pretty nice. If 
Tellico doesn't work well enough for you, you might want to give it a try.

> The import function works very well, not as one year ago. The problem is
> that tellico (or sqllite ...) has problems managing >40K registers. I
> have to admit that I don't have a box cutting the edge, it's just a amd64
> 3200 with 1Gb of RAM running a debian amd64 distro. :-)

Right, you should really try to catch up with the times and get a modern 
box. :) I can't say that I've worked with a collection of 40 000 entries. 
Do they all have thumbnails associated with them?

> This then makes me think in one reflexion about this problem. Probably,
> although the migration to a sql server with improve it, there's some
> problem in the manage of the database in the tellico structure. I think
> that tellico has to minimize the access to the data, because in a large
> database is not practical. To manage a little collection will not be a
> problem because I think that the database is in memory, but it's better
> to have a screen in a minimal information about the whole database and
> the complete of one register and all the other makings queries to the
> database (sqllite or server). I think that is not practical have a
> complete list of all the register because then the manage of the database
> became impractical eating all the resources of the box.

I agree with all that, I think. And to be clear, I have no experience 
whatsoever with writing a SQL database application. I'll probably make 
every rookie mistake in the book about inefficiency. But hopefully, I'll 
figure out how to improve things as it goes along. Right now, I'll be happy 
just when I can get it to work! :)

And the database won't be in memory. SQLite has an option for that, I think, 
but I don't plan to use it. As much as I can, I'm going to try to keep a 
minimal amount of data cached, while at the same time, keeping enough that 
all the calls to the db don't make everything too slow.

Robby