Select AS question

Sten-Erik Björling s-e.bjorling at enviro.se
Fri Nov 22 13:01:00 EST 2019


Dear John,

This is exactly what I wanted to know - many thanks!!

Stene

______

> 22 nov. 2019 kl. 18:07 skrev John Boehme <john at integralservices.biz>:
> 
> Hi Sten,
> 
> I had replied to the wrong message the other day so not sure that you noticed my response.
> 
> Not sure if I understand your question totally. Here is what I think
> applies:
> 
> We have these class objects that manage a record for a table, and, various connected records to that main record.
> When retrieving a "document" as we call it, we also load up the child records. This may result in many db requests to retrieve each of the child records for the main document.
> 
> With a local connection to the db, this is irrelevant. However, when connecting to a remote db, the delay for each request is very significant.
> The same <1s process on a local db may take 10-15, 30 seconds on a remote db just retrieving the child records.
> 
> For certain reports, where complete detail of an individual document is required, I tried this approach which seems to work pretty neat. I use a structure like this as a column calculation:
> 
>    ( SELECT string_agg(concat(n.note_date, '\t', n.note_text), '\~'::text) AS note_list
>           FROM notes n
>          WHERE (n.doc_uid = doc.doc_uid)
>          GROUP BY n.doc_uid) AS notes,
> 
> Where doc.doc_uid is the parent record key.
> 
> What this select does is build a delimited list of columns/records from the notes linked to a document. So for each document, one column has a list of all its notes. We actually have several columns pulled back with other child lists of the document we're retrieving.
> 
> When using joins, you'll get multiple records for the main record depending on the number of child records. With this approach, we get one record returned for each main record. All the child records for each record are in columns of the record. So only one db request to get everything.
> 
> To retrieve the data from the column, we run the data through a parser that translates the delimited records into a list:
> 
> Do $cinst.$getList(iNotesList,ivMainList.note_list)
> 
> Where ivMainList.note_list is a child list of a main record retrieve with the sql above, and upon return, the iNotesList is populated with the data from ivMainList.note_list.
> 
> A similar approach would be to use a json service, which would handle the data in pretty much in the same way except in a much a nicer format.
> 
> Good luck.
> 
> 
> John Boehme
> Integral Consulting Services
> Bothell, WA
> 940-597-3046
> 
> 
> -----Original Message-----
> From: omnisdev-en [mailto:omnisdev-en-bounces at lists.omnis-dev.com] On Behalf Of Sten-Erik Björling
> Sent: Thursday, November 21, 2019 7:27 AM
> To: omnisdev-en at lists.omnis-dev.com
> Subject: Select AS question
> 
> Dear all,
> 
> Evaluating how to speed up the management of several searches using encapsulated searches using SEARCH… AS functionality. The Postgres documentation is a bit scarce in this matter - do anyone have a tip on how to approach this?
> 
> Stene
> 
> _______
> 
> 
> Sten-Erik Björling
> Enviro Data
> Kyrkogatan 5A 2 tr
> SE-972 32  Luleå
> Sweden
> 
> E-Mail: s-e.bjorling at enviro.se
> Mobile: +46-70-655 11 72
> Wire: @stenerikbjorling
> Skype: stenerikbjorling
> iChat: stene at mac.com
> FaceTime: stene at mac.com
> whereby.com: stenerikbjorling
> Hotmail / Messenger: stenerikbjorling at hotmail.com
> GMail: stenerikbjorling at gmail.com
> ______
> 
> This email and any files transmitted with it are confidential, may be legally privileged and are intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, please note that any use, distribution, or reproduction of the contents of this email is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by return email and destroy all copies of the original message including any attachments thereto. Thank you.
> 
> Please note that we take reasonable precautions to prevent the transmission of viruses; however, we cannot guarantee that this email or its attachments are free from viruses. We only send and receive emails on the basis that we are not be liable for any loss or damage resulting from the opening of this message and/or attachments.
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 



Sten-Erik Björling
Enviro Data
Kyrkogatan 5A 2 tr
SE-972 32  Luleå
Sweden

E-Mail: s-e.bjorling at enviro.se
Mobile: +46-70-655 11 72
Wire: @stenerikbjorling
Skype: stenerikbjorling
iChat: stene at mac.com
FaceTime: stene at mac.com
whereby.com: stenerikbjorling
Hotmail / Messenger: stenerikbjorling at hotmail.com
GMail: stenerikbjorling at gmail.com
______

This email and any files transmitted with it are confidential, may be legally privileged and are intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient, please note that any use, distribution, or reproduction of the contents of this email is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by return email and destroy all copies of the original message including any attachments thereto. Thank you.

Please note that we take reasonable precautions to prevent the transmission of viruses; however, we cannot guarantee that this email or its attachments are free from viruses. We only send and receive emails on the basis that we are not be liable for any loss or damage resulting from the opening of this message and/or attachments.



More information about the omnisdev-en mailing list