Hi,

It's really poor!!! Each call to CSRUniversalReferenceList::GetUniversalReference makes a query on database. Same thing concerning CSRInstrument::GetClientReference. I had to make a dump into a file of the instruments in position, of course, I'm using a memory cache using the ITQMemoryCache framework.

The query to get the informations on the universal reference definitions - without taking the allotments and currencies into account - and to store it in a cache is the following one:

select r.reference,d.ref_ident from references r, extrnl_references_definition d where r.model='Other reference' and r.data=d.ref_name

Thus, the returned ref_ident is used with the sicovam of the instrument on another cache loaded by using this query:

select sophis_ident,ref_ident,value from extrnl_references_instruments where sophis_ident in(select sicovam from histomvts where opcvm in(select ident from folio where level>=0 connect by mgr=prior ident start with ident=<the_root_folio_id>))

And the C++ code that is currently used looks like:

 static SSReference _dumpReference[] = {
     {"ISIN","ISIN",__OFFSET_OF(SSInstrument,m_isin) },
     {"BLOOMBERG","BLOOMBERG",__OFFSET_OF(SSInstrument,m_bloomberg) },
     {"REUTERS","REUTERS",__OFFSET_OF(SSInstrument,m_reuters) },
     {"TICKER","TICKER",__OFFSET_OF(SSInstrument,m_ticker) },
     ....
     {"SECURITYDES","SECURITYDES",__OFFSET_OF(SSInstrument,m_securityDES) } };

 int _referenceCount = sizeof(_dumpReference) / sizeof(SSReference);
 for(int i=0;i<_referenceCount;i++)
 {
  // using the cache on the extern references...
  if(m_accReferenceDefinitions)
   m_accReferenceDefinitions->SetValue(_dumpReference[i].m_universal);

  m_valueAccessor.SetRowId(-1); // reset the result of the precedent search
  m_valueAccessor.SetType(itq::memorycache::IITQAccessor::evtNull); // let the cache input the type
  if(m_accReferenceDefinitions && m_cacheReferences && m_cacheReferenceDefinitions->GetItem(m_indexReferenceDefinitions,m_keyReferenceDefinitions,&m_valueAccessor))
  {
   long _referenceId = (*(long*)m_valueAccessor.GetValue());
   // true, we can get using the other memory cache
   m_accReferences1->SetValue(&_sicovam);
   m_accReferences2->SetValue(&_referenceId);

   m_valueAccessor.SetRowId(-1); // reset the result of the precedent search
   m_valueAccessor.SetType(itq::memorycache::IITQAccessor::evtNull); // let the cache input the type
   if(m_cacheReferences->GetItem(m_indexReferences,m_keyReferences,&m_valueAccessor))
   {
    char _value[21]; // 20 car. max in database of the value of extern ref (extrnl_references_instruments)
    int _size = sizeof(_value);
    if(m_valueAccessor.GetStringValue(_value,&_size)) // a right code should increase the value...
     (*(_STL::string*)((char*)(&_record)+_dumpReference[i].m_offset)) = _value;
    else // or just prevent that we have to change the code because Sophis made some chgt  in db
    {
     ITQ_LOG(Log::error,"Unable to retrieve the value, buffer too small");
    }
   }

Rgds,
Philippe