Codejock Developer Community : Record caching?

Record caching? : I ended up with another solution...

Tue, 25 Jan 2011 11:45:07 +0000

Author: Algae
Subject: 17570
Posted: 25 January 2011 at 11:45am

I ended up with another solution which is pretty fast.

I load the data into CXTPReportRecords collection, then when I need to set it aside, I put that collection into a CMap. From the CMap I can then do a lookup when I need it again. The map is keyed to the database table name.

I suppose you could use an STL map if you prefer.

// set record collection to control.. add this method to cxtpreportcontrol.h (public!)
void SetRecords(CXTPReportRecords* pRecords) { m_pRecords = pRecords; }

// View class stuff

// record collection map in .h file
CMap recordMap;

// accessor for record collection
// if it returns false, use your loader to get the data straight from the database.
BOOL GetMappedRecords(CString db)
{
     CXTPReportRecords* pRecords;

     if (recordMap.Lookup(db,pRecords))
     {
        GetReportCtrl().SetRecords(pRecords);
        return TRUE;
    }
    return FALSE;
}

// in your view code somewhere
// Set data to map for subsequent retrieval.
// this is only necessary the first time a new table is encountered and you need to save // off the existing records.

CXTPReportRecords* pRecords = new CXTPReportRecords();
pRecords = GetReportCtrl().GetRecords();
// save old table data to the map for later lookup
recordMap.SetAt(oldDB, pRecords);
// get existing "new" records
if (!GetMappedRecords(newDB))
{
     .. if not in the map, use your loader to get records here
}
... make sure to populate (slow!)

// tear down when done, typically the view destructor
POSITION pos = recordMap.GetStartPosition();
while( pos != NULL )
{
    CXTPReportRecords* pRecords;
    CString str;

    recordMap.GetNextAssoc( pos, str, pRecords );

// report control will cleanup the current data collection on it's own so we skip that one
    if (GetReportCtrl().GetRecords() != pRecords)
    {
        pRecords->RemoveAll();
        delete pRecords;
    }
}
recordMap.RemoveAll();

*Note: In a multi-user environment you have to track record changes and update the records as usual.

*This gives up memory for speed.

Record caching? : Back to square 1 with caching....

Thu, 23 Dec 2010 12:20:30 +0000

Author: Algae
Subject: 17570
Posted: 23 December 2010 at 12:20pm

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.

Record caching? : thx vb6?

Fri, 17 Dec 2010 15:00:38 +0000

Author: Source
Subject: 17570
Posted: 17 December 2010 at 3:00pm

thx

vb6?

Record caching? : While I can't give out the...

Fri, 17 Dec 2010 12:38:16 +0000

Author: Algae
Subject: 17570
Posted: 17 December 2010 at 12:38pm

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

Record caching? : Can you post an example? It will...

Fri, 17 Dec 2010 05:33:05 +0000

Author: Source
Subject: 17570
Posted: 17 December 2010 at 5:33am

Can you post an example? It will be good for all!

Record caching? : There may be better ways to do...

Thu, 16 Dec 2010 13:10:56 +0000

Author: Algae
Subject: 17570
Posted: 16 December 2010 at 1:10pm

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.

Record caching? : That is why I did not renew my...

Thu, 18 Nov 2010 05:04:06 +0000

Author: Source
Subject: 17570
Posted: 18 November 2010 at 5:04am

That is why I did not renew my subscription...

- Virtual mode does not have the same functionality has "normal mode";
- Grid control does not exists;
- RC databinding is very, very... slow with large recordsets;
- Flatedit, date and text controls are ages away from the componentone activex controls designed so many years ago (they are so simple to use and implement) - ex: decimals behavour in a simple control.

... probably I will by it again :)

Record caching? : That is the problem using Virtual...

Thu, 18 Nov 2010 04:58:13 +0000

Author: Source
Subject: 17570
Posted: 18 November 2010 at 4:58am

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!

Record caching? : Anyone know the best way to cache...

Wed, 17 Nov 2010 20:08:11 +0000

Author: Algae
Subject: 17570
Posted: 17 November 2010 at 8:08pm

Anyone know the best way to cache report records in memory so they can be read back into a report control at a later time? Or is it better to write them to disk? Or is either just a bad idea?

I'd like to use virtual mode but it seems you give up a lot of flexibility doing so (sorting, grouping etc).

Thanks in advance to anyone who has a suggestion.