Hi, As far as I can see, the entire table will always be scanned in this case. This is one situation in which a prior analysis of the range arguments may be helpful. Assuming that you are doing this inside a subroutine, it may be worthwhile to check the difference between start and end dates (in this case 4385 inclusive). Form an active list with all possible Ids, then do the select on top of the active list. Instead of scanning the entire table, your select will be limited to testing only the 4,385 out of 340,000 record keys. Note that if the number of days in the range was only 10 for example, the regular select would still take the same time as when the range contains 10,000 days but with the active list technique it would return almost instantaneously. The difference in performance will become very noticeable for a table with millions of records with a unique account portion of the Id and when we know from the business logic that date range can never exceed some relatively small number.
On Fri, Jul 31, 2020 at 17:15 VK <[email protected]> wrote: > Hi coleagues. > > What is the fastest way to SELECT a set of records from a J4 table: > > @IDs are in the format: (account_number)-(date_in_internal_format), e.g. > 5001-18628. > > trying: > > SELECT THE.TABLE WITH @ID BETWEEN "5001-18628" "5001-23012" > > Result is bit more than a second (Windows 2019 server, enough memory and > CPUs). Maybe it can be improved? > > usr: 1.11 sys: 0.00 elapsed: 0m1.11s > > Usage of "GE ... LE" didn't help: > > usr: 1.36 sys: 0.00 elapsed: 0m1.36s > > Records number: circa 340,000. > > Resizing didn't help. > > Conversion to JR, as expected, made things a bit worse. > > Thought about year-based distribution but haven't given it a try yet. > > Last question - is jBASE indexing stable enough to be trusted? > > TAFC 13 (no, question isn't about T24!) > > -- > -- > IMPORTANT: T24/Globus posts are no longer accepted on this forum. > > To post, send email to [email protected] > To unsubscribe, send email to [email protected] > For more options, visit this group at > http://groups.google.com/group/jBASE?hl=en > > --- > You received this message because you are subscribed to the Google Groups > "jBASE" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jbase/6dc6a3b5-6d52-4f90-bb32-2f6c7fa8d260o%40googlegroups.com > <https://groups.google.com/d/msgid/jbase/6dc6a3b5-6d52-4f90-bb32-2f6c7fa8d260o%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- -- IMPORTANT: T24/Globus posts are no longer accepted on this forum. To post, send email to [email protected] To unsubscribe, send email to [email protected] For more options, visit this group at http://groups.google.com/group/jBASE?hl=en --- You received this message because you are subscribed to the Google Groups "jBASE" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jbase/CAJeZcsZB91yUVd%2BUF8rTxxki%3DiO39bawmAkX%2BbN019wuvVaBwA%40mail.gmail.com.
