Sqlite taking too long to fetch pictures

Question

I have a few large DB file (each around 50GB) which contain Images of costumer documents (each pic size around 300KB).

I'm trying to load the pictures into my viewer but it's taking too long, like 1min. Specially for the first query/record.

string query = "select pic from tbl_pictures where record_id = '" + SearchID + "'";

            SQLiteConnection con = new SQLiteConnection(conString);
            SQLiteCommand cmd = new SQLiteCommand(query, con);
            con.Open();
            try
            {
                IDataReader rdr = cmd.ExecuteReader();
                try
                {
                    ListOfImagesBytes.Clear();
                    while (rdr.Read())
                    {
                        byte[] a = (System.Byte[])rdr[0];
                        ListOfImagesBytes.Add((System.Byte[])rdr[0]);


                    }
                }
                catch (Exception exc) { MessageBox.Show(exc.Message); }
            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
            con.Close();

And here is the properties of DB files when I created them:

PRAGMA auto_vacuum = 1;
PRAGMA main.page_size = 4096;
PRAGMA main.locking_mode=NORMAL;
PRAGMA main.synchronous=NORMAL;
PRAGMA main.journal_mode=WAL;
PRAGMA main.cache_size=5000;
PRAGMA main.temp_store=Memory;

You should keep in mind SQLite is file based, so most probably the first query takes longer because a lot of stuff is loaded in memory... — dutzu, Jul 24 '13 at 13:43
Is there a reason why you're not storing the images on disk and referencing their relative path from the DB? But in fairness sqlite should be able to handle blobs. — Ian Quigley, Jul 24 '13 at 13:43
I don't know enough for the full answer, but to mitigate the slowness of the first query, you can run a query in the background at start-up. It won't do anything, but by the time your code tries to bring up the first image of a customer document, it won't be doing it for the first time, and should run as fast as your other queries. — Shaz, Jul 24 '13 at 13:44
Try to use Image object, as you can see in http://stackoverflow.com/questions/330346/c-sharp-read-a-jpeg-from-file-and-store-as-an-image and http://stackoverflow.com/questions/891617/how-to-read-a-image-by-idatareader. — Mihai8, Jul 24 '13 at 13:44

score 3 · Accepted Answer · edited Jul 24 '13 at 16:05

3

You may just be accessing a record deep inside the table with zero indexes to help. Maybe add an index?

CREATE INDEX record_id_index ON tbl_pictures(record_id);

edited Jul 24 '13 at 16:05

CL.

173,858
17
217
259

answered Jul 24 '13 at 13:43

wilso132

829
5
10

Can you please explain how this works? Is it a 1 time command or I should run it every week/month? I thought just creating a field called `ID` as Integer AUTO Increment, will do the index job... – xperator Jul 25 '13 at 11:03
1

An index only needs to be created once. In most versions of SQL you can rebuild the index if it has performance issues (i.e. you're deleting a lot of records... think of it like a fragemented hard drive). – wilso132 Jul 30 '13 at 12:59
1

I believe this is the command to reindex whenever needed: REINDEX database_name.record_id_index; See more options here: http://www.tutorialspoint.com/sqlite/sqlite_syntax.htm – wilso132 Jul 30 '13 at 13:04

score 0 · Answer 2 · answered Jul 24 '13 at 13:44

A page size on the database of 8k or 16k may be better - the SQLite guys have a page about BLOBs and performance at http://www.sqlite.org/intern-v-extern-blob.html ; however it seems to show that files up to 100kB are okay. Much larger than 100kB and you take a performance hit.

Sqlite taking too long to fetch pictures

2 Answers2