|
|
David W. Fenton wrote:
> Bri <not@here.com> wrote in news:3ceZf.2940$_u1.1473@pd7tw2no:
>
>
>
> Well, I don't think your test is very realistic -- 200K records is
> an awful lot to open at once.
I picked a large table to test the extremes. This is ment to be a test
to show up differences in relative performance, not necessarily to mimic
something that I would do in an app.
>
>
> I just can't help but think that there's something else going on
> here because I wouldn't expect that kind of difference.
Opening the Table from the Database Window and navigating to the last
record take less time than the FindFirst. I don't know what is happening
either, but something is causing it to crawl.
> Is the data local or on a network? Granted, networked is likely to
> be a production environment, but it seems like if you're going to
> use a high-end test (a 200K-record table), then you should use the
> most high-performance scenario. Either that, or use a more realistic
> table.
It is local, but in a PGPdisk volumn (mounted as N: to be my local
equivelent of the client's network drive). I expect that there is some
overhead due to the encrypt/decrypt process, but it is minimal compared
to an actual network. I've never seen any noticable difference between
something in the PGPdisk and something not in it. The Table is
realistic. It is an actual table with actual data that I actually search
and query against in the app. My test code is running in the BE (so I
can use Seek directly), but the app itself is a split FE.
> I'd be interested to hear what happens with a 20K-record table, for
> instance.
The tests I did used the tables PK which is a Text field
I just tried a few variations:
- Used a unique Long Integer field;
Open rs - 0
FindFirst - 2.972656
FindFirst finish - 2.984375
Query (1000) - 2.011719
Seek (1000) - 0.421875
All steps ran faster, but Seek improved the most, to a 4x ratio.
- Used a smaller table (32k records) and a unique Long Integer field;
Open rs - 0
FindFirst - 0.4609375
FindFirst finish - 0.4609375
Query (1000) - 1.820313
Seek (1000) - 0.390625
Now Seek is 4x+ faster. FindFirst is faster than before, but remember
that this time is for ONE findfirst vs 1000 loops of the others.
- Used a even smaller table (2.7k records) and a unique Long Integer
field;
Open rs - 0
FindFirst - 0.0390625
FindFirst finish - 0.05078125
Query (1000) - 1.722656
Seek (1000) - 0.5429688
It is now looking like Seek and the Query are only marginally affected
by the size of the table while FindFirst improves significantly (still
not as fast by a long shot). This seems to support that Seek and the
Query are using the index but FindFirst is not.
> It also makes no sense to me that all of this would take that long
> in a recordset since I have apps that basically do the same thing in
> bound forms that don't take anything like that amount of time
> (though the tables are 200K records in most of the apps, but some of
> them are still close to 100K records).
I can't explain it either, only demonstrate it.
>
>
> Use of SEEK in that scenario would seem to me to be a clear case of
> premature optimization.
Agreed.
>
>
> I'd be interested if the slow FindFirst appears after passing some
> threshold in the number of records. I assume you were using an
> Autonumber PK for the index you were seeking on? It could be that
> there are differences in FindFirst's efficiency with different data
> types, since that might have an important impact on how many data
> pages are in the index and need to be retrieved.
As mentioned above, the first tests were on a text PK, the test with the
Long Unique index, still shows the same order of speed. Same with the
smaller tables.
> Perhaps FindFirst is doing a scan of the index, whereas SEEK is
> doing nodal jumps through the b-tree. If that's the case, it would
> cause me to wonder why FindFirst would have been engineered in that
> manner.
Could be, but it seems too slow for even that.
> It would also be useful to see how much FindFirst is slowed down by
> dropping the index. That would definitely show if the FindFirst is
> not using the index at all or if it's just using it less efficiently
> than SEEK.
OK, using the last table above (2.7k records) using a field without an
index (dropped Seek from test as it requires an index):
Open rs - 0
FindFirst - 0.0390625
FindFirst finish - 0.05078125
Query (1000) - 3.632813
FindFirst times are IDENTICAL to the test with the unique index. This
supports my theory that FindFirst is not using the index. The query is
about half as fast. The table scan for the first loop may get cached so
that the subsequent loops are faster. I was expecting the query to have
slowed down a lot more than that.
None of this changes how I would code in real life as I usually use
FindFirst to do a single pass of a RecordsetClone where neither Seek or
a Query would be of use (the RecordsetClone is already based on a query,
not on a whole table so is usually <100 records).
--
Bri
|
|