1

I have a database with ~50000 rows and 16 columns(at least for now). When a button is pressed in the main form of the application, the program makes some calculations from which a result is computed. I need to position the current record of the table as the computed result.

example: i have the result 35500. I need to set the position to the record number 35500 in the table. How can I accomplish that in a fast way? I work with ADO (no SQL) and I've tried with instructions like

table.First;
table.MoveBy(35500);

but they are very slow for this case. I also have to mention that the positioning is done without any output in any component like TDBGrid or anything else, so it has to be a faster way

Ken White
  • 123,280
  • 14
  • 225
  • 444
new delphi user
  • 57
  • 1
  • 5
  • Probably because you ask Microsoft ADO drivers to do 35500 "move next record" operations, one after another. If you really do need ISAM operations instead of SQL (but why???) maybe you'd have more luck with some Delphi-native engine ( tdbf.sf.net, nexusDB or anything from Torry.net ) where you would be able to do something like `DataSetObject.RecNo := 12345;` ? – Arioch 'The Dec 16 '16 at 19:34
  • 1
    Use ClientDataSet – FLICKER Dec 16 '16 at 21:15
  • @FLICKER: Actually, with a table the size the OP mentions on a database server, ADO is much faster than TClientDataSet, especially in the retrieval phase. – MartynA Dec 16 '16 at 22:18
  • It is *not* necessary to repeat your tags in the title of your question. The tag system works extremely well here - please stop trying to help it. It doesn't need the assistance. – Ken White Dec 16 '16 at 23:04
  • @KenWhite ok, I'll keep that in mind – new delphi user Dec 17 '16 at 10:36

1 Answers1

4

I realise you say that there are no db-aware controls connected to your dataset, but have you tried comparing the time to do a MoveBy(50000) with and without it being surrounded by calls to .DisableControls and .EnableControls? You might think that without any db-aware components, using .DisableControls and .EnableControls or not would make no difference, but in fact it does.

On my system, doing a MoveBy(50000) against a Sql Server (2014) takes 19+ seconds without .DisableControls and .EnableControls and 0.2 seconds with, which is a huge difference.

I think my answer to this previous q may be relevant: Why does scrolling through ADOTable get slower and slower?

Btw, MoveBy() is mainly useful when the best way of going to the row you want can be expressed by its Distance parameter. As noted in a comment by Christine Ross, if you know the value of one or more fields in the row you want to move to, it can be much faster to use the boolean Locate function, as in

if AdoQuery1.Locate('CountryCode', 'US', [])  then

Locate allows you to specify several fields to match as in

if AdoQuery1.Locate('CountryCode;Surname', VarArrayOf(['US', 'Smith']), []) then
Community
  • 1
  • 1
MartynA
  • 30,454
  • 4
  • 32
  • 73