Ado recordsets populated from sqlserver .filter problem

[huggyboy]

Hello all - i have a problem someone might be able to help me with.

I am converting an access 2000 mdb with linked tables to another mdb to work with linked tables on Sql Server 2005 instead.
All has gone reasonably ok but now i am having a performance problem - something that is instantaneous on the existing system takes 1m 50 seconds on the new one.
I am reading 3 ado recordsets from the sql server then writing an ado recordset on the frontend with the following kind of processing

do until rst1.eof
   rst2.filter = blah
   do until rst2.eof
      rst3.filter = blah
      do until rst3.eof
      .... processing - .addnew on the local recordset .update
          rst3.movenext
      loop
   rst2.movenext
   loop
   rst1.movenext
loop

Running sql profiler trace on the sql database and stopping the vba in debug i saw that the .filter command on each recordset seemed to generate a select statement for each 'hit' recordset entry!!! - hence the performance issue.
According to various sources on the net .cachesize on the recordset should fix this - seems to have no effect at all.

Any ideas? Is there any way to force the caching of the recordsets?
I might be destined to rewrite it using arrays, populating them from the recordsets using getrows - seems a bit of backwards step to me.

Thanks in anticipation.

 

[RoyVidar]

I would consider trying to do the processing on the server. With SQL server, you have a powerful engine with the very fast and powerful tool Stored Procedure.

Roy-Vidar

 

[huggyboy]

Thanks for the reply.

Calling stored procedures is normally the way I do these kind of things but that still involves setting some parameters then calling the stored procedure to repopulate the recordset when the data is (supposedly) already in the existing recordset.

As an experiment I converted the innermost loop to use an array and immediately the processing time dropped from 1min50secs to 7 seconds so i think i will use this approach on this occasion (i inherited this code from someone else and my boss is keen to get it converted & useable for future developments later in the year)

 

[huggyboy]

oops - I've just checked the array after .getrows and all the recordsets havent been transferred to the array only the recordset entries that have been cached - rethink called for ....

 

[RoyVidar]

Depends on the cursor, I think - but again, what you're doing, dragging lot of records over the network to create local recordsets is a dead slow method. It would be much better doing the whole process on the server. An SQL server SP is way more powerfull than Access/Jet queries, even temp tables.

Roy-Vidar

 

[huggyboy]

OK i will change my approach - it is frustrating because the code works fine with vba in mdb reading recordsets from another mdb but incredibly slowly with sql server database

Cheers
Hugh

 

[huggyboy]

Got it to work reasonably ok by looping round each of the entries in each recordset (couldnt get .getrows to work then replaced the loop round each of the recordsets witha for.. next loop round the array with an if statement in place of the filter.

Bit old-fashioned - will try a more radical approach when i have time

Thanks for the input Roy-Vidar