SQLite optimization

[minthaka]

I'm basically finishing a media cataloging program written in Gambas. It is something like GWhere, but it has some useful additions. I hope soon I'll be able to share it with you. Everything works fine, but I'm not satisfied with the querry speed. I'm using sqlite database with 5 columns, And the search works just fine when I want to find a particular file. BUT, when I try to list the content of a DVD, it takes quite a lot to do it. For example if I have a DVD with 5000 files on it it takes 2-3 minutes to list 5*5000=cca. 25000 records. Is it normal?
I've tried to use an additional column id and PRIMARY KEY, but I don't see the difference.
Can you recommend me a strategy for storing huge amount of records?

[Cabhan]

Do you have any indexes at all?

Basically, suppose we have a table called files, you might make a call like this:

SELECT * FROM files WHERE dvd_name = "Dr. Horrible's Sing-Along Blog"

What this will do is find every row where dvd_name has the given value. However, it will be damned inefficient unless you have an index on dvd_name. This is because the database will have to look at every row to see if it matches. If you had an index, the database would just look in the sorted index to find all entries with this name, and return them much faster.

This is still a bit naive. Better still would be:

SELECT * FROM dvds JOIN files ON dvds.dvd_id = files.dvd_id WHERE dvds.name = "Dr. Horrible's Sing-Along Blog"

This implies a foreign key relationship, where each entry in the files table has a foreign key into the dvds table. Now we look in the (much smaller) dvds table for the given name (probably still a good idea to index this), and then, now that we know the id, use an index on the dvd_id column in the files table to quickly look up the files.

The advantage to this process is that we're not duplicating information.

I don't know how good you are with databases or what your schema is. But as a general rule, you should always have an index on columns that you will be using in a WHERE clause.

Hope this helps. If you need more help, give us some more info.

[minthaka]

Thank you Cabhan!
I will consider it. I did some changes in the algorithm: and speeded up at least 50 the querry, or the process of the display. I must share this: Before my first post I used to add rows to the GridView paralelly as the results arrived. That was a mistake. Now I'm collecting results into arrays, and from arrays I create GridView. Just a few words about the present sqlite structure (maybe would be changed): It has one table for each CD/DVD, a table containig the names of the CD/DVD's and numbers of files/folders, and one as a program - wide info. The querry is fast enough for single files and wildcards, and now for the individual ROMs too. I only have to solve the problem of the listing the entire database. Anyway, I'm starting a new project on sourceforge.net today. I intend to use the name GamCat = Gambas+Cataloger. Any tester will be welcomed. Of course, it's free to use and to change.
The reason I've started it is simple:
- The Gwhere project is dead since two years at least.
- I could not learn the way they started it. (language etc.)
- I missed some practical things: Abilities to export the results of querries, filtering the results.
- I don't need some features Gwhere has: e.g. Categories.
I will continue to develop it, when I have time.

[minthaka]

O.K. Cabhan, I need your help:
How to create a table with indexes? I've tried to follow the documentations for sqlite, but always ran ito constraint errors.
Some example I've tried:

CREATE TABLE some(id INTEGER PRIMARY KEY AUTOINCREMENT, FName TEXT, FSize REAL,FPath TEXT,FChanged TEXT);

How to insert values for id?

How to use FName for Primary Key ?

Anyway, I've tested Gambas' performance, and got following result: For a volume of 9700 files it took 37 seconds to display the result (listing the volume), of which 3 second for sqlite to retrieve the result, another 2 seconds to put them into arrays, and around 32 seconds to fill the GridView! It's poor isn't?

Thanks!

[Cabhan]

So I don't know anything about Gambas or GridView, so I can't say anything on that matter.

As for AUTOINCREMENT fields, at least with MySQL, you just give it NULL for that column, and it automatically does the autoincrement magic.

For indexes, check this link:
SQLite Query Language: CREATE INDEX

You can only have one primary key for table, so if you wanted to use FName, you would have to not use id.