Faster Record tagging

[Roger Ayre]

I have boolean fields in my Stock records which I use to remember which
stock records users have selected for particular purposes.
I was horrified to discover when doing some tests on some user's data
how slow this was. We are talking of about 2 minutes for 1000 records.
It uses apply to selection, but trying selection to array and indexing
the field made no significant difference.

So as an alternative I can think of 2 options
A use global sets
B use a separate selection table

The selection must be available to any workstation and even web service
calls
I thought I would pose the question here to see what others do.

With sets I would have to load them on server startup and issue commands
to execute on server to save them. Sets seem to be the way to go to me.
With records I guess I could save one record with a list of selected
records so I only save 1 record instead of 1000. But sets do seem to be
doing much of the work for me.

Of course some-one may have an entirely different way of handling this.
Thanks
Roger

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Jody Bevan]

Roger:

In situations where I have had a predetermined selections that needs  
to get used again and again, I have used sets.

In my case these were not user selections, but selections based on  
searches they would perform. So when we did a mass change of data, I  
would have the server do all the searches and then create sets. The  
sets then got saved into records. If the data was changed in small  
ways I would determine which saved search records needed to be  
modified and would do that all in the background.

Essentially I saved the set into a text field (in version 3 there were  
no blobs as of yet). When the user wanted that selection I would just  
'use set' from the text fields.

In the case I had, there were 1.5 million records, and the search was  
on 5 fields. (This was in 4D version 3). The search would take about 5  
minutes.

By moving to saved sets, when the user would select that selection of  
records it would take under a second. This was a huge improvement that  
for the whole organization.

So we have had very good experiences with sets.

Jody


On Jul 2, 2008, at 10:34 AM, Roger wrote:

> I have boolean fields in my Stock records which I use to remember  
> which stock records users have selected for particular purposes.
> I was horrified to discover when doing some tests on some user's  
> data how slow this was. We are talking of about 2 minutes for 1000  
> records.
> It uses apply to selection, but trying selection to array and  
> indexing the field made no significant difference.
>
> So as an alternative I can think of 2 options
> A use global sets
> B use a separate selection table
>
> The selection must be available to any workstation and even web  
> service calls
> I thought I would pose the question here to see what others do.
>
> With sets I would have to load them on server startup and issue  
> commands to execute on server to save them. Sets seem to be the way  
> to go to me.
> With records I guess I could save one record with a list of selected  
> records so I only save 1 record instead of 1000. But sets do seem to  
> be doing much of the work for me.
>
> Of course some-one may have an entirely different way of handling  
> this.
> Thanks
> Roger

Jody Bevan                            Jonoke Software Dev. Inc.

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Roger Ayre]

Hi Jody

That is something I hadn't thought about - saving the sets in records
I was just think of using the 4D commands Save and Load sets
How do you actually save a set into a text field?

Roger

Jody Bevan wrote:

> In situations where I have had a predetermined selections that needs
> to get used again and again, I have used sets.
>
> In my case these were not user selections, but selections based on
> searches they would perform. So when we did a mass change of data, I
> would have the server do all the searches and then create sets. The
> sets then got saved into records. If the data was changed in small
> ways I would determine which saved search records needed to be
> modified and would do that all in the background.
>
> Essentially I saved the set into a text field (in version 3 there were
> no blobs as of yet). When the user wanted that selection I would just
> 'use set' from the text fields.
>
> In the case I had, there were 1.5 million records, and the search was
> on 5 fields. (This was in 4D version 3). The search would take about 5
> minutes.
>
> By moving to saved sets, when the user would select that selection of
> records it would take under a second. This was a huge improvement that
> for the whole organization.
>
> So we have had very good experiences with sets.
>
> Jody
>
>
> On Jul 2, 2008, at 10:34 AM, Roger wrote:
>
>> I have boolean fields in my Stock records which I use to remember
>> which stock records users have selected for particular purposes.
>> I was horrified to discover when doing some tests on some user's data
>> how slow this was. We are talking of about 2 minutes for 1000 records.
>> It uses apply to selection, but trying selection to array and
>> indexing the field made no significant difference.
>>
>> So as an alternative I can think of 2 options
>> A use global sets
>> B use a separate selection table
>>
>> The selection must be available to any workstation and even web
>> service calls
>> I thought I would pose the question here to see what others do.
>>
>> With sets I would have to load them on server startup and issue
>> commands to execute on server to save them. Sets seem to be the way
>> to go to me.
>> With records I guess I could save one record with a list of selected
>> records so I only save 1 record instead of 1000. But sets do seem to
>> be doing much of the work for me.
>>
>> Of course some-one may have an entirely different way of handling this.
>> Thanks
>> Roger
>
>
>
> Jody Bevan                            Jonoke Software Dev. Inc.

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Chip Scheide]

you dont

set to boolean array
array to blob

the one big 'catch' with storing set -
your data has to be static (or nearly so)
as a dynamic table (lots of changes) will make your sets invalid

On Wed, 02 Jul 2008 19:00:10 +0100, Roger wrote:
>
>
> That is something I hadn't thought about - saving the sets in records
> I was just think of using the 4D commands Save and Load sets
> How do you actually save a set into a text field?
**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Arnaud de Montard]

Le 2 juil. 08 à 20:00, Roger a écrit :

> That is something I hadn't thought about - saving the sets in records
> I was just think of using the 4D commands Save and Load sets
> How do you actually save a set into a text field?

Hi,
yes, you can do it this way:
        SAVE SET(set;document)
        DOCUMENT TO BLOB(document;blob)
        ...
but that way doesn't need document:
        BOOLEAN ARRAY FROM SET(array;set)
        VARIABLE TO BLOB (array;blob)

Using sets, however, may be a problem if a set's record is deleted:  
next added record will belong to the set. For this reason I prefer to  
blob array of ID's.

--
Arnaud de Montard



**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Ortwin Zillgen]

> So as an alternative I can think of 2 options
> A use global sets
> B use a separate selection table
>
> The selection must be available to any workstation and even web  
> service
> calls. I thought I would pose the question here to see what others do.


- every record has an unique ID (Longint in my case and unique for the  
database; alphas will work, too)
- there is a selections-table with a blob-field to hold
- SELECTION TO ARRAY([Table]Unique_Field;array)
- when I need that selection (because it was saved by the user or for  
inter-process-communication)
- QUERY WITH ARRAY([Table]Unique_Field;array) rebuilds the selection
  works like a charm
+ no sets stinking from the head after a couple of days
+ no data outside of 4D nor beyond 4D Backup
+ fast


Regards
Ortwin Zillgen
___________________________________________________
Stadt Land Fluß * Geographic Informationsystems
<mailto:dddd@...>   <http://dddd.mettre.de?4713>
<http://dddd.mettre.de/geodddd.shtml?4714>


**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Jack des Bouillons]

Array Longint($TBL_Record_aL;0)
Longint array from Selection([Table];$TBL_Record_aL)

Save the array in a blob

Do something with the blob (to a record or disk)

AT some future point...

Load the blob

Get the array

Then..

Array Longint($TBL_Record_aL;0)
Create selection from array([Table];$TBL_Record_aL)


This will be much faster than  Selection to array and a Query.

BTW..the advantage of using the record number array (as opposed to just a
set is that you send and get an ORDERED selection of records.

Also, there is a 4D tech note (now in the public domain) that provides the
equivalents of INTERSECTION, UNION and DIFFERENCE when dealing with record
number arrays rather than SETS.  It is well worth looking at.

Jack des Bouillons




On 7/2/08 1:13 PM, "Ortwin Zillgen" <info@...> wrote:

> - every record has an unique ID (Longint in my case and unique for the
> database; alphas will work, too)
> - there is a selections-table with a blob-field to hold
> - SELECTION TO ARRAY([Table]Unique_Field;array)
> - when I need that selection (because it was saved by the user or for
> inter-process-communication)
> - QUERY WITH ARRAY([Table]Unique_Field;array) rebuilds the selection
>   works like a charm
> + no sets stinking from the head after a couple of days
> + no data outside of 4D nor beyond 4D Backup
> + fast

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Roger Ayre]

Thanks Ortwin and Jack
These are suggestions are very helpful

I tend to favour using my unique id rather than record numbers, becuase
of reported problems with record numbers being re-used. It seems query
with array doesn't worry if it cannot find a particular id, so if a
record were deleted the selection would be ok.
This is going to be so much quicker and so simple
Great and thanks guys

Roger

Jack des Bouillons wrote:

> Array Longint($TBL_Record_aL;0)
> Longint array from Selection([Table];$TBL_Record_aL)
>
> Save the array in a blob
>
> Do something with the blob (to a record or disk)
>
> AT some future point...
>
> Load the blob
>
> Get the array
>
> Then..
>
> Array Longint($TBL_Record_aL;0)
> Create selection from array([Table];$TBL_Record_aL)
>
>
> This will be much faster than  Selection to array and a Query.
>
> BTW..the advantage of using the record number array (as opposed to just a
> set is that you send and get an ORDERED selection of records.
>
> Also, there is a 4D tech note (now in the public domain) that provides the
> equivalents of INTERSECTION, UNION and DIFFERENCE when dealing with record
> number arrays rather than SETS.  It is well worth looking at.
>
> Jack des Bouillons
>
>
>
>
> On 7/2/08 1:13 PM, "Ortwin Zillgen" <info@...> wrote:
>
>  
>> - every record has an unique ID (Longint in my case and unique for the
>> database; alphas will work, too)
>> - there is a selections-table with a blob-field to hold
>> - SELECTION TO ARRAY([Table]Unique_Field;array)
>> - when I need that selection (because it was saved by the user or for
>> inter-process-communication)
>> - QUERY WITH ARRAY([Table]Unique_Field;array) rebuilds the selection
>>   works like a charm
>> + no sets stinking from the head after a couple of days
>> + no data outside of 4D nor beyond 4D Backup
>> + fast
>>    
>
>  

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Jody Bevan]

Roger:

Think of record numbers like sets. Though the record number is very  
good, until a compact or recover by tags is done. Then they will be  
wrong.

Jody


On Jul 3, 2008, at 4:05 AM, Roger wrote:

> I tend to favour using my unique id rather than record numbers,  
> becuase of reported problems with record numbers being re-used. It  
> seems query with array doesn't worry if it cannot find a particular  
> id, so if a record were deleted the selection would be ok.
> This is going to be so much quicker and so simple
> Great and thanks guys
>
> Roger



Developers of JonokeMed™, FaxButler™, ImageButler™, and WebButler™

Jody Bevan                            Jonoke Software Dev. Inc.

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Peter Jakobsson-2]

On 3 Jul 2008, at 17:00, Jody Bevan wrote:

> Think of record numbers like sets. Though the record number is very  
> good, until a compact or recover by tags is done. Then they will be  
> wrong.

Jody -

Is it the case that 4D will re-use a deleted record number ? I wasn't  
sure about this and haven't got round to experimenting yet.

I've been thinking about this problem for a few years and concluded  
that Jack's solution is in a different league as far as performance  
is concerned (basically because it's scaleable and doesn't require  
any querying to reconstruct the current selection). The catch, as has  
been pointed out, is that the record numbers have limited validity.

They have improved at least since v3 as I understand because they  
will survive a dat file rebuild.

Do you have anything to say about his Jack ? How do you deal with  
deleted record ?

IMHO, subfiles really were the business when it came to solving this  
problem - at least as far as the query side goes (as opposed to  
flagging) - but I think this is one of the very few areas where we've  
lost a bit of functionality with v11 (unless I've missed something).

Peter

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Jack des Bouillons]

JPR's dictum applies:  Know your data...

It's the basis of any design choice.  Obviously, if you had a table where it
was common to create and delete records on a regular basis, using record
numbers may not be the bests choice.

However, I would argue that using SETS (or running a Query with Array based
on a saved array of Primary Keys) is not a good choice either under such
circumstances.  Yes, there will be no error, as in the case of using an
array of Record Numbers..BUT...the returned selection may still invalid.

What is a saved set (or selection) anyway?  ...The user is saying, in
effect, I want to work with THESE records at some point in the future.  If
some of those records are deleted, and do not appear when called again, that
is a "business process error"...it is NOT the same selection  Perhaps in
some cases, this makes no difference.  In other cases it might.

Depending, again, on the nature of the system and what you are trying to
"save" you can handle an update of the saved "sets"...Say you want to save
all the records in a Contacts table by State.  So, in your Saved Sets table,
you would have 50 state records, each tagged in some way by State.  You
could update this once a night when people were not on the system.  IF it
were important to have the records accurate up to the moment, put a call in
the SAVE and DELETE triggers:  If State value is modified, kick of a new
process passing the state and record ID*.  Load the appropriate record, and
update the array (adding or deleting)...

Here in our system, saved sets are transient in nature:  users typically
create sets to use for a few days..maybe a week..rarely more than a month.
In the main tables (Buyers & Offices) I have implemented a feature like that
described above.  On those tables, I have never run into a problem. On rare
occasions, in other tables, an error has been thrown because a record got
deleted.

Jack des Bouillons

*Actually, you would have to pass the PRIMARY KEY, NOT the record
number..because record number is not assigned to a new record until the
trigger completes.  So in the new processes, you need to do a lookup to get
the record number based on the Primary Key.  (Don't use Query...Just Find
Index Key...it's faster...)






On 7/3/08 10:41 AM, "Peter Jakobsson" <lists@...> wrote:

> Do you have anything to say about his Jack ? How do you deal with
> deleted record ?



**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Arnaud de Montard]

Le 3 juil. 08 à 19:41, Peter Jakobsson a écrit :

> Is it the case that 4D will re-use a deleted record number ? I  
> wasn't sure about this and haven't got round to experimenting yet.


it does; a table looks like seats in a theater. On the same seat, one  
person goes (delete), another enters (create). Sets and record number  
are seat numbers, while unique ID's are person numbers.

So, sets and record numbers (CREATE SELECTION FROM ARRAY, for example)  
commands are faster, while SELECTION TO ARRAY / QUERY WITH ARRAY are  
more reliable for long life storage.

--
Arnaud de Montard



**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Jack des Bouillons]

I just don't agree with that as a blanket statement...

If it is MISSION-CRITICAL for me to be able to access a specific selection
of records in the future, should any of those records be deleted in the
meantime, when I try to retrieve them I will not have the same selection...

Now, with the case of an array of records, I will have records in my
selection that were not part of the original set.  But with a selection of
PKs I will be missing records.

BOTH are invalid under the assumption that I needed to have a specific
selection of records to retrieve.

I will give you that the SELECTION approach is safer, as the user would
notice that the selection now was smaller.  But suppose your system HAS NO
USER..at least in the sense of someone at a keyboard looking at the data.
Suppose it is an automated accounting function that is generating a summary
report?

I would argue that in most cases, if record deletion is a concern, the safer
approach would not be to use SAVED SETS (or SELECTIONS) at all, but build a
stored query that could be executed as needed.

 Jack des Bouillons
Hobbs Herder Advertising


On 7/3/08 12:17 PM, "arnaud" <arnaud@...> wrote:

> while SELECTION TO ARRAY / QUERY WITH ARRAY are
> more reliable for long life storage.



**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Peter Jakobsson-2]

On 3 Jul 2008, at 21:17, arnaud wrote:

> it does; a table looks like seats in a theater. On the same seat,  
> one person goes (delete), another enters (create). Sets and record  
> number are seat numbers, while unique ID's are person numbers.

Thanks Arnaud.

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Roger Ayre]

Just want to thank every one for their suggestions
I have now implement a solution using a separate selections table and
query with array and the results are brilliant.

One big bonus is that I no longer need to worry about record locking.

I need to highlight records on the output screen which are part of a
selection, and I was worried that this might be slower, but I cannot
notice any change.

As for the discussion about deleting records which are in the selection.
If a selected record should be deleted then it can nolong be part of the
selection. To keep the selection valid each time the selection is loaded
I check that number or records match the size of the saved array, and
save the new selection if it is different. This way my selection is
always valid.
It seems to me that *if you allow record deletion* then keeping the
selection relavent is just good house keeping.
Thanks
Roger
**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************

[Peter Jakobsson-2]

On 5 Jul 2008, at 11:45, Roger wrote:

> I need to highlight records on the output screen which are part of  
> a selection, and I was worried that this might be slower, but I  
> cannot notice any change.

Roger,

I take it your storing the selections as a list of primary keys in a  
different table, then using SELECTION TO ARRAY followed by QUERY WITH  
ARRAY.

Just out of interest, how big are the biggest selections you are  
restoring using QUERY WITH ARRAY ?

>
> As for the discussion about deleting records which are in the  
> selection. If a selected record should be deleted then it can  
> nolong be part of the selection.

Just a suggestion - you could implement a little trigger code in the  
'On Delete Record' event to simple knock out the items from the index  
table as master records are deleted. This would keep your selections  
nice and tidy and is only about 5 lines of code.

You could also maintain the table in the trigger by creating the keys  
in the the 'On Saving New Record' event, thereby 'bulletproofing'  
your solution.

Peter

**********************************************************************
4D Server v11 SQL has arrived!
Buy it NOW at http://store.4ddepot.com

4th Dimension Internet Users Group (4D iNUG)
FAQ:  http://www.4d.com/support/faqnug.html
Archive:  http://dir.gmane.org/gmane.comp.lang.inug-4d.tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
Post: mailto:4d_tech@...
Options: https://lists.4d.com/mailman/listinfo/4d_tech
**********************************************************************