Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

how to make a fast parameterized query in DAO ?

I use the DAO classes for Access databases; until recently i used 'static binding', it means that for each recordset associated with a table of the database, a class derived from CDaoRecodset must be created, with members variables corresponding to the fields of the table. The DoFieldExchange function transmits data between members variables & fields.
The visual studio class wizard takes care of creating theses classes, but as i have tens of tables in my database, the result is tens of classes, it's not very neat.

I prefer now to use 'dynamic binding' : all my recordsets are standard CDaoRecodset and i use GetFieldValue / SetFieldValue to read/write in the tables. The code is so more 'transparent', and also it's easily portable to ADO if one day Microsoft makes a ADO driver that performs well for Access databases (stevbe, if you read this, you're welcome on board).

Now there is something that i can't get with dynamic binding, it's fast parameterized queries. In my application, i have to frequently search for records given by their 'tag' in a table POINTS. With static binding, i had simply to fill the m_strFilter (for ex. "TagPoint = param"), init the value of the parameter (myRecordset->m_param = ...), and then call myRecordset->Requery(). It's very fast.

solution 1
-----------
With dynamic binding, i tried 1st simply to open the recordset without any filter and then search the records with FindFirst("TagPoint = ...") :

                           if (! pRst->IsOpen())
            {
                  strSQL.Format("SELECT * FROM POINTS");
                  pRst->Open(dbOpenDynaset, strSQL, dbReadOnly);
            }
            strSQL.Format("TagPoint = %d", Tag);
            pRst->FindFirst(strSQL);

it works but its almost twice slower than what i get with static binding.

solution 2
-----------
I tried then to use the m_strFilter member of CDaoRecodset this way :

            if (! pRst->IsOpen())
            {
                  pRst->m_strSQL = "POINTS";
                  pRst->m_nParams = 1;
                  pRst->m_strFilter.Format("TagPoint = TagParam");
                  pRst->Open(dbOpenDynaset, pRst->m_strSQL, dbReadOnly);
            }
            else
            {
                  pRst->SetParamValue("TagParam", tag);
                  pRst->Requery();
            }

It does not work because pRst->Open() causes an "ASSERT(m_nFields > 0)" in Daocore.cpp (source file from MFC).
CDaoRecodset-->m_nFields is the number of members variables associated with fields in the table, but in my case it's null (because i don't use DoFieldExchange) -->it seems that the m_strFilter mechanism only works with static binding.

solution 3
-----------
Finally , after stepping in the daocore code, i tried to emulate what it does, which is a SQL parameterized query stored in a CQueryDef:

                                COleVariant object = tag; // tag to be searched
                                if (! pRst->IsOpen())
            {
                  strSQL.Format("PARAMETERS [TagParam] Long; SELECT * FROM POINT WHERE TagPoint = [TagParam]");
                  pRst->m_pQueryDef = new CDaoQueryDef(m_pDatabase);
                  pRst->m_pQueryDef->Create("", strSQL);
                  pRst->m_pQueryDef->SetParamValue("[TagEnt]", object);
                  pRst->Open(pRst->m_pQueryDef, dbOpenDynaset, dbReadOnly);
            }
            else
            {
                  pRst->m_pQueryDef->SetParamValue("[TagParam]", object);
                  pRst->Requery();
            }

It works, but its as slow as the 1st solution.
0
JP_Goblet
Asked:
JP_Goblet
  • 7
  • 7
1 Solution
 
AlexFMCommented:
Assuming that TagPoint is Numeric, you can do this:

strSQL.Format("SELECT * FROM POINTS WHERE TagPoint = %d", n);   // n is desired TagPoint value
pRst->Open(dbOpenDynaset, strSQL, dbReadOnly);

If TagPoint is string:

strSQL.Format("SELECT * FROM POINTS WHERE TagPoint = '%s'", s);   // s is desired TagPoint value
pRst->Open(dbOpenDynaset, strSQL, dbReadOnly);

Using of PARAMETERS is good for running SQL queries kept in the Access database (like stored procedures in SQL server). I think execution speed depends on table indexes. If there is index by TagPoint, this code should perform well.
0
 
AndyAinscowCommented:
You may achieve better performance with dbOpenSnapshot (assuming you don't require changes made by other users to be visible whilst the recordset exists).

You can save the query in the access database and use CDaoQueryDef to open a recordset based on the querydef rather than constructing it yourself.

You could also just build an SQL query - you almost have it -

          if (pRst->IsOpen())
pRst->Close();
          {
               strSQL.Format("SELECT * FROM POINTS WHERE (TagPoint =%ld)", object);   <---- I assume object is a long variable
               pRst->Open(dbOpenDynaset, strSQL, dbReadOnly);
          }
0
 
JP_GobletAuthor Commented:
I was sceptic about your code, as it reopens the recordset for each search, but i tested it : sorry to say is the slowest of all i have tried... (but not by much).

And yes there is a index on the field on which i do the search.
0
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!

 
JP_GobletAuthor Commented:
My comment above (01.40PM) was adressed to AlexFM, but in the meantime AndyAinscow has sent a comment.
I tried dbOpenSnapshot : no differences.
Save the query in the database : i think it will gain very little, because in my code (solution 3) the creation of the parameterized query is done only once, at the 1st search, then i do thousands of searchs with just SetParamValue & Requery().
The last proposal of AndyAinscow is the same than the AlexFM's one : it's the slowest.
0
 
AndyAinscowCommented:
sorry to say is the slowest of all i have tried.

I assume that is directed to code from both Alex and myself.

To be honest I am surprised by that.  If I had a table with x (say 1000000) records I would expect that just returning one record would be much faster than opening the complete table and then searching.

Have you compared dbOpenDynaset with dbOpenSnapshot?
0
 
JP_GobletAuthor Commented:
AndyAinscow,
my comment at 01.40PM was directed to AlexFM, the one at 01:49PM was for both of you ...

"To be honest I am surprised by that.  If I had a table with x (say 1000000) records I would expect that just returning one record would be much faster than opening the complete table and then searching"

perhaps but, from my experience,  if i have to do 1000 searches, then opening just once all the recordset and then querying in it is faster than opening 1000 times the recordset for just one record.

ANYWAY... COMMENT TO ALL :
stop your efforts for now, i have perhaps discovered something in my code explaining the lack of speed ...
0
 
AndyAinscowCommented:
< then i do thousands of searchs>  You may wish to explain a bit more of what you are attempting to achieve.  How do you perform the timing?  Have you used the profile tool?
0
 
AndyAinscowCommented:
OK - waiting.
0
 
JP_GobletAuthor Commented:
My problem is solved.

First I must confess i made invalid time comparisons because i was comparing between : 1) a debug executable using static binding OUTSIDE visual studio 2) 1) a debug executable using dynamic binding INSIDE visual studio.  I didn't know than lauching from visual studio had such a bad effect on performance. When i compare in the same conditions, the version with dynamic binding is as fast as the other one.

Secundly there was in my code (in the 2 versions) an instruction opening a ADO recordset at each search, and the bulk of the time was losed in that instruction. I have temporary commented this line, i know now where i have to put my optimization efforts. Yes,  AndyAinscow, if i had a profiler i could have seen that immediately, but i have just a watch.

I now have seriously compared times for the different methods for searching a record. What i'm doing is filling a table of points;  some of the data written in this table are extracted from another table, the link being the tag of the point; that's why i have, for each point i add in the 1st table, to query its tag in the 2nd table.
In my previous comparisons, i cound not detect speed differences between the search methods, because of the opening of the ADO recordset that took must of the time, and because i was filling  a too small points table. Now i fill a 40000 points table. The searches are made in a table of same size (40000).

the fastest : using a  parameterized query, as is wrote in my 1st post (my solution 3) : 13 sec.

much slower : what you 2 have suggested : reopening the recordset each time with a SQL query filtering on the tag : 1 min. 5 sec.

awful : using FindFirst (my solution 1) : this starts well but soon the progression is more and more slower ... i gave out after 2 minutes, the progression bar was at about 30 %.
Surely the fact that FindFirst slows down more & more is due to the fact than in the search table the records are ordered by tag, so as the filling progress, the tags are increasing, and these tags have to be searched farther & farther from the beginning. I have no garantee that the search table will be always ordered by tag, otherwise i would do a FindNext rather than a FindFirst.

Thank you 2 for your help; if you have no objection i will ask for closing the question.
0
 
AndyAinscowCommented:
There is a profiler included in Visual C (at least with version 6).  It should be on the build menu at the bottom.


<What i'm doing is filling a table of points;  some of the data written in this table are extracted from another table, the link being the tag of the point; that's why i have, for each point i add in the 1st table, to query its tag in the 2nd table.>
Why not use an update query - no need to get the records - probably fastest of all.
0
 
JP_GobletAuthor Commented:
i have visual studio 6; profiler option is there but greyed; perhaps the version my boss bought (professional edition) does not have the profiler.

an update query is for modifying data in a table; what i'm doing iis filling a table, so it's rather an insert query that i need :
INSERT INTO table_name VALUES (value1, value2,....)
i don't see how to use that query in a way such that valueX woud come from another table
0
 
AndyAinscowCommented:
greyed - possibly optional component to install, I have pro version.


INSERT INTO Target ( [key], [number] )
SELECT Base_Query.key, Base_Query.TotalNumber
FROM Base_Query;

This takes values from another query(or table) and adds records into the table called target.
0
 
JP_GobletAuthor Commented:
i'm trying that ...
0
 
JP_GobletAuthor Commented:
yes i see how this query could insert data from one or more tables in another table (very helpful doc. at
http://www.devguru.com/Technologies/jetsql/quickref/insert_into.html)

in fact what i do is a bit more complicated : some of the values i put in the new table come from another table, but others values are constants or even are computed from values extracted from the query_table ...
so i would need a insert query allowing in the same time a SELECT statement for values extracted from another table, and a VALUES statement for the others values ...
i would be very surprised if what i do could be doable with just one query

And i have to say that my table of  40000 points was an extrem case, usually it's at most 10000, and the release version is also well faster than debug, so the time i could gain is very little.

however i wouldn't want you believe i'm trying to keep my points, your helpful comments deserve a  reward ...
0
 
AndyAinscowCommented:
Thanks.

You can use left/right joins to determine if a record is already there (or missing) in your target table.  There is nothing to stop you using 2 (or more) queries, one after the other.  Wrap in transaction if data integrity requires an all or none.

INSERT INTO Target ( [key], [number] ,[DUMMY])
SELECT Base_Query.key, Base_Query.TotalNumber, 5 AS x     <--- 5 is a constant value, not from the other table/query
FROM Base_Query;
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now