Fastest Way to Get Progress Database Table Into Memory

[TomBascom]

Dynamic query may not work so well with larger result sets.

Unless you specify "forward-only" (or use the -noautoreslist startup parameter) dynamic queries will build sort files on the client -- which will be painfully slow once they become non-trivial in size.

In any event 2,500 records is an awfully small data set to be drawing conclusions from but it looks to me like your static FOR EACH with a WHERE clause was the winner. Which is pretty much what I would have expected...

You might also try using field lists.

And another thing... don't rely on etime to draw conclusions that you're going to try to scale. A better approach is compare logical read operations and "useful records". The closer you come to a "perfect" ratio (slightly more than 2:1) that doesn't change as the result set size changes the better the query will scale and the more reliable the relative performance will be as you change things (like -B) in your environment.

 

[bulklodd]

Unless you specify "forward-only" (or use the -noautoreslist startup parameter) dynamic queries will build sort files on the client -- which will be painfully slow once they become non-trivial in size.

however, the progress manual says

Specifying this startup parameter (-noautoreslist) has no affect on dynamic queries, since they are scrolling queries by default.​

 

[Dries Feys]

Aah, I didn't know of of the existence of this parameter yet (neither does my progress help-file)

Anyhow, you can find here some info on those parameters.

I'll probably use the dynamic query, but that's because I have to run 6 times the same query with a small change in the where clausule.

About the ratio : my query & indexes are designed that way that I have a perfect index match, so I have a 1:1 relationship on the query :yippee:

 

[TomBascom]

Bulklodd is right Knowledgebase entry P18639 details forward-only & -noautoreslist.

About the query... you cannot have a 1:1 ratio -- Progress always uses an index thus you always have at least 2 logical reads (block accesses) for a record read (one for the index block, another for the record). It is also very good practice to actually test these things -- sometimes there is a very large discrepency between what you think your query should do and what it actually does do. Check out "Investigating Suspicious Code" at www.greenfieldtech.com for some ideas on how to go about instrumenting that sort of test.