Is there any time estimation about sqlite3's open speed?

Question

I am using SQLite3 in C++, I found the opening time of sqlite seems unstable at the first time (by that I mean the time to open windows and open the db at the first time). It takes a long time on 50M db, about 10s in windows, and vary on different times.

Has any one met the same problem?

I am writing a desktop application in windows, so the opening speed is really important for me. Thanks in advance

void OpenDB(const CString& strPath)
{
    int nRet;

#if defined(_UNICODE) || defined(UNICODE)

    nRet = sqlite3_open16(szFile, &mpDB); // not tested under window 98 

#else // For Ansi Version
    //****-  Added by Begemot  szFile must be in unicode- 23/03/06 11:04 - ****
    OSVERSIONINFOEX osvi;
    ZeroMemory(&osvi, sizeof(OSVERSIONINFOEX));
    osvi.dwOSVersionInfoSize = sizeof(OSVERSIONINFOEX);
    GetVersionEx((OSVERSIONINFO *) &osvi);

    if ( osvi.dwMajorVersion == 5) 
    {
        WCHAR pMultiByteStr[MAX_PATH+1];
        MultiByteToWideChar( CP_ACP, 0, szFile,
                _tcslen(szFile)+1, pMultiByteStr,   
                sizeof(pMultiByteStr)/sizeof(pMultiByteStr[0]) );
        nRet = sqlite3_open16(pMultiByteStr, &mpDB);
    }
    else
        nRet = sqlite3_open(szFile,&mpDB);
#endif
    //*************************
    if (nRet != SQLITE_OK)
    {
        LPCTSTR szError = (LPCTSTR) _sqlite3_errmsg(mpDB);
        throw CppSQLite3Exception(nRet, (LPTSTR)szError, DONT_DELETE_MSG);
    }
    setBusyTimeout(mnBusyTimeoutMs);
}

At first, I thought is was a cache problem, I thought it was becase of the random access of sqlite for my indexes on the disk which cause such a long time at the first start(restart windows). So I added the following code before it:

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;
}

CString strDBPath = _T("XXXX");
preload(strDBPath);
Opendb(strDBPath);

however this code makes no difference.

Answer 1

Since your question is "Has any one met the same problem?" my short answer is "No, I don't".

But to be more specific, and as Snake suggested, if you think that the loading time of your database is to high and that this could somehow disturb user experience, you should setup some notification box, indicating that the loading is in progress.

Most users won't even consider that annoying as long as they have information about what is going on.

Update:

Your last comment states that it takes much less time to open it the second time (and supposedly the third as well). This could have something to do with your hard-drive caching system. There is sometimes mechanisms that fasten the loading of the frequently used files by keeping them in some fast-access memory instead of reading them from the disk (which is slow).

Answer 2

SQLite stores data in a paged tree. Simply opening the connection and not executing any queries should not trigger any (substantial) loading of data.

Perhaps something else is going on. You could use Process Monitor to see if there are any other processes (i.e. antivirus?) that are accessing your database file when you call open.