SQLite DB open time really long Problem

Question

I am using sqlite in c++ windows, And I have a db size about 60M, When I open the sqlite db, It takes about 13 second.

sqlite3* mpDB;
nRet = sqlite3_open16(szFile, &mpDB); 

And if I closed my application and reopen it again. It takse only less then 1 second.

First, I thought It is because of disk cache. So I preload the 60M db file before sqlite open, and read the file using CFile, However, after preloading, the first time is still very slow.

    BOOL CQFilePro::PreLoad(const CString& strPath)
    {
        boost::shared_array<BYTE> temp = boost::shared_array<BYTE>(new BYTE[PRE_LOAD_BUFFER_LENGTH]);
        int nReadLength;
        try
        {
            CFile file;
            if (file.Open(strPath, CFile::modeRead) == FALSE)
            {
                return FALSE;
            }
            do 
            {
                nReadLength = file.Read(temp.get(), PRE_LOAD_BUFFER_LENGTH);
            } while (nReadLength == PRE_LOAD_BUFFER_LENGTH);
        file.Close();
        }
        catch(...)
        {

        }
        return TRUE;
         }

My question is what is the difference between first open and second open. How can I accelerate the sqlite open-process.

Answer 1

Actually, I don't imagine it would be a caching issue. I'm pretty certain SQLite doesn't load the entire database into memory when you open it - it just gets some relatively small amount of on-disk structures.

One possibility, however, is if it has not been compiled with the SQLITE_OMIT_AUTOINIT preprocessor define. In that case, a call to sqlite3_open16 will result in a call to sqlite3_initialize().

Quite a bit happens within that function though I'm unsure as to how much time it will take. The sqlite3_initialize() function maintains a flag indicating it's been called before and then, on subsequent calls, it will exit (almost) immediately. That's why I mention it as a possible culprit in the difference between first and subsequent opens.

I'd suggest changing your code from:

sqlite3* mpDB;
nRet = sqlite3_open16(szFile, &mpDB);

to:

sqlite3* mpDB;
nRet = sqlite3_initialize();
if (nRet == SQLITE_OK)
    nRet = sqlite3_open16(szFile, &mpDB);

and timing the two function calls independently. It may be that it's the initialisation taking up the time.