Re: SQL and Filter

From: Val Mazur (group51a_at_hotmail.com)
Date: 05/28/04 

Date: Thu, 27 May 2004 21:58:58 -0400

Hi Lorne,

Using Filter is not a good idea unless recordset is disconnected. It would
require roundtrips between the client and server. Best way is to use SELECT
with WHERE clause, as in your second scenario. In most case, if you need to
provide some sort of batch updating, you even could avoid opening of the
recordset at all and do everything on a server side using action queries.
Personally, I do not see any reason to select all 17000 records, because
client probably will not work with all these records at same time. What are
you trying to achieve in your case? 

-- 
Val Mazur
Microsoft MVP
"Lorne Merner" <lorne.merner@lhsc.on.ca> wrote in message 
news:eFmSveCREHA.808@tk2msftngp13.phx.gbl...
>I have three recordsets opened from an SQL database.  The common field for
> all three is ID.  I open each recordset with Cursorlocation = client,
> CursorType = OpenDynamic, LockType = Optimistic.
>
> Recordset one is Master with 17,000 records.
> Recordset two is Trainers with 4,000 records.
> Recordset three is Rooms with 4,000 records.
>
> We want all 17,000 master records returned and processed whether there are
> Trainers or Rooms Records but we want to retrieve the Trainers or Rooms
> records.  With DAO using Seek, in the current Access Database, the
> processing is almost instant but using ADO it is very slow.  The following
> is the scenarios:
>
> The AdoOpenRs subroutine just opens the recordsets to the above 
> conditions.
>
> Dim MasterRs as ADODB.RecordSet
> Dim TrainersRs as ADODB.RecordSet
> Dim RoomsRs as ADODB.RecordSet
>
> AdoOpenRs("SELECT * FROM [Master]", MasterRs)
> AdoOpenRs("SELECT * FROM [Trainers]", TrainersRs)
> AdoOpenRs("SELECT * FROM [Rooms]", RoomsRs)
>
> '    Scenario 1 using filters.
> do until MasterRs.EOF = True
>    TrainersRs.Filter = ""
>    TrainersRs.Filter = "[ID] = '" + MasterRs("ID") + "'"
>    RoomsRs.Filter = ""
>    RoomsRs.Filter = "[ID] = '" + MasterRs("ID") + "'"
>    MasterRs.MoveNext
> loop
>
> '    Scenario 2 using open recordset.
> do until MasterRs.EOF = True
>    AdoOpenRs("SELECT * FROM [Trainers] WHERE [ID] = '" + MasterRs("ID") +
> "'", TrainersRs)
>    AdoOpenRs("SELECT * FROM [Rooms] WHERE [ID] = '" + MasterRs("ID") + 
> "'",
> RoomsRs)
>    MasterRs.MoveNext
> loop
>
> Scenario 2 is much faster than scenario 1.  When using the filter in
> scenario 1 it took up to 3-4 seconds each filter which on 17,000 records 
> is
> not fast.  I always thought filters are processing recordsets which are in
> memory which should be instant.
>
> Either scenario is not even close to using the seek in DAO, it is almost
> instantaneous.
>
> I could use INNER JOINS but I need all the records in Master whether there
> are records in the other tables and I am on the understanding that would 
> not
> be the case.
>
> Thanks for any thoughts.
> Lorne
>
>

Relevant Pages

  • Re: AD & NAT
    ... I thought i explained the scenario we are facing in detail but i would ... The client is a demerged company of the parent. ... The natting would be done by a NAT device and not the ... understanding purpose we would call it as primary datacentre while additional ...
    (microsoft.public.windows.server.active_directory)
  • Re: OpenDataSource SQL Server xpress problem
    ... In scenario 1) If I go to the Providers Tab in the 'Data Link Properties' dialog and set this to SQL Native Client and then work through the boxes it then succeeds in the Test Connection and the subsequent Mail Merge!! ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Encryption Scenerio
    ... For the second scenario, using DPAPI is probably the best way to go. ... i have problems about storing KEYS that are used in encryption. ... not decide where should i store my keys or how should i store them. ... We are developing project with server / client architecture. ...
    (microsoft.public.dotnet.security)
  • Re: 1080i & 720p HDTV Resolution
    ... Basically, you appeal to the worst case scenario, but that worst case ... > be transformed into highly visible flashing by the interlacing ... all TV uses a vertical lowpass filter that does ...
    (sci.image.processing)
  • SQL and Filter
    ... Trainers or Rooms Records but we want to retrieve the Trainers or Rooms ... The AdoOpenRs subroutine just opens the recordsets to the above conditions. ... ' Scenario 1 using filters. ... ' Scenario 2 using open recordset. ...
    (microsoft.public.vb.database.ado)