Is there a faster/better way than .Filter?

  • Thread starter Thread starter MyndPhlyp
  • Start date Start date
M

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?
 
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
 
Ted said:
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.
Click to expand...

..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.
 
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.
 
Ted said:
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.
Click to expand...

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 said:
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.
Click to expand...

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).
 
Back
Top