ADO Filtering and time

[Kdev]

The problem I am working on requires me to retrieve a recordset from a databse with about 14000 records. Then I retrieve another recordset from a database that may have around 14000 records. (The numbers are arbitrary and may never be the same)

Once I have these 2 recordsets I need to iterate over one of them. For each record in the first recordset I need to see if there are any records in the second recordset with the same Serial Number and if so then do some processing with the corresponding data.

I was accomplishing this initially using collections rather than recordsets for indexing. However this turned out to be WAY too slow so I decided to just stick with recordsets and figure out the filter property.

For each record in the first recordset I am setting the filter property of the second recordset to all records that have the same serial number as the current record in the first recordset. This appears to be working though it takes almost 3-4 full seconds to filter. Since I am iterating over every record in the first recordset (again 14000+ records) this will take almost half a day to run!

Is there a better way to accomplish such a task?
What factors determine how long a filter takes to process?

-K

[vchapran]

Have you thought about using Joins? I mean, to retrieve all data in one recordset and process them.
Vlad

[Z LoveLife]

Have you considered opening a hierachical recordset using a shape command? When you do that you basically open two recordsets at the same time which are linked by an index that is created when you open the recordset. There's a parent recordset and a child recordset. You get all of the parents regardless of whether they have children or not It seems like the perfect construct for what you're trying to do. If you'd like to know more about it. Take a look at these links.


http://www.microsoft.com/mind/defaulttop.asp?page=/mind/0199/shaping/shaping.htm&nav=/mind/0199/inthisissuecolumns0199.htm
http://www.vbpj.com/upload/free/features/vbpj/2000/06jun00/bf0600/bf0600.asp
http://support.microsoft.com/support/kb/articles/q196/0/29.asp
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5449

Hope this helps.

[Iouri]

That will compare 2 recordsets

Dim dbcurrent As Database
Dim qryMyQuery As QueryDef
Dim recMatching as Recordset
' Set the database
Set dbcurrent = OpenDatabase("c:\my files\myData.mdb")

'Create a Query
Set qryMyQuery = dbcurrent.CreateQueryDef(MyQuery, "SELECT * FROM [Tablename] WHERE [Exclusions]")

Your next move is to create a recordset based upon the matching criteria......

Set recMatching = dbcurrent.OpenRecordset("SELECT * FROM [Table2] INNER JOIN [MyQuery] ON [Table2].[common field] = [MyQuery].[Common Field]")


The recordset will now contain all the matching criteria that you will be able to manipulate in whatever
form you like.


Iouri Boutchkine
iouri@hotsheet.com

[Iouri]

Here are SQL's that give you everything in Table 1 what is not in table 2

I need all Customer ID in the Table T1 that are not in the table T2

SELECT DISTINCTROW [T1].[CustomerBPCSID], [T1].[ProgramNumber],
[T1].[RebateNumber]
FROM T1 LEFT JOIN T2 ON
[T1].[CustomerBPCSID] = [T2].[CustomerID]
WHERE ([T2].[CustomerID] Is Null);


'another way of doing it==================
This gives you all the records from table1 that are not in table2, and all the records from table2 that are not
in table1. However, you can't tell if those records were modified, added, or deleted. You can just see that
the have changed.
Note: this is 1 SQL statement, presuming that there are two tables (table1 and table2) which have
a two-columnd key (combinatin of field1 and field2)


SELECT * FROM table1 WHERE not(field1 in ( SELECT table1.field1 FROM table1
INNER JOIN table2 on table1.field1 = table2.field1 and tabel1.field2 = table2.field2)) AND not(field2 in
( SELECT table1.field1 FROM table1 INNER JOIN table2 on table1.field1 = table2.field1 and
tabel1.field2 = table2.field2))UNIONSELECT * FROM table2 WHERE not(field1 in
(SELECT table1.field1 FROM table1 INNER JOIN table2 on table1.field1 = table2.field1 and
tabel1.field2 = table2.field2)) AND not(field2 in ( SELECT table1.field1 FROM table1
INNER JOIN table2 on table1.field1 = table2.field1 and tabel1.field2 = table2.field2))


Iouri Boutchkine
iouri@hotsheet.com