CRecordSet Updating: Efficiency Question 
Author Message
 CRecordSet Updating: Efficiency Question

Hello,

I have a few "general" questions on efficiency. Please only reply to this
post if you have certain knowledge/experience of what I'm referring to.

General Situation:

- I must query a certain table in my SQL Server 7 Database for a primary
key, given a parameter.
- If the record exists, I use this primary key in another table to add a
record to that other table.
-If the record DOESN'T exist, then I add a new record to the table, requery
the table for the record (to obtain the primary key) and then add a record
to the "other" table
-All CRecordSet derived classes use the "dynaset" option

If you have any suggestions for a faster (memory *won't* be a problem on the
target machine) implementation than the one below then please feel free to
reply to this post:

Thanks in advance:

Now Some code (note that I have several helper functions that do the "try
catch" logic):

void CDBUpdaterThread::UpdateAgentContact(CRFAgentResponse *pResponse)
{
 CRSAAgent *pRSAgent = NULL; //Agent Table for querying ...CRecordSet
Derived
 CRSAAgentContact *pRSAgentContact = NULL; //table to update ...CRecordSet
Derived

 SYSTEMTIME sysTime;

 if (!m_bDatabaseOpened)
  return;

 pRSAgent = new CRSAAgent(&m_database);
 ASSERT(pRSAgent);

//query according to IP address of the "agent"
 pRSAgent->m_strFilter = _T("IPAddress = '") + pResponse->GetIPAddress() +
_T("'");

 RecordSetTryOpen(pRSAgent); //according to the filter
 if (!pRSAgent)
  return;

 if(pRSAgent->IsBOF()) //the Record Set is empty (we need to add this Agent
information)
 {
  if (RecordSetTryAddNew(pRSAgent)) //CRecordSet::AddNew() call with try
catch blocks
  {
   pRSAgent->m_IPAddress = pResponse->GetIPAddress();
   pRSAgent->m_MasterID = 1; //hard coded for now
   if (RecordSetTryUpdate(pRSAgent))
    RecordSetTryRequery(pRSAgent); //so we get correct data into our record
set
  }
 }

 while (!pRSAgent->IsEOF())
 {
    pRSAgent->MoveNext( ); //should only be one record here, either the one
we just added, or existing
 }

 if (pRSAgent->m_AgentID) //primary key exists, therefore we can update the
Agent Contact table
 {
  pRSAgentContact = new CRSAAgentContact;
  ASSERT(pRSAgentContact);

  if (RecordSetTryOpen(pRSAgentContact))
   RecordSetTryAddNew(pRSAgentContact);

  if (pRSAgentContact)
  {
   ::GetSystemTime(&sysTime);
   pRSAgentContact->m_AgentID = pRSAgent->m_AgentID;
   pRSAgentContact->m_ContactTime = CTime(sysTime); //will want GMT time ;)
  }
  RecordSetTryUpdate(pRSAgentContact);
 }

 //clean up possible memory leaks
 if (pRSAgentContact)
  delete pRSAgentContact;
 if (pRSAgent)
  delete pRSAgent;

Quote:
}/*==========================================================*/



Tue, 22 Jul 2003 09:24:58 GMT  
 CRecordSet Updating: Efficiency Question
The most efficient way is to let SQL Server handle all these. ie. you write
a stored proc on your SQL Server database and call it from your VC client.

- Chu


Quote:
> Hello,

> I have a few "general" questions on efficiency. Please only reply to this
> post if you have certain knowledge/experience of what I'm referring to.

> General Situation:

> - I must query a certain table in my SQL Server 7 Database for a primary
> key, given a parameter.
> - If the record exists, I use this primary key in another table to add a
> record to that other table.
> -If the record DOESN'T exist, then I add a new record to the table,
requery
> the table for the record (to obtain the primary key) and then add a record
> to the "other" table
> -All CRecordSet derived classes use the "dynaset" option

> If you have any suggestions for a faster (memory *won't* be a problem on
the
> target machine) implementation than the one below then please feel free to
> reply to this post:

> Thanks in advance:

> Now Some code (note that I have several helper functions that do the "try
> catch" logic):

> void CDBUpdaterThread::UpdateAgentContact(CRFAgentResponse *pResponse)
> {
>  CRSAAgent *pRSAgent = NULL; //Agent Table for querying ...CRecordSet
> Derived
>  CRSAAgentContact *pRSAgentContact = NULL; //table to update ...CRecordSet
> Derived

>  SYSTEMTIME sysTime;

>  if (!m_bDatabaseOpened)
>   return;

>  pRSAgent = new CRSAAgent(&m_database);
>  ASSERT(pRSAgent);

> //query according to IP address of the "agent"
>  pRSAgent->m_strFilter = _T("IPAddress = '") + pResponse->GetIPAddress() +
> _T("'");

>  RecordSetTryOpen(pRSAgent); //according to the filter
>  if (!pRSAgent)
>   return;

>  if(pRSAgent->IsBOF()) //the Record Set is empty (we need to add this
Agent
> information)
>  {
>   if (RecordSetTryAddNew(pRSAgent)) //CRecordSet::AddNew() call with try
> catch blocks
>   {
>    pRSAgent->m_IPAddress = pResponse->GetIPAddress();
>    pRSAgent->m_MasterID = 1; //hard coded for now
>    if (RecordSetTryUpdate(pRSAgent))
>     RecordSetTryRequery(pRSAgent); //so we get correct data into our
record
> set
>   }
>  }

>  while (!pRSAgent->IsEOF())
>  {
>     pRSAgent->MoveNext( ); //should only be one record here, either the
one
> we just added, or existing
>  }

>  if (pRSAgent->m_AgentID) //primary key exists, therefore we can update
the
> Agent Contact table
>  {
>   pRSAgentContact = new CRSAAgentContact;
>   ASSERT(pRSAgentContact);

>   if (RecordSetTryOpen(pRSAgentContact))
>    RecordSetTryAddNew(pRSAgentContact);

>   if (pRSAgentContact)
>   {
>    ::GetSystemTime(&sysTime);
>    pRSAgentContact->m_AgentID = pRSAgent->m_AgentID;
>    pRSAgentContact->m_ContactTime = CTime(sysTime); //will want GMT time
;)
>   }
>   RecordSetTryUpdate(pRSAgentContact);
>  }

>  //clean up possible memory leaks
>  if (pRSAgentContact)
>   delete pRSAgentContact;
>  if (pRSAgent)
>   delete pRSAgent;

> }/*==========================================================*/



Tue, 22 Jul 2003 12:57:24 GMT  
 CRecordSet Updating: Efficiency Question
Yikes!
I can see that leaving the work to SQL Server makes sense... where can I
look for good documentation on doing so?
Will I have to create some special tables in the database to hold the stored
procedures? Where should I start in researching stored procedures?

Thanks.


Quote:
> The most efficient way is to let SQL Server handle all these. ie. you
write
> a stored proc on your SQL Server database and call it from your VC client.

> - Chu



> > Hello,

> > I have a few "general" questions on efficiency. Please only reply to
this
> > post if you have certain knowledge/experience of what I'm referring to.

> > General Situation:

> > - I must query a certain table in my SQL Server 7 Database for a primary
> > key, given a parameter.
> > - If the record exists, I use this primary key in another table to add a
> > record to that other table.
> > -If the record DOESN'T exist, then I add a new record to the table,
> requery
> > the table for the record (to obtain the primary key) and then add a
record
> > to the "other" table
> > -All CRecordSet derived classes use the "dynaset" option

> > If you have any suggestions for a faster (memory *won't* be a problem on
> the
> > target machine) implementation than the one below then please feel free
to
> > reply to this post:

> > Thanks in advance:

> > Now Some code (note that I have several helper functions that do the
"try
> > catch" logic):

> > void CDBUpdaterThread::UpdateAgentContact(CRFAgentResponse *pResponse)
> > {
> >  CRSAAgent *pRSAgent = NULL; //Agent Table for querying ...CRecordSet
> > Derived
> >  CRSAAgentContact *pRSAgentContact = NULL; //table to update
...CRecordSet
> > Derived

> >  SYSTEMTIME sysTime;

> >  if (!m_bDatabaseOpened)
> >   return;

> >  pRSAgent = new CRSAAgent(&m_database);
> >  ASSERT(pRSAgent);

> > //query according to IP address of the "agent"
> >  pRSAgent->m_strFilter = _T("IPAddress = '") + pResponse->GetIPAddress()
+
> > _T("'");

> >  RecordSetTryOpen(pRSAgent); //according to the filter
> >  if (!pRSAgent)
> >   return;

> >  if(pRSAgent->IsBOF()) //the Record Set is empty (we need to add this
> Agent
> > information)
> >  {
> >   if (RecordSetTryAddNew(pRSAgent)) //CRecordSet::AddNew() call with try
> > catch blocks
> >   {
> >    pRSAgent->m_IPAddress = pResponse->GetIPAddress();
> >    pRSAgent->m_MasterID = 1; //hard coded for now
> >    if (RecordSetTryUpdate(pRSAgent))
> >     RecordSetTryRequery(pRSAgent); //so we get correct data into our
> record
> > set
> >   }
> >  }

> >  while (!pRSAgent->IsEOF())
> >  {
> >     pRSAgent->MoveNext( ); //should only be one record here, either the
> one
> > we just added, or existing
> >  }

> >  if (pRSAgent->m_AgentID) //primary key exists, therefore we can update
> the
> > Agent Contact table
> >  {
> >   pRSAgentContact = new CRSAAgentContact;
> >   ASSERT(pRSAgentContact);

> >   if (RecordSetTryOpen(pRSAgentContact))
> >    RecordSetTryAddNew(pRSAgentContact);

> >   if (pRSAgentContact)
> >   {
> >    ::GetSystemTime(&sysTime);
> >    pRSAgentContact->m_AgentID = pRSAgent->m_AgentID;
> >    pRSAgentContact->m_ContactTime = CTime(sysTime); //will want GMT time
> ;)
> >   }
> >   RecordSetTryUpdate(pRSAgentContact);
> >  }

> >  //clean up possible memory leaks
> >  if (pRSAgentContact)
> >   delete pRSAgentContact;
> >  if (pRSAgent)
> >   delete pRSAgent;

> > }/*==========================================================*/



Wed, 23 Jul 2003 01:07:24 GMT  
 CRecordSet Updating: Efficiency Question
Hi Chris:

Sorry but I don't quite follow your question.. are you asking how to write a
stored procedure?

You don't need to create any special tables to hold stored procedures - they
are not tables. If you have SQL Server handy, check its Books Online for
help.

If  you need examples calling stored procedures in VC, you can read VC's
help (on Data Access) or search http://msdn.microsoft.com.

HTH.
- Chu


Quote:
> Yikes!
> I can see that leaving the work to SQL Server makes sense... where can I
> look for good documentation on doing so?
> Will I have to create some special tables in the database to hold the
stored
> procedures? Where should I start in researching stored procedures?

> Thanks.



> > The most efficient way is to let SQL Server handle all these. ie. you
> write
> > a stored proc on your SQL Server database and call it from your VC
client.

> > - Chu



> > > Hello,

> > > I have a few "general" questions on efficiency. Please only reply to
> this
> > > post if you have certain knowledge/experience of what I'm referring
to.

> > > General Situation:

> > > - I must query a certain table in my SQL Server 7 Database for a
primary
> > > key, given a parameter.
> > > - If the record exists, I use this primary key in another table to add
a
> > > record to that other table.
> > > -If the record DOESN'T exist, then I add a new record to the table,
> > requery
> > > the table for the record (to obtain the primary key) and then add a
> record
> > > to the "other" table
> > > -All CRecordSet derived classes use the "dynaset" option

> > > If you have any suggestions for a faster (memory *won't* be a problem
on
> > the
> > > target machine) implementation than the one below then please feel
free
> to
> > > reply to this post:

> > > Thanks in advance:

> > > Now Some code (note that I have several helper functions that do the
> "try
> > > catch" logic):

> > > void CDBUpdaterThread::UpdateAgentContact(CRFAgentResponse *pResponse)
> > > {
> > >  CRSAAgent *pRSAgent = NULL; //Agent Table for querying ...CRecordSet
> > > Derived
> > >  CRSAAgentContact *pRSAgentContact = NULL; //table to update
> ...CRecordSet
> > > Derived

> > >  SYSTEMTIME sysTime;

> > >  if (!m_bDatabaseOpened)
> > >   return;

> > >  pRSAgent = new CRSAAgent(&m_database);
> > >  ASSERT(pRSAgent);

> > > //query according to IP address of the "agent"
> > >  pRSAgent->m_strFilter = _T("IPAddress = '") +

pResponse->GetIPAddress()

- Show quoted text -

Quote:
> +
> > > _T("'");

> > >  RecordSetTryOpen(pRSAgent); //according to the filter
> > >  if (!pRSAgent)
> > >   return;

> > >  if(pRSAgent->IsBOF()) //the Record Set is empty (we need to add this
> > Agent
> > > information)
> > >  {
> > >   if (RecordSetTryAddNew(pRSAgent)) //CRecordSet::AddNew() call with
try
> > > catch blocks
> > >   {
> > >    pRSAgent->m_IPAddress = pResponse->GetIPAddress();
> > >    pRSAgent->m_MasterID = 1; //hard coded for now
> > >    if (RecordSetTryUpdate(pRSAgent))
> > >     RecordSetTryRequery(pRSAgent); //so we get correct data into our
> > record
> > > set
> > >   }
> > >  }

> > >  while (!pRSAgent->IsEOF())
> > >  {
> > >     pRSAgent->MoveNext( ); //should only be one record here, either
the
> > one
> > > we just added, or existing
> > >  }

> > >  if (pRSAgent->m_AgentID) //primary key exists, therefore we can
update
> > the
> > > Agent Contact table
> > >  {
> > >   pRSAgentContact = new CRSAAgentContact;
> > >   ASSERT(pRSAgentContact);

> > >   if (RecordSetTryOpen(pRSAgentContact))
> > >    RecordSetTryAddNew(pRSAgentContact);

> > >   if (pRSAgentContact)
> > >   {
> > >    ::GetSystemTime(&sysTime);
> > >    pRSAgentContact->m_AgentID = pRSAgent->m_AgentID;
> > >    pRSAgentContact->m_ContactTime = CTime(sysTime); //will want GMT
time
> > ;)
> > >   }
> > >   RecordSetTryUpdate(pRSAgentContact);
> > >  }

> > >  //clean up possible memory leaks
> > >  if (pRSAgentContact)
> > >   delete pRSAgentContact;
> > >  if (pRSAgent)
> > >   delete pRSAgent;

> > > }/*==========================================================*/



Wed, 23 Jul 2003 12:09:38 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. CRecordset.Update() question???

2. updating multiple CRecordsets (newbie question)

3. Answer to Update CRecordset and my question about DialogBox

4. CRecordset->Update(), But no Update

5. CRecordset::Update() fails while updating long values

6. Jake's Code Efficiency Challenge Updated

7. Updated Scores for Jake's Efficiency Challenge

8. Multithreading - efficiency question

9. efficiency question involving arrays

10. Efficiency question

11. Efficiency Question

12. Efficiency Question.

 

 
Powered by phpBB® Forum Software