Re: compare 2 recordsets

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Let me explain better.
I have an Interbase 5.6 database. It can be slow and certain joins should be avoided.
What I have done is this: I move data from Interbase to SQLite in lightweight SQL,
that is mainly no joins or very occasionally one join at the most. This works fine in
that it is fast and puts little strain on the system, so it doesn't slow down the clinical
front-end software. Once I have all the needed data in SQLite (these are clincal searches)
I can manipulate it further there and then finally dump to Excel. All this is working very
well, but I thought I could speed up one particular and common scenario where I need
to take out patients found by a child node (this is a treeview) that don't appear in the parent
node of that child node. This is the way the graphical interface presents the searches.
What happens now is that the the child node gets all the data from Interbase with the parameters
of that child node, moves the data to SQLite and then I take out the patients of that child node
by joining to the SQLite table of the parent node. The time consuming step in this is the moving
of data from the ADO recordset (with data from Interbase) to SQLite. Hence my idea to do this
by filtering the ADO recordset against a dhSQLite recordset (or array) obtained from the table
of the parent node. This would mean that less had to move from the ADO recordset to SQLite.
I could take this idea further by also taking out duplicate patients.

So, you can see that in this setup registering SQLite with Interbase (or the other way round) won't help.

Now as the relevant columns (PATIENT_ID) of both recordsets are sorted ascending I can do something
like this, but there are 2 drawbacks. It is not that fast and also for some reason the resulting recordset is
not sorted anymore.

260 Do While i <= UBound(arrParent, 2) And rsNode.EOF = False
270 Do While rsNode.Fields(lNodeField) < arrParent(0, i)
280 rsNode.Delete adAffectCurrent
290 rsNode.MoveNext
300 Loop
310 If rsNode.Fields(lNodeField) = arrParent(0, i) Then
320 rsNode.MoveNext
330 End If
340 i = i + 1
350 Loop

So, as this is not great I will have a look at your code and hopefully that will do better.
Thanks for that.
I have duplicate values to consider, but only in the recordset that needs the deletes.

Will have a go now and let you know.


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


.