SELECT of records in range

[VK]

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!)

[Dennis Bartlett]

I would make the SELECT field specific, ie SELECT WITH ACCOUNT = "5001" AND WITH DATE GT @18628 AND DATE LT  23012

ACCOUNT_NUMBER and DATE are likely to be indexed.

It's like having a porsche in the garage but insisting on using your daughter's bicycle to do the speed trial if you insist on using a SELECT on a random ID being between x and y, when you actually have indexed fields you can select on.

Dennis Bartlett

If you consider that 3000 plus banks used JBase before it moved on, I'd say the case for JBase indexing is solid. 

--
--
IMPORTANT: T24/Globus posts are no longer accepted on this forum.
 
To post, send email to jB...@googlegroups.com
To unsubscribe, send email to jBASE-un...@googlegroups.com
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 jbase+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jbase/6dc6a3b5-6d52-4f90-bb32-2f6c7fa8d260o%40googlegroups.com.

[VK]

> If you consider that 3000 plus banks used JBase before it moved on, I'd say the case for JBase indexing is solid

how many of them used jBASE and not Oracle / DB2 /MSSQL etc - maybe 10%?

how many of these 10% used jBASE indexing?

If indexing was solid why Temenos uses CONCAT files and (IIRC) EB.ALTERNATE.INDEX?

Well, I'd like to hear not statistics but the reply of people really using that :)))

Thanks anyway :))

Cheers

VK

[VK]

Sorry forgot to mention that there's no field with account number, it's just a part of @ID. Isn't @ID a primary index itself?

On Friday, 31 July 2020 17:15:36 UTC+2, VK wrote:

[Peter Falson]

In an RDBMS yes, on jBASE (MV platforms) you have to create an index. 

Sent from my iPhone

On Aug 1, 2020, at 8:51 AM, VK <kzm...@gmail.com> wrote:



--
--
IMPORTANT: T24/Globus posts are no longer accepted on this forum.
 
To post, send email to jB...@googlegroups.com
To unsubscribe, send email to jBASE-un...@googlegroups.com
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 jbase+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jbase/ef1a5fa4-6e7d-498d-8c2a-b501af233ca5o%40googlegroups.com.

[Marco Manyevere]

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.

--
--
IMPORTANT: T24/Globus posts are no longer accepted on this forum.
 
To post, send email to jB...@googlegroups.com
To unsubscribe, send email to jBASE-un...@googlegroups.com
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 jbase+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jbase/6dc6a3b5-6d52-4f90-bb32-2f6c7fa8d260o%40googlegroups.com.

[Vladimir Kazimirchik]

Thanks to everyone who responded.

>> <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 jB...@googlegroups.com
> To unsubscribe, send email to jBASE-un...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/jBASE?hl=en
>
> ---

> You received this message because you are subscribed to a topic in the
> Google Groups "jBASE" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/jbase/oZTLRieZ65I/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

> jbase+un...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/jbase/CAJeZcsZB91yUVd%2BUF8rTxxki%3DiO39bawmAkX%2BbN019wuvVaBwA%40mail.gmail.com.
>

[Clifford Ponce]

JBase indexes are very stable. We have about 145 throughout all of our tables.

[VK]

Hi.

Thanks; we decided to create an index on @ID; QUERY.INDEX works very fast.