Re: compare 2 recordsets

Tech-Archive recommends: Speed Up your PC by fixing your registry

Have run your code now and although there is only one loop it is no faster than my code.
Maybe I could make it faster by testing against an array rather than the parent node recordset.
By the way I take back what I said about the recordset getting un-sorted. This was something
else.
Maybe it could be speeded up by using Seek.
Thanks again for all the trouble taken in looking at this.
Will see if there is a better way and come back.

RBS



"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message news:%23ilb4QtAIHA.4836@xxxxxxxxxxxxxxxxxxxxxxx

"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:OwWCChmAIHA.1168@xxxxxxxxxxxxxxxxxxxxxxx
Yes, 2 databases, so I can't do it in SQL.

Why not?

Every database I know has a method of registering a 3rd party database with itself.
For SQL Server, it is stored producedure

sp_addlinkedserver

with arguments
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]

After that I could do

DELETE FROM tablename where somefield IN (SELECT somefield FROM remoteserver.dbo.anothertablename)
Far quicker than the 2-recordset approach

But on the 2-recordset approach:

It is not a double loop.
It is a single loop anti-merge (and very fast).
Something like

===========================

IF not recA.EOF and not recB.EOF THEN
DO
If recA.Item.Fld1 < rec2.Item.FldB THEN
recA.Delete
recA.MoveNext
If recA.EOF THEN EXIT DO
ELSEIf recA.Item.Fld1 > recB.Item.Fld1 THEN
recB.MoveNext
If recB.EOF THEN EXIT DO
ELSE
' they are equal
recA.MoveNext
recB.MoveNext
If recA.EOF or recB.EOF THEN EXIT DO
END IF
LOOP
END IF

'Deal with the fact that recA may not be exhausted but recB must be (other case needs no consideration)
DO WHILE NOT recA.EOF
recA.Delete
recA.MoveNext
LOOP

=================================

Note you did not say if recA or recB could have repeated values
If they can I would change

ELSE
' they are equal
recA.MoveNext
recB.MoveNext
If recA.EOF or recB.EOF THEN EXIT DO
END IF

to

ELSE
' they are equal
recA.MoveNext
If recA.EOF THEN EXIT DO
END IF

so that all A's duplicate values match the B record

Stephen Howe


.

Relevant Pages

  • Re: compare 2 recordsets
    ... This made me think that maybe a double loop could be faster as you ... point and do exit do or exit loop when it has past the value. ... Another thing to try is using GetRows to put the rsParentNode data into an array - looping through an array is much quicker than looping through a recordset ...
    (microsoft.public.data.ado)
  • Re: VBScript Exit Loops
    ... Within this Main Loop there are lots of conditions/clauses IF Else... ... I know I can use Exit Do, Exit IF etc but if I ... Until adoRecordset.EOF Loop and move to the next RecordSet. ... simply exit the sub. ...
    (microsoft.public.scripting.vbscript)
  • Re: VBScript Exit Loops
    ... Within this Main Loop there are lots of conditions/clauses IF Else... ... I know I can use Exit Do, Exit IF etc but if I ... Until adoRecordset.EOF Loop and move to the next RecordSet. ... Sub doTheWorkForUser(... ...
    (microsoft.public.scripting.vbscript)
  • Re: VBScript Exit Loops
    ... I have a script that goes through a RecordSet containing user details ... Within this Main Loop there are lots of conditions/clauses IF Else... ... I know I can use Exit Do, Exit IF etc but if I ... Until adoRecordset.EOF Loop and move to the next RecordSet. ...
    (microsoft.public.scripting.vbscript)
  • Re: Infinite Loops and Explicit Exits
    ... > CS> One of these proposals relaxes the current restriction that an EXIT ... > termination of the loop is not visible at that point. ... > terminating condition is visible in that context. ... > You now want to allow this remote procedure, ...
    (comp.lang.cobol)