Re: Object fields (Was: Re: QUERY BY ATTRIBUTE is slow af)

[John Baughman via 4D_Tech]

In my mind a variable is the same thing as a key value pair. The variable name 
is the key and the data the variable contains is the value. I name all 
variables with a user readable name so what is the difference between a 
variable named vFirstName that contains “John” and oObj.firstname that equals 
“John”.  

If (vFirstName=“John”)   0rIf (oObj.firstname =“John”)

In this vein, I am looking at any blob field that contains an ObjectTools 
object, as a potential candidate for replacement with a c_object field. I say 
looking at, as I am not sure it is worth the effort to replace existing code 
unless I decide to drop the use of the OT plugin.

I am thinking going forward, however, that in a process where I know I need to 
store “state” for revisits, I can use an object field instead of a blob field 
with OT object. Hey, instead of using variables at all,  why not use a c_object 
from the get go. With dot notation it becomes really easy to work with the 
c_object just like you would a variable. Store the c_object in the database 
when the process closes and retrieve it when the process is revisited.

John




> On Sep 12, 2017, at 9:27 PM, David Adams via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
>> Here´s my real-world use:
>> I wrote an iPhone app that collects data (scans barcodes).
>> That data is in JSON format and transferred via HTTP to a 4D Server.
>> The server stores that data in an object field (which is never queried. It
>> just holds the collected data).
> 
> That's a great example of a classic use for a JSON field, by whatever name.
> (And one of the reasons I would like compression options for JSON fields in
> 4D.) This an example that I'd file under "storing logging or API result
> data", something that makes sense to me for a doc field, if you need the
> data in the database for some reason.
> 
> By default, I don't want most logging data in 4D, but do when it's for
> auditing/troubleshooting. I like text files pushed into a log analyzer. I
> should be working on that again shortly, actually.
> 
>> The same would work with a text- or blob-field to store the data, but an
>> object field makes it a bit easier.
> 
> Great example, thanks for sharing.
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **

John Baughman
Kailua, Hawaii
(808) 262-0328
john...@hawaii.rr.com





**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Object fields (Was: Re: QUERY BY ATTRIBUTE is slow af)

[Herr Alexander Heintz via 4D_Tech]

Hi Bob,

i will tell a little story that aligns perfectly with what you told:

I have a huge database system for managing car spare parts. 
each part can be of a specific type, thus having specific descriptive criteria
each part can be linked to any number of vehicles, sometimes with a specific 
set of limiting criteria (age range, chassis numbers, etc)

articles are categorized using what we call a generic article (you might call 
it a template) and for this article type there is a definition wich criteria 
need to be filled and wich ones are optional, also if criteria have a defined 
enumeration list, it can be limited for that specific use.

we have long ago decided against using any kind of object for setting these 
criteria, as we loose flexibility:

Finding articles based on generic article and criterium is quite fast in 
standard tables, but when it comes to mass application of criteria or mass 
modifications a separate table is much more flexible, also on im- and export, 
data aggregation, reporting, etc. etc..

The data structure is quite complex, granted, but I have yet to find a 
compelling reason to use anything else, as there is nothing I cannot do with a 
separate table. 

And that is my main argument: IMHO it only makes sense to switch to another 
tool if there is a distinct benefit, and that is something I do not see yet, on 
the contrary, there are a lot of things i cannot do easily when using object 
fields.

So for now, until a compelling reason comes along, I use objects only as a kind 
of „attic“ for the record, keeping complex data I only need from time to time 
(audit trail, source file references, record history, etc. etc.) and that never 
will be used for any business logic.

OTOH I love c_objects variables in the language and make heavy use of these, 
they allow for a much cleaner approach to development (from passing parameters, 
to replacing process variables, storing form object settings, etc. etc.).

Hope this helped, available for further questions, also by PM.

Cheers
Alex
  





> Am 13.09.2017 um 16:28 schrieb bob.miller--- via 4D_Tech 
> <4d_tech@lists.4d.com>:
> 
> Along the lines of, "what is an appropriate use of an object field", I've 
> been considering this:
> 
> We are a manufacturing company with lots of tools and lots of products. 
> Each tool has various attributes, but each tool type has a different set 
> of characteristics. 
> 
> Silly example that I hope everyone can follow: if the tool type is 
> "screwdriver" then the attributes might be:
> 
> Tip Type (blade, Phillips, Torx, etc.)
> Tip Size (#1 or #2)
> Shank length (in mm)
> Handle diameter (in mm)
> 
> 
> ...If the tool type, however, is "hammer" then the attributes might be:
> 
> Head type (claw, peen, sledge, etc.)
> Claw shape
> Head weight
> Shank material
> Shank length
> Grip Type
> ...and many more
> 
> 
> Common to all tools, regardless of tool type are attributes such as:
> 
> Tool ID
> Tool Type - which determines the format and contents of the object field 
> that contains the attributes
> Storage Location
> Purchase Date
> Condition (good, fair, poor, unusable)
> Status (ready to use, in use, lost)
> 
> 
> 90% of the time users want to enter a Tool ID to retrieve the Storage 
> Location, Condition, and Status.  10% of the time they know the tool type 
> but don't know the tool ID and want to query for a tool to do the job, 
> using one or several attributes.  I've been thinking that because there 
> are so many tool types and so many attribute "collections", an object 
> field might be a good place to store the attribute data in name-value 
> pairs.  This way I don't have to have a table with every single attribute 
> name, which would be a problem because I don't know them in advance (users 
> want to be able to specify the attributes themselves, along with the 
> attribute value of course) and many tools do not share many attributes, so 
> a big mostly-empty record would result if I used fields.
> 
> The object field on a record with Tool Type = "screwdriver" would contain 
> attributes for a screwdriver, while the object field on a record with a 
> different Tool Type would contain a different set of attributes.  A table 
> for "Tool Type" would have one record for each tool type and could also 
> use an object field to contain the attribute names, since this would be 
> more handy than having multiple records for each tool type, one for each 
> attribute name.  The normalized, relational model isn't really needed 
> here, though of course it could be used.
> 
> An object field seems ideal for this kind of application, and 'Query by 
> Attribute' certainly makes it possible.
> 
> There are always LOTS of ways to solve a problem, but do you agree that an 
> object field is a good way to solve this one?  If it is a mediocre way to 
> solve it, I'd like to hear your comments!
> 
> 
> Bob Miller
> Chomerics, a division of Parker Hannifin Corporation 
> 
> 
> 
> 

Object fields (Was: Re: QUERY BY ATTRIBUTE is slow af)

[bob.miller--- via 4D_Tech]

Along the lines of, "what is an appropriate use of an object field", I've 
been considering this:

We are a manufacturing company with lots of tools and lots of products. 
Each tool has various attributes, but each tool type has a different set 
of characteristics. 

Silly example that I hope everyone can follow: if the tool type is 
"screwdriver" then the attributes might be:

Tip Type (blade, Phillips, Torx, etc.)
Tip Size (#1 or #2)
Shank length (in mm)
Handle diameter (in mm)


...If the tool type, however, is "hammer" then the attributes might be:

Head type (claw, peen, sledge, etc.)
Claw shape
Head weight
Shank material
Shank length
Grip Type
...and many more


Common to all tools, regardless of tool type are attributes such as:

Tool ID
Tool Type - which determines the format and contents of the object field 
that contains the attributes
Storage Location
Purchase Date
Condition (good, fair, poor, unusable)
Status (ready to use, in use, lost)


90% of the time users want to enter a Tool ID to retrieve the Storage 
Location, Condition, and Status.  10% of the time they know the tool type 
but don't know the tool ID and want to query for a tool to do the job, 
using one or several attributes.  I've been thinking that because there 
are so many tool types and so many attribute "collections", an object 
field might be a good place to store the attribute data in name-value 
pairs.  This way I don't have to have a table with every single attribute 
name, which would be a problem because I don't know them in advance (users 
want to be able to specify the attributes themselves, along with the 
attribute value of course) and many tools do not share many attributes, so 
a big mostly-empty record would result if I used fields.

The object field on a record with Tool Type = "screwdriver" would contain 
attributes for a screwdriver, while the object field on a record with a 
different Tool Type would contain a different set of attributes.  A table 
for "Tool Type" would have one record for each tool type and could also 
use an object field to contain the attribute names, since this would be 
more handy than having multiple records for each tool type, one for each 
attribute name.  The normalized, relational model isn't really needed 
here, though of course it could be used.

An object field seems ideal for this kind of application, and 'Query by 
Attribute' certainly makes it possible.

There are always LOTS of ways to solve a problem, but do you agree that an 
object field is a good way to solve this one?  If it is a mediocre way to 
solve it, I'd like to hear your comments!


Bob Miller
Chomerics, a division of Parker Hannifin Corporation 



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: QUERY BY ATTRIBUTE is slow af

[Epperlein, Lutz (agendo) via 4D_Tech]

Since the feature of object fields is very near the concept of NoSQL databases 
like MongoDB and so on (at least it is partly stolen from their) ... 
I think the following article by Sarah Mei summarizes the discussion in the 
this thread in a way: 
http://www.sarahmei.com/blog/2013/11/11/why-you-should-never-use-mongodb/

Regards
Lutz

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Peter Bozek via 4D_Tech]

On Tue, Sep 12, 2017 at 11:07 PM, Jeffrey Kain via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> Yeah, so many caveats to object fields. Bite the bullet, use a related
> table...
>
>
Well, I  wrote that i am playing with it, Testing how it can be used, what
commands are available and where are limitation, and keeping eye on new
development. I am looking at object fields not as a JSON structures, but as
a name value pairs.

I want to test the idea of keeping denormalized fields in object (but not
necessarily only denormalized fields), and building some simple way to
maintain, show and search them. Now sure how it will work, maybe not well,
but without testing I will not know. I see few advantages: while I keep
some rules how to name fields in structure, I can (or plan to) give object
fields more user friendly names. I will have more flexibility to add /
remove / rename fields. I will have to adjust search and sort dialogues,
and I plan to do a simple ones using just one object field.

--

Peter Bozek
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: Object fields (Was: Re: QUERY BY ATTRIBUTE is slow af)

[David Adams via 4D_Tech]

> Here´s my real-world use:
> I wrote an iPhone app that collects data (scans barcodes).
> That data is in JSON format and transferred via HTTP to a 4D Server.
> The server stores that data in an object field (which is never queried. It
> just holds the collected data).

That's a great example of a classic use for a JSON field, by whatever name.
(And one of the reasons I would like compression options for JSON fields in
4D.) This an example that I'd file under "storing logging or API result
data", something that makes sense to me for a doc field, if you need the
data in the database for some reason.

By default, I don't want most logging data in 4D, but do when it's for
auditing/troubleshooting. I like text files pushed into a log analyzer. I
should be working on that again shortly, actually.

> The same would work with a text- or blob-field to store the data, but an
> object field makes it a bit easier.

Great example, thanks for sharing.
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Object fields (Was: Re: QUERY BY ATTRIBUTE is slow af)

[Bernd Fröhlich via 4D_Tech]

David Adams:

> I've been asking about real-world use of object fields for quite some time

Here´s my real-world use:
I wrote an iPhone app that collects data (scans barcodes).
That data is in JSON format and transferred via HTTP to a 4D Server.
The server stores that data in an object field (which is never queried. It just 
holds the collected data).
The beauty is: when I need to send additional data that I did not think of 
while writing the app, I just stuff that data into the JSON along with the rest.
Of course the parsing routine on 4D Server has to be tweaked to make use of the 
additional data, but that´s all that is needed.
I am quite happy with this approach.
The same would work with a text- or blob-field to store the data, but an object 
field makes it a bit easier.

Greetings from Germany,
Bernd Fröhlich
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[John DeSoi via 4D_Tech]

> On Sep 12, 2017, at 5:48 PM, David Adams via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Exciting stuff. I've read a ton about it and done some tests. Rob's plugin
> doesn't have an explicit command for transferring object fields, but you
> can stringify them and send them as text.

Most Postgres client libraries use text rather than binary encoding because the 
binary formats are not documented. Of course, you have the source but that is a 
lot of work :). But the binary option is especially fast and useful when you 
are sending/receiving binary, aka Postgres bytea. 

https://stackoverflow.com/questions/35600070/postgres-jsonb-specification-for-copy-in-binary-format


> If you have exotic searching requirements, Postgres has a ton of
> engine-level features...and a common add-on gives you a bunch of fuzzy
> matching tools. I was talking with John DeSoi the other day and he says
> that the fuzzy tools add-on includes "tri-grams". That's "n-grams" or
> "q-grams" with a length of 3. I've got a stack of research papers on why
> trigrams are so frickin amazing at fuzzy matching. I've tried (and failed)
> to implement them effectively in 4D a couple of times. WIth Postgres, you
> just add the fuzzy tools and away you go.

Here are a couple of links if anyone is interested.

https://www.postgresql.org/docs/9.6/static/pgtrgm.html

https://www.postgresql.org/docs/9.6/static/textsearch.html

John DeSoi, Ph.D.


**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[David Adams via 4D_Tech]

On Wed, Sep 13, 2017 at 9:25 AM, Tim Nevels via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> On Sep 12, 2017, at 5:49 PM, Jeffrey Kain wrote:
>
> > Yeah, so many caveats to object fields. Bite the bullet, use a related
> table...
>
> Remember 4D’s implementation strategy for new features:
>
> - You get a little bit at a time.
> - The feature set is not complete.
> - The public (i.e. 4D developers) are not provided with any information
> about what the completed feature set will ultimately look like.
>

Nicely summarized.

About 20 years ago, I decided "I won't base any of my choices on software
that isn't shipping." (Fool me 18 times, shame on you, fool me 19 times,
shame on me.)  I might bend that a bit for a company with a detailed
roadmap, and a really solid record of delivering on that roadmap. Even
then, I wouldn't roll the dice on something that would be a huge disaster
if it didn't ship on time or with the feature as expected. I've never, ever
regretted this choice. This isn't specifically about 4D by any means, but
they aren't excepted from the rule either.

With 4D, we have no road map so the *only* features and behaviors we can
plan on are the ones in our hands *today.* That's fine, most of the time I
only want to know how things work now so that I can make plans. 4D is under
no obligation to provide whatever pet feature requests any of us have.
Clearly. Still, when I hear about "future versions" I file them under "who
cares?" I mean, it's fun to think about - but it does nothing for my work
today and does nothing to my plans.

It's like when the medical world says that something will be a viable
treatment in "5-10" years. That's pure science fiction, they have no idea
how it will play out when something is that far out. So, fun to think
about, but not useful for making choices.

So, yeah, object fields are already useful in a limited number of cases,
IMO, and may evolve into something far more powerful and useful. Let's hope
so! But for now, when I need more exotic/performant/scalable features,
there's Postgres where all of this stuff already has years of field use.
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Tim Nevels via 4D_Tech]

On Sep 12, 2017, at 5:49 PM, Jeffrey Kain wrote:

> Yeah, so many caveats to object fields. Bite the bullet, use a related 
> table...

Remember 4D’s implementation strategy for new features:

- You get a little bit at a time. 
- The feature set is not complete.
- The public (i.e. 4D developers) are not provided with any information about 
what the completed feature set will ultimately look like. 

It’s quite possible 4D Engineering doesn’t even know what the full feature set 
of object fields will be. Could be they just keep adding features based on what 
developers say we need, or maybe what 4D Engineering “thinks” a developer might 
need or find useful. 

Add to this the fact that 4D Engineers are not, and have never been, 4D 
developers. They don’t really use the thing that they are creating. 

Don’t get me wrong, I know 4D Inc. uses 4D internally for a lot of things. So 
4D Inc. does eat their own dog food. But the guys in the kitchen making the dog 
food never really taste it. They cook it and then send it off for others to eat 
and wait to hear how good it tastes. And if it need more salt. Or if it is too 
spicy. You know what I’m saying.

Tim


Tim Nevels
Innovative Solutions
785-749-3444
timnev...@mac.com


**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[David Adams via 4D_Tech]

That's a good article, if you want more - ping me. And that's exactly the
idea I was referring to when I said I'd be willing to learn some new
operators if it bought me a lot. And, since I tend to write like I know
what I'm talking about no matter if I do or don't ;-) At the least, John
DeSoi and Jim Crate on this list seem to *really* know what they're talking
about with regards to Postgres.

Yeah, Postgres has several ways of dealing with JSON. You can use a plain
JSON field if all you want to do is pipeline or store some JSON for some
reason (pre-rendered values for pulls, logged API calls for review, etc.)
JSONB is a lot different and takes advantage of the GIN index. From the
sound of it, the GIN index is the real genius of the JSONB type. GIN has
been in development for over a decade and helps provide Solr-like search
features. (Finding words based on how near they are to other words, for
example.)

For space, JSON fields are smaller than comparable 4D object fields because
they don't default to UTF16 and can be configured to use a much smaller
collation. Assuming you can it your data into a smaller character
encoding/set/whatever you call it. JSONB isn't space-optimized by default,
but you can get an add-on to improve storage by a factor of roughly 6:1.
But, really, the reason to use JSONB instead of JSON is for the searching.
If you just need to store JSON, a JSON field is fine. (A text field is also
fine, but a JSON field gives you database-level validation that the string
*is* JSON.)

Exciting stuff. I've read a ton about it and done some tests. Rob's plugin
doesn't have an explicit command for transferring object fields, but you
can stringify them and send them as text. The default rules for escaping
text in Postgres are easy:

-- Convert single quotes into two single quotes.
-- Put single quotes around the whole block.

If you have exotic searching requirements, Postgres has a ton of
engine-level features...and a common add-on gives you a bunch of fuzzy
matching tools. I was talking with John DeSoi the other day and he says
that the fuzzy tools add-on includes "tri-grams". That's "n-grams" or
"q-grams" with a length of 3. I've got a stack of research papers on why
trigrams are so frickin amazing at fuzzy matching. I've tried (and failed)
to implement them effectively in 4D a couple of times. WIth Postgres, you
just add the fuzzy tools and away you go.
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Jeffrey Kain via 4D_Tech]

I agree.

I had just read this article the other day, however, and was hoping (expecting) 
that 4D would provide something similar to Postgres JSONB and GIN indexes:

https://compose.com/articles/is-postgresql-your-next-json-database/

--
Jeffrey Kain
jeffrey.k...@gmail.com

> On Sep 12, 2017, at 5:39 PM, David Adams via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Seriously people, 4D's hostility to the relational model has been around
> since longer than I have, and it makes no sense. The beauty of normalized
> data is that you can easily answer *unanticipated questions.* Also, you can
> store your information in a way that's easy to keep in order. The problem
> with subtables wasn't just the implementation of the idea, it was the idea
> itself. Object fields for relational data? Much the same.

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[David Adams via 4D_Tech]

Jeff,

Well, thanks for trying to be the cranky old man instead of me. But just
won't do. Now I have to rant.

My skepticism regarding using object fields for the purposes should be
pretty obvious to anyone that's paid attention.

Regarding the Summit demo...I watched the video and thought that it was
very well presented and that it took a ton of work to do. I also think that
Olivier is a very nice person personally and professionally. It was a pity
that there was effectively no Q & A. Two points from the presentation:

1) The "optimization" from the object field depended on knowing the *exact*
question(s) that you wanted answered in advance. Unanticipated question?
Then the object search might end up being sequential.

2) The overall thrust of the demo seemed to be to show that object fields
represent some kind of a sweet spot between a one table, one field database
and a fully normalized database. The claim being that full normalization is
slower and harder to do. Hmmm. Didn't make sense. I downloaded the demo,
looked at the relational joining code, rewrote it to something more
sensibleand the so-called speed "optimization" from object fields went
away. I only spent two minutes on the code, so perhaps I did something
horribly wrong? Could be, doubt it.

I've been asking about real-world use of object fields for quite some time
and also pushing questions and ideas towards 4D. I still don't get it.

If you're thinking about using object fields (think "subtable without a
fixed schema") ask yourself, "why?" What is it buying you? A lot of uses
I've seen are to solve problems that don't exist in a normalized table, but
do exist in a denormalized table that uses full fields. So, a lot of it
sounds to me like taking badly designed tables and redoing them into badly
design tables.

My hope for object fields was not to use them for user-facing, normalized
tables but to use them for a couple of other classes of table:

* Programmer data where you're storing what amount to manifests or
parameter blocks. So, job definitions that you pass around for execution,
logging data for forwarding to the outside world, preferences and
configuration documents of various sorts.

* Pre-built JSON blocks to pass to the outside world. In this case, 4D is
part of a pipeline where data is summarized/calculated and stored ready for
export to some other system (like a visualization platform) or ready to be
pulled by some other system (like a JavaScript that runs some kind of
rendering.) 4D told me that this is "wrong" because if I have a fixed
schema, I should use a full table. Okay, so 4D is *not* trying to build a
NoSQL/doc store system. Because if they were, then what I've described is
about as normal a use as you can come up with for those systems.

* Fine, object fields aren't meant for storing JSON blocks that have a
regular format. Well, that's probably just an opinion of some (important)
people at 4D. In practice, they're just not efficient for it as the object
takes almost exactly the amount of space required for the JSON
serialization in UTF16...which takes a ton of space, often for no purpose.

* So what are object fields for? From what 4D said in response to me, it
sounds like they're for when you have data that has an irregular format.
Hmmm. I don't have a lot of that apart from rendered JSON, logging data
(when there are a mixture of logs), or internal messages.

* Whatever problems you had with subtables, expect comparable problems with
object fields.

* I built a 1.75 million row table with an object field and did some
experiments. I couldn't find any speed advantage to using an object query
versus placing values in a field and querying there. Then again, I could
also index the object field and get the same query there, which was cool.
That seems promising. Then again, I have to use a different query command
to do this. I couldn't find any advantage to the object field per se, so
why not use a regular field/ Can't come up with a reason. I mean, I would
be willing to use a different set of commands and/or operators for some
benefit (proximity/fuzzy text searches as an obvious example, or a big
speed boost)but for no advantage? Why make the code harder.

If 4D offered some compression and/or collation options on object fields,
I'd use them for pipelining JSON. As it stands, they don't and seem
unresponsive to the suggestion.

I can absolutely see using object fields for internal messaging and prefs,
as mentioned.

Object fields for user data? While there are doubtlessly cases where this
is tempting, only  a few of those are likely to make sense. There are a few
situations where the relational model just pinches your toes. (When you
have "kind of" relations with a small number of categories.) They happen in
any big structure, but they should be uncommon. I get a bit of a tight neck
when I run into one...They're an edge case. Real, but not standard.

Using object fields for the wholesale replacement of normal 

Re: QUERY BY ATTRIBUTE is slow af

[Jeffrey Kain via 4D_Tech]

Yeah, so many caveats to object fields. Bite the bullet, use a related table...

--
Jeffrey Kain
jeffrey.k...@gmail.com

> On Sep 12, 2017, at 5:03 PM, David Adams via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
>> I am playing with idea to use object for values that are used only for
>> display / searching. I have structures with tables with tens of such fields
>> - mostly comments, flags or user defined fields. This would simplify
>> structure a bit. It would be nice if 4D provided some simple way how to
>> export / print values from objects (like supporting dot notation in source
>> field property.)
>> 
> 
> They do! It's called "use a related table."

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[David Adams via 4D_Tech]

On Wed, Sep 13, 2017 at 3:22 AM, Peter Bozek via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> On Tue, Sep 12, 2017 at 7:10 PM, Herr Alexander Heintz via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
>
>
>
> I am playing with idea to use object for values that are used only for
> display / searching. I have structures with tables with tens of such fields
> - mostly comments, flags or user defined fields. This would simplify
> structure a bit. It would be nice if 4D provided some simple way how to
> export / print values from objects (like supporting dot notation in source
> field property.)
>

They do! It's called "use a related table."
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Peter Bozek via 4D_Tech]

On Tue, Sep 12, 2017 at 7:10 PM, Herr Alexander Heintz via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> Absolutely.
> I exclusively use object fields for additional data and storage of
> connected information to a specific data row, such as record and field
> history, specific item settings etc.
> All of these will NOT be queried on.
> There are cases where I use object fields for variable data structures,
> but limit this to basic 1-level objects that can be queried rapidly.
> Also bear in mind, that accessing data embedded in objects is far more
> complicated (for example for mass editing) than using a related table. It
> might sound like an intriguing concept to store, say keywords, in an object
> array, but even if these were quick to query, modifying a bunch a of
> keywords is far more complicated when they are stored in objects than just
> using an APPLY TO SELECTION for a „normal“ related table.
> Just my 2 ct, your mileage may vary
>


I am playing with idea to use object for values that are used only for
display / searching. I have structures with tables with tens of such fields
- mostly comments, flags or user defined fields. This would simplify
structure a bit. It would be nice if 4D provided some simple way how to
export / print values from objects (like supporting dot notation in source
field property.)

--

Peter Bozek
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Herr Alexander Heintz via 4D_Tech]

Absolutely.
I exclusively use object fields for additional data and storage of connected 
information to a specific data row, such as record and field history, specific 
item settings etc.
All of these will NOT be queried on.
There are cases where I use object fields for variable data structures, but 
limit this to basic 1-level objects that can be queried rapidly.
Also bear in mind, that accessing data embedded in objects is far more 
complicated (for example for mass editing) than using a related table. It might 
sound like an intriguing concept to store, say keywords, in an object array, 
but even if these were quick to query, modifying a bunch a of keywords is far 
more complicated when they are stored in objects than just using an APPLY TO 
SELECTION for a „normal“ related table.
Just my 2 ct, your mileage may vary


> Am 12.09.2017 um 18:58 schrieb Jeffrey Kain via 4D_Tech 
> <4d_tech@lists.4d.com>:
> 
> So disappointing.

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Jeffrey Kain via 4D_Tech]

So disappointing.

--
Jeffrey Kain
jeffrey.k...@gmail.com

> On Sep 12, 2017, at 12:14 PM, Herr Alexander Heintz via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> Having worked with 4D since ages long forgotten, I would have to feel very 
> lucky and outright daring to even assume such a thing might work :-)

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Herr Alexander Heintz via 4D_Tech]

Am 12.09.2017 um 17:22 schrieb Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com>:
> 
> Trying to figure out why our experience with QUERY BY ATTRIBUTE is so 
> different than the demos at the last 4D Summit.
> 
> We're running 4D Server 16.2 64-bit on Windows. The Invoices table has about 
> 9 million rows, and we added an index to the Extra field in this table which 
> is an object field.
> 
> The following query always runs sequentially - i.e. very very slowly. 
> Thinking it might be a wildcard issue with the email we also tried strings 
> with no '@', but no matter what we do the query runs sequentially.
> 
> QUERY BY ATTRIBUTE \
>  ([Invoices];\
>   [Invoices]Extra;\
>   „gift_card_line_items[].to_email";=;"desig...@sweetwater.com")

If Memory serves me right: the demos at the summit only queried on first level 
object fields:

QUERY BY ATTRIBUTE ([table]; [table]object;“name“;=;“fred“)

You, sir, are trying to get to a second level value from an array

Having worked with 4D since ages long forgotten, I would have to feel very 
lucky and outright daring to even assume such a thing might work :-)

Not helpful I know….
Try searching for a first level direct value if you have such a thing.

Cheers
Alex
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Jeffrey Kain via 4D_Tech]

That was my first thought too, but I can change the query string to "Ralph" and 
it still does a sequential scan of the table, returning no rows.

--
Jeffrey Kain
jeffrey.k...@gmail.com


> On Sep 12, 2017, at 11:26 AM, Spencer Hinsdale via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> isn't there some trick to use a character code instead of at?  
> 
> and does the "beginning and end" pref possibly apply?

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: QUERY BY ATTRIBUTE is slow af

[Epperlein, Lutz (agendo) via 4D_Tech]

Maybe there is an issue with @ character in the search string ...
I've no idea how to circumvent it.

HTH
Regards
Lutz

> -Original Message-
> From: 4D_Tech [mailto:4d_tech-boun...@lists.4d.com] On Behalf Of Jeffrey Kain 
> via
> 4D_Tech
> Sent: Tuesday, September 12, 2017 5:23 PM
> To: 4d_tech@lists.4d.com
> Cc: Jeffrey Kain 
> Subject: QUERY BY ATTRIBUTE is slow af
> 
> Trying to figure out why our experience with QUERY BY ATTRIBUTE is so 
> different than
> the demos at the last 4D Summit.
> 
> We're running 4D Server 16.2 64-bit on Windows. The Invoices table has about 9
> million rows, and we added an index to the Extra field in this table which is 
> an object
> field.
> 
> The following query always runs sequentially - i.e. very very slowly. 
> Thinking it might be
> a wildcard issue with the email we also tried strings with no '@', but no 
> matter what we
> do the query runs sequentially.
> 
> QUERY BY ATTRIBUTE \
>   ([Invoices];\
>[Invoices]Extra;\
>"gift_card_line_items[].to_email";=;"desig...@sweetwater.com")
> 
> 
> What are we doing wrong here? The query returns one row, 20 minutes later.
> *
> *
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> *
> *
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: QUERY BY ATTRIBUTE is slow af

[Spencer Hinsdale via 4D_Tech]

isn't there some trick to use a character code instead of at?  

and does the "beginning and end" pref possibly apply?

> On Sep 12, 2017, at 8:22 AM, Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Trying to figure out why our experience with QUERY BY ATTRIBUTE is so 
> different than the demos at the last 4D Summit.
> 
> We're running 4D Server 16.2 64-bit on Windows. The Invoices table has about 
> 9 million rows, and we added an index to the Extra field in this table which 
> is an object field.
> 
> The following query always runs sequentially - i.e. very very slowly. 
> Thinking it might be a wildcard issue with the email we also tried strings 
> with no '@', but no matter what we do the query runs sequentially.
> 
> QUERY BY ATTRIBUTE \
>  ([Invoices];\
>   [Invoices]Extra;\
>   "gift_card_line_items[].to_email";=;"desig...@sweetwater.com")
> 
> 
> What are we doing wrong here? The query returns one row, 20 minutes later.
> **
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**