Re: SELECT of records in range

2020-08-15 Thread VK 
Hi.
Thanks; we decided to create an index on @ID; QUERY.INDEX works very fast.

On Saturday, 15 August 2020 at 00:32:34 UTC+2 cliff...@gmail.com wrote:

> JBase indexes are very stable. We have about 145 throughout all of our 
> tables.
>
>
>
> On Friday, July 31, 2020 at 11:15:36 AM UTC-4, VK 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 jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/1bd350b0-463d-4e12-8d8c-e1080f2beb8dn%40googlegroups.com.

Re: SELECT of records in range

2020-08-14 Thread Clifford Ponce 
JBase indexes are very stable. We have about 145 throughout all of our 
tables.



On Friday, July 31, 2020 at 11:15:36 AM UTC-4, VK 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 jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/9e418ece-9f9a-4039-ae56-fb11ea28ac19o%40googlegroups.com.

Re: SELECT of records in range

2020-08-04 Thread Vladimir Kazimirchik 
Thanks to everyone who responded.

On 02/08/2020, Marco Manyevere  wrote:
> 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  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 jBASE@googlegroups.com
>> To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/jbase/6dc6a3b5-6d52-4f90-bb32-2f6c7fa8d260o%40googlegroups.com
>> 
>> .
>>
>
> --
> --
> IMPORTANT: T24/Globus posts are no longer accepted on this forum.
>
> To post, send email to jBASE@googlegroups.com
> To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jbase/CAJeZcsZB91yUVd%2BUF8rTxxki%3DiO39bawmAkX%2BbN019wuvVaBwA%40mail.gmail.com.
>

-- 
-- 
IMPORTANT: T24/Globus posts are no longer accepted on this forum.

To post, send email to jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/CAP1OLt%2BkzBmcCopdGNe30h%2BjzFX5CymBXdGrG26JZaJQcGKPkw%40mail.gmail.com.

Re: SELECT of records in range

2020-08-02 Thread 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.


On Fri, Jul 31, 2020 at 17:15 VK  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 jBASE@googlegroups.com
> To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jbase/6dc6a3b5-6d52-4f90-bb32-2f6c7fa8d260o%40googlegroups.com
> 
> .
>

-- 
-- 
IMPORTANT: T24/Globus posts are no longer accepted on this forum.

To post, send email to jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/CAJeZcsZB91yUVd%2BUF8rTxxki%3DiO39bawmAkX%2BbN019wuvVaBwA%40mail.gmail.com.

Re: SELECT of records in range

2020-08-01 Thread 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  wrote:
> 
> 
> 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:
>> 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 jBASE@googlegroups.com
> To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/jbase/ef1a5fa4-6e7d-498d-8c2a-b501af233ca5o%40googlegroups.com.

-- 
-- 
IMPORTANT: T24/Globus posts are no longer accepted on this forum.

To post, send email to jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/E4D147D8-A283-4BDE-A336-EE086F6E7BC2%40gmail.com.

Re: SELECT of records in range

2020-08-01 Thread 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:
>
> 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 jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/ef1a5fa4-6e7d-498d-8c2a-b501af233ca5o%40googlegroups.com.

Re: SELECT of records in range

2020-07-31 Thread 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


On Friday, 31 July 2020 17:15:36 UTC+2, VK 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 jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/44f8dfa8-1bd3-4f8e-af6a-377448a17d5co%40googlegroups.com.

Re: SELECT of records in range

2020-07-31 Thread 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.



On Fri, 31 Jul 2020 at 16:15, VK  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 jBASE@googlegroups.com
> To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/jbase/6dc6a3b5-6d52-4f90-bb32-2f6c7fa8d260o%40googlegroups.com
> 
> .
>

-- 
-- 
IMPORTANT: T24/Globus posts are no longer accepted on this forum.

To post, send email to jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/CAE1D0%2Bgp%3Db3ofGRugdptnUDMhK7ZzfQyz8FaMdN-UuUWDnxWyA%40mail.gmail.com.

SELECT of records in range

2020-07-31 Thread 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!)

-- 
-- 
IMPORTANT: T24/Globus posts are no longer accepted on this forum.

To post, send email to jBASE@googlegroups.com
To unsubscribe, send email to jbase-unsubscr...@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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/jbase/6dc6a3b5-6d52-4f90-bb32-2f6c7fa8d260o%40googlegroups.com.