Record caching?

That is the problem using Virtual Mode... you lose some of the good stuff.

Working with large recordsets is fine but you do not have this things.
Working in "normal mode" with the same large recordsets is too slow but you have the good stuff.

The best of the 2 worlds - not!

There may be better ways to do this, but no one is saying so I constructed a fairly simple system using CMemFile and serializing the records as a CArchive using property exchange for the records. Its a lot faster than dredging up the records from SQL again and again. Closest thing I've got to virtual records without clobbering the nice sort, group, etc options.

While I can't give out the entire project, the archive construct is fairly simple. Cache is sort of a misnomer because it's not really acting like a true cache.

Note that if you have enormous amount of records it's going to eat memory proportionally.

// .h file stuff

    // oldDB is string to name the old records file
    // newDB is string to name the new records file
    // aSQL is query cmd string SELECT * FROM etc

    void UpdateRecords(CString oldDB, CString newDB, CString aSQL);
    CArray <CMemFile*,CMemFile*> cache_files;
    void _cache(CMemFile *pFile, BOOL bLoad = FALSE);


// .cpp stuff
// Cache archive function

void CReportView::_cache(CMemFile *pFile, BOOL bLoad)
{
    CArchive archive(pFile, bLoad);
    CXTPPropExchangeArchive px(archive);
    GetReportCtrl().GetRecords()->DoPropExchange(&px);
    archive.Close();
    pFile->SeekToBegin();
}

// when program changes database tables do this.
//
// for performance testing a timer is included in debug build
//
// CDataHandler is an instance of your database engine. Change it to whatever you use.

void CReportView::UpdateRecords(CString oldDB, CString newDB, CString aSQL)
{
    DWORD nCount = GetReportCtrl().GetRecords()->GetCount();
       
    CDataHandler dh;

#ifdef _DEBUG
    DWORD dwTime0 = ::GetTickCount();
    DWORD dwTime1 = 0;
#endif

    // if no records, we assume that it's never been loaded
    // so load from database

    if (nCount == 0)
    {
// using your database engine, send the query and put results in the report control
        dh.AddRecords(aSQL, GetReportCtrl());
#ifdef _DEBUG
        dwTime1 = ::GetTickCount();
        TRACE(_T("Load New %s, Operation %.3f sec\n"),newDB, (dwTime1 - dwTime0) / 1000.0);
#endif
        return;
    }
   
// if we never change tables, this never happens!

    CMemFile *pCache = NULL;
    BOOL bFound = FALSE;

    // find cache file and save old db records

    for (int i = 0; i < cache_files.GetCount(); i++)
    {
        pCache = cache_files.GetAt(i);

        if (pCache->GetFilePath() == oldDB)
        {
            bFound = TRUE;
            break;
        }
    }

// make a new memfile if we need one

    if (!bFound)
    {
        pCache = new CMemFile();
        pCache->SetFilePath(oldDB);
        cache_files.Add(pCache);
    }

// clobber the existing memfile and store current data to it.

    if (pCache != NULL)
    {
        pCache->SetLength(0);
        _cache(pCache,CArchive::store);
    }

    GetReportCtrl().ResetContent(FALSE);

    bFound = FALSE;

    // find cache file and load it up with a batch of records

    for (int i = 0; i < cache_files.GetCount(); i++)
    {
        pCache = cache_files.GetAt(i);

        if (pCache->GetFilePath() == newDB)
        {
            bFound = TRUE;
            break;
        }
    }

    // no cache found, we have to go to the database again.
    if (!bFound)
    {
        dh.AddRecords(aSQL, GetReportCtrl());
#ifdef _DEBUG
        dwTime1 = ::GetTickCount();
        TRACE(_T("Load New %s, Operation %.3f sec\n"),newDB, (dwTime1 - dwTime0) / 1000.0);
#endif
        return;
    }
    _cache(pCache,CArchive::load);

#ifdef _DEBUG
    dwTime1 = ::GetTickCount();
    TRACE(_T("Load from Cache %s, Operation %.3f sec\n"),newDB, (dwTime1 - dwTime0) / 1000.0);
#endif

    // get modified or new records from everyone else
    // deleted records handled by storing operation
    // set time stamp for update

    CString sTime = m_lastTime.Format();
    aSQL.AppendFormat(_T(" WHERE Created > '%s' OR Modified > '%s'"), sTime, sTime);
    dh.RefreshRecords(aSQL,GetReportCtrl());
}

Back to square 1 with caching.

The property exchange code is too slow to be effective for large record sets. Performance degraded significantly with large sets.

Don't bother with this method.