Is there a faster/better way than .Filter?

[MyndPhlyp]

I'm going through one table serially and looking up additional values from
other tables. (Sounds pretty normal, right?) All the tables are indexed on
common columns. The secondary tables are a 0-n:1 relationship with the
primary table.

Right now, I'm using .Filter to get the limited Recordset on the secondary
tables with:

<primarykey> = <somevalue> AND <anothercolumn> = <someothervalue>

(.Filter has to be used rather than .Find due to the "AND" in the criteria.)

The overhead is painful. Comparing the throughput of a serial read on the
primary table with the throughput of the two added tables via .Filter, the
performance degrades in the order of 1:10 (or so it seems). I've tried
creating a compound index on the primary and secondary columns, but no
relief.

Is there a better way than .Filter to look up rows in the secondary
Recordsets?

 

[Ted]

You may want to look at using recordset.FindFirst
and .FindNext. You would have to define your recordset
as a dynaset type rather than a table type. I'm not sure
whether it will improve performance, but I know that
these accept normal sql type criteria statements rather
than just a single field value to look for.

-Ted

 

[MyndPhlyp]

You may want to look at using recordset.FindFirst
and .FindNext. You would have to define your recordset
as a dynaset type rather than a table type. I'm not sure
whether it will improve performance, but I know that
these accept normal sql type criteria statements rather
than just a single field value to look for.

..FindFirst and .FindNext are DAO, aren't they? I'm using ADO. ADO's .Filter
provides the same functionality as DAO's .FindFirst/.FindNext.

I'm experimenting with the .Index property and .Seek method (more
accurately: will be soon) to see if it works out a little better.

 

[Ted]

Yeah, they are DAO. Sorry. I don't think that seek can
be used to find more than the first match, but I may be
wrong.

 

[MyndPhlyp]

Yeah, they are DAO. Sorry. I don't think that seek can
be used to find more than the first match, but I may be
wrong.

I believe you are correct. Fortunately, the concatenated columns that make
up the index will form a unique value - one record is all I would expect to
see (if any). .Seek will return .EOF = True if the key wasn't found.

If I were not able to uniquely identify the record, .Filter would be just
about as good as I could hope for, by the looks of things.

At least I have an alternative method to play around with.

 

[MyndPhlyp]

I believe you are correct. Fortunately, the concatenated columns that make
up the index will form a unique value - one record is all I would expect to
see (if any). .Seek will return .EOF = True if the key wasn't found.

If I were not able to uniquely identify the record, .Filter would be just
about as good as I could hope for, by the looks of things.

At least I have an alternative method to play around with.

Well, that blows THAT idea completely out of the water.

The tables I'm working with are in a Pervasive (Btrieve) database linked
into the Access application. Access is saying the Btrieve table doesn't
support adIndex and adSeek (even though the index I want to use exists).