?
Solved

CDaoRecordSet->Requery() not allowed in a transaction ?

Posted on 2004-11-13
5
Medium Priority
?
574 Views
Last Modified: 2013-11-20
i'm using parameterized queries, that's the fastest way i have found when i have to call very often the same query with just a parameter value changing. Here is how i open such a recordset :

if (! pRst->IsOpen())
{
    strSQL.Format("PARAMETERS [Param] Long; SELECT * FROM POINTS WHERE tagPoint = [Param]");
    if (pRst->m_pMyQueryDef)
   {
     pRst->m_pMyQueryDef->Close();
     delete pRst->m_pMyQueryDef;
     pRst->m_pMyQueryDef = NULL;
   }
   pRst->m_pMyQueryDef = new CDaoQueryDef(m_pDatabase);
   pRst->m_pMyQueryDef->Create("", strSQL);
   COleVariant object = param; // value to be searched
   pRst->m_pMyQueryDef->SetParamValue("[Param]", object);
   pRst->Open(pRst->m_pMyQueryDef, dbOpenDynaset);
}
else // recordset already opened : just requery with the new parameter value :
{
  COleVariant object = param; // value to be searched
  pRst->m_pMyQueryDef->SetParamValue("[Param]", object);
  pRst->Requery();
}
pRst->m_bFound = ! pRst->IsEOF();

That works fine and fast.
Now i try to accelerate some part of my code where i have to update some values for a big numbers of records; a SQL UPDATE cannot be used because the values have to computed  by one of my functions for each record. I thought that doing the update batch within a transaction could have a positive effect on speed. Opening the recordset as done above still works, but when the recordset  is already opened, pRst->Requery()  causes this exception :

  DAO.Recordset
  Operation not supported in transactions.
  ErrorCode DAO : 3246

"Operation not supported' : that seems to put a stop on my expectations. But i find it curious that opening the recordset still works : isn't a (re)query implicitly done during this opening ?
So my question is : is there a way to use parameterized queries during transactions ?
0
Comment
Question by:JP_Goblet
  • 3
  • 2
5 Comments
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 12573926
You could try to explicitly close the recordset before the requery  (Not tried it so it may not have any effect)
  pRst->Close();
  pRst->m_pMyQueryDef->SetParamValue("[Param]", object);
  pRst->Requery();

However you state you are in a transaction for performance reasons.  I might be wrong in this but I don't think it would make any difference (and possibly even result in poorer performance).  My understanding of transactions is to provide an 'all or none' mechanism to preserve data integrity.
Roughly if you perform three actions (A then B then C).  Without a transaction if C fails your data has an Action A and B performed upon it but no C - consequences could be catastrophic.  In a transaction if C fails you rollback and your data is as if nothing was performed.
0
 
LVL 45

Accepted Solution

by:
AndyAinscow earned 750 total points
ID: 12581952
I've done some testing here (I hadn't thought of your approach myself before).

For my small test.
This requery is vastly faster than closing/opening the recordset. (15 times faster)
Wrapping the close/open in a transaction improves the performance by about 70%, but that is still lousy compared to the requery.
Wrapping the requery in a transaction showed no change in performance.

Note the final statement.
The following code works.  It is a paramterised query inside a transaction.


void CXyzDlg::PerfomQuery(CDaoDatabase* pDB)
{
      pDB->m_pWorkspace->BeginTrans();

      CDaoRecordset rs(pDB);
      CDaoQueryDef Qdf(pDB);

      Qdf.Create(_T(""), _T("PARAMETERS z Long; SELECT testTbl.* FROM testTbl WHERE (((testTbl.testID)=[z]));"));

      COleVariant var((long)0);
      Qdf.SetParamValue("[z]", var);
 
      rs.Open(&Qdf);

      for(int i = 1; i < 11000; i++)
      {
            var.lVal = i;
            Qdf.SetParamValue("[z]", var);
            rs.Requery();
            rs.Edit();
            rs.SetFieldValue("Beleg ID", var);
            rs.Update();
      }
      rs.Close();
      Qdf.Close();
      pDB->m_pWorkspace->CommitTrans();
}

0
 

Author Comment

by:JP_Goblet
ID: 12582275
I was surprised that your code works as it does the same than mine : open once and then just requery for all records. After some tests i have discovered why my code did not work : my recordset was already opened before the BeginTrans (because, again for performance reasons, i keep the same recordset for all my application, and i reopen it only when necessary, for example if the query filter has changed). Closing the recordset before BeginTrans and then reopening it solves the problem.
But as you said there is no performance increase.

I supposed the transaction would speed things because it writes all the data on disk in one shot; without transaction there are a lot of access to disk (although the windows HD cache is supposed to avoid that ?). That's what i had noticed some time ago when i made tests with ADO.

Your conclusion is right :  a parameterised query inside a transaction can be done but do not increase performance.
0
 

Author Comment

by:JP_Goblet
ID: 12582303
to be more precise, i expected a performance increase not in the querying process, but in the updating of the table
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 12582349
The transaction is still useful for the data integrity for bulk processing.
Interesting, I'll have to remember this approach (parameter requery instead of close/open).
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
Introduction: Dialogs (1) modal - maintaining the database. Continuing from the ninth article about sudoku.   You might have heard of modal and modeless dialogs.  Here with this Sudoku application will we use one of each type: a modal dialog …
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month15 days, 4 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question