Re: QUERY into variable vs. Records in Table

[Arnaud de Montard via 4D_Tech]

> Le 19 oct. 2017 à 19:22, Pat Bensky via 4D_Tech <4d_tech@lists.4d.com> a 
> écrit :
> 
> I'd have thought Records in Table would be quicker ... I'm sure 4D already
> "knows" how many records there are in each table, so it can just answer
> that question immediately rather than having to query and count the result

4D server knows, but a client has to ask, due to other clients inserts or 
deletions. If any client had to be aware at any time, we would have a huge 
traffic. 4D could embed these value in some "stowaway", as I described, but 
it's not the case.

I translated a french feature I made a few months ago related to this:
 

-- 
Arnaud de Montard 




**
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 into variable vs. Records in Table

[David Adams via 4D_Tech]

> And all it's doing is waiting for a relatively rare condition to happen
(and a record to appear in this table).  It might
>  happen at most once or twice per day, maybe only once every few days,
but when it happens it has to be dealt with
> immediately. It's such a waste to be constantly querying for a record
that 9 times out of 10 won't exist.

Can this rare condition that creates a record do anything else? Something
that would make it into a push/notification instead of record/semaphore
that you have to constantly poll for?

And, the last time I tested, Records in table takes advantage of a stored
statistic so it's speed is very fast and also very consistent. It doesn't
change based on table size, etc. As you know, InnoDB doesn't store that
stat, so SELECT COUNT(*) on a big table can kill you. Postgres, on the
other hand, stores tons of stats and has a sample-based frequency table on
board or the query analyzer to read from.
**
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 into variable vs. Records in Table

[Kirk Brooks via 4D_Tech]

Hi Jeff,

On Thu, Oct 19, 2017 at 10:28 AM, Jeffrey Kain via 4D_Tech <
4d_tech@lists.4d.com> wrote:

> ​...
>

all it's doing is waiting for a relatively rare condition to happen (and a
> record to appear in this table).  It might happen at most once or twice per
> day, maybe only once every few days, but when it happens it has to be dealt
> with immediately. It's such a waste to be constantly querying for a record
> that 9 times out of 10 won't exist.
>
​
An approach I use in these cases is (I'm using pseudo code and this off the
top of my head)​:

​// Handle_rare_condition (critical param{;internal param})

Case of

:(Count parameters=1)

//  Write $1 to the [Message table}

$id:=Execute on server(Current method name;0;Current method name;$1;Current
process;*)

Resume process($id)


:(Count parameters=2) //  this part is executing on the server


$done:=false

While(Not($done))

All Records([Message table])

Loop ([Message table])  --  do important stuff

Pause process(Current process)

End while

End case​


​When ​Handle_rare_condition is called with one param it writes that param
to the Message Table. Then it calls itself on the server. Execute on server
will either start the process, if it isn't already there, or since I
included the trailing * simply return the id of the existing process, which
if it exists is probably paused. Resume process($id) is ignored if the
process isn't paused (like when you just created it). Otherwise it wakes it
up.

On the server side $1 is pretty much ignored in this case. $2 might be
useful (I use this same form on Clients for spawning processes so knowing
the parent process number can be useful for messaging). In this case you
could make $1 a magic number of some sort to indicate the part of the code
running on the server. Or you could write the [Message table] record first
and then call this method without any params to initiate it and the parent
process as $1 to indicate the server code.

Whatever, the bottom part of the method starts a loop on the server which
sounds like it could get all records for [Message table], loop them and do
the important stuff. Then just go back to sleep. As someone else suggested
this could be called from a trigger on [Message table] to run whenever one
of those records was created. ​

​This would not involve any queries.​

I like putting the spawning and execution of server side methods in one
method. All the important stuff is in one place.

-- 
Kirk Brooks
San Francisco, CA
===

*The only thing necessary for the triumph of evil is for good men to do
nothing.*

*- Edmund Burke*
**
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 into variable vs. Records in Table

[Tim Nevels via 4D_Tech]

On Oct 19, 2017, at 1:45 PM, Jeffrey Kain  wrote:

> Decided to test it. Here are the results, running interpreted, client-server 
> against a server under very low load:
> 
> Records in table: 565 ms
> Semaphore polling: 2482 ms
> Query into variable: 2749 ms

Makes sense to me if you think like a programmer and what it would take to 
accomplish the task. 

Records in table requires a small packet of data to be sent to the server, 
server checks a value in RAM and sends back a small packet of data with the 
result. So minimal network I/O.

Semaphore would also be a small packet of data sent to the server, but the code 
running on the server would obviously be more complex so takes a little longer 
to process. Another small packet of data sent back to the client.

And doing a QUERY would be the most code intensive on the server. 

Would have been interesting to see the result if you did a QUERY into the 
current selection. I would guess that would take a bit longer due to having to 
manage setting up the current selection on the server. And the packet of data 
returned by the server would also include the first record of the selection so 
it would be bigger. Plus all the time on the client side to setup the selection 
info and make the shadow copy of the current record. 

Everything is small bits of time, but they all add up when you do it thousands 
of times.

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 into variable vs. Records in Table

[Jeffrey Kain via 4D_Tech]

Great idea - I think that would actually work for what I need...

Thanks!

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

> On Oct 19, 2017, at 2:45 PM, Spencer Hinsdale via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> maybe i don't understand but what about:
> 
> `trigger
> execute on client("@";"flagPoll")
> 
> `flagPoll
> c_boolean(<>flagPoll)
> <>flagPoll:=true

**
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 into variable vs. Records in Table

[Spencer Hinsdale via 4D_Tech]

maybe i don't understand but what about:

`trigger
execute on client("@";"flagPoll")

`flagPoll
c_boolean(<>flagPoll)
<>flagPoll:=true

...

if(<>flagPoll)
>   $x:=Records in table([ServerMigrations])
<>flagPoll:=false
else
 `skip 99,999 calls to server
end if

> On Oct 19, 2017, at 11:31 AM, Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> Decided to test it. Here are the results, running interpreted, client-server 
> against a server under very low load:
> 
> Records in table: 565 ms
> Semaphore polling: 2482 ms
> Query into variable: 2749 ms
> 
> Here's my test code:
> 
> C_LONGINT($msRIT;$msSEM;$msQUERY;$x;$n)
> C_BOOLEAN($b)
> $n:=1000
> 
> $msRIT:=Milliseconds
> For ($i;1;$n)
>   $x:=Records in table([ServerMigrations])
> End for 
> $msRIT:=Milliseconds-$msRIT

**
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 into variable vs. Records in Table

[Jeffrey Kain via 4D_Tech]

Decided to test it. Here are the results, running interpreted, client-server 
against a server under very low load:

Records in table: 565 ms
Semaphore polling: 2482 ms
Query into variable: 2749 ms

Here's my test code:

C_LONGINT($msRIT;$msSEM;$msQUERY;$x;$n)
C_BOOLEAN($b)
$n:=1000

$msRIT:=Milliseconds
For ($i;1;$n)
   $x:=Records in table([ServerMigrations])
End for 
$msRIT:=Milliseconds-$msRIT

$msSEM:=Milliseconds
For ($i;1;$n)
   $b:=Test semaphore("SomeSemaphore")
End for 
$msSEM:=Milliseconds-$msSEM

$msQUERY:=Milliseconds
For ($i;1;$n)
   SET QUERY DESTINATION(Into variable;$x)
   QUERY([ServerMigrations];[ServerMigrations]StartDate=!2017-10-18!)
   SET QUERY DESTINATION(Into current selection)
End for 
$msQUERY:=Milliseconds-$msQUERY

ALERT("Records in table: "+String($msRIT)+" ms.\n"+\
   "Semaphore polling: "+String($msSEM)+" ms.\n"+\
   "Query into variable: "+String($msQUERY)+" ms.")



> On Oct 19, 2017, at 2:06 PM, Timothy Penner  wrote:
> 
> Got it, the two concepts just seem completely different. One requires a query 
> the other doesn’t.
> I would assume the query would slow it down, but I could be wrong or the 
> difference could be insignificant...
> 
> Sorry if this isn’t much help.
> 
> -Tim

**
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 into variable vs. Records in Table

[Spencer Hinsdale via 4D_Tech]

trigger?

> On Oct 19, 2017, at 10:28 AM, Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> I feel like I'm missing an obvious solution.
**
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 into variable vs. Records in Table

[Timothy Penner via 4D_Tech]

I am not sure why you would be comparing QUERY into variable vs. Records in 
Table - did you really mean Records in Selection, not Table?

QUERY into variable would require a query which would probably be slower (due 
to a query).
Records in Table ignores any selection and just returns the number of records 
in the table, presumably from the table headers (without any actual query).

Of course I am just guessing here, I haven’t run any tests.

-Tim



**
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 into variable vs. Records in Table

[Julio Carneiro via 4D_Tech]

Jeffrey, I did not quite get your question.

Records in Table does not relate to QUERY in any way, because you are comparing 
a sort of static number, total records in a table, to the result of a dynamic 
operation.

Did you mean compare:

Set Query Destination(into variable;$nrecs)
query….
Set Query Destination(into current selection)

as opposed to

   Query…
   $nrecs:= records in selection ([table])


There I can see a viable comparison, and without running any real tests, gut 
feeling tells me the first would be quicker. The difference is that in the 
second case the first record in the selection is made available to the client, 
thus transferred over the network. While i the first case, all that sent from 
server to client is a number! Thus network wise, first option sounds quicker.

Now, back Records in Table, one would think that would be a known variable, 
that 4D would have available at some place. Well, it does not… first time you 
call Records in Table, 4D seems to actually calculate the # of recs in a table, 
and on a large DB it’ll take some time to respond. Then it caches that number, 
and subsequent calls are quicker. I have a form on most of my apps, where I 
present a list of tables along with their record count. On a 70Gb datafile, 
with 100s of table, populating that list can take a measurable amount (the 
first time).

julio
> On Oct 19, 2017, at 3:28 PM, Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com> 
> wrote:
> 
> We've been using the new Get Database Measures command to take a look at what 
> our application is actually doing, and plotting some of these metrics to our 
> dashboard... for example, most frequently run queries.  This particular query 
> popped to the top, executing a few hundred times per minute.
> 
> And all it's doing is waiting for a relatively rare condition to happen (and 
> a record to appear in this table).  It might happen at most once or twice per 
> day, maybe only once every few days, but when it happens it has to be dealt 
> with immediately. It's such a waste to be constantly querying for a record 
> that 9 times out of 10 won't exist.
> 
> I feel like I'm missing an obvious solution.
> 
> --
> Jeffrey Kain
> jeffrey.k...@gmail.com
> 
> 

--
Julio Carneiro
jjfo...@gmail.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 into variable vs. Records in Table

[Jeffrey Kain via 4D_Tech]

We've been using the new Get Database Measures command to take a look at what 
our application is actually doing, and plotting some of these metrics to our 
dashboard... for example, most frequently run queries.  This particular query 
popped to the top, executing a few hundred times per minute.

And all it's doing is waiting for a relatively rare condition to happen (and a 
record to appear in this table).  It might happen at most once or twice per 
day, maybe only once every few days, but when it happens it has to be dealt 
with immediately. It's such a waste to be constantly querying for a record that 
9 times out of 10 won't exist.

I feel like I'm missing an obvious solution.

--
Jeffrey Kain
jeffrey.k...@gmail.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 into variable vs. Records in Table

[Pat Bensky via 4D_Tech]

I'd have thought Records in Table would be quicker ... I'm sure 4D already
"knows" how many records there are in each table, so it can just answer
that question immediately rather than having to query and count the result
...

Pat

On 19 October 2017 at 17:28, Dennis, Neil via 4D_Tech <4d_tech@lists.4d.com>
wrote:

> > Does anyone know the relative performance impact on a server of calling
> a QUERY wrapped inside
> > a SET QUERY DESTINATION (Into Variable...), compared to calling Records
> In Table?
>
> In theory, related to each other the into variable would execute a bit
> faster in that it isn't building and passing selections between client and
> server. But I would bet the actual difference is pretty minimal.
>
> I would be curious if someone wants to set it up and test the actual time
> :)
>
> Neil
>
>
>
>
>
>
> --
>
>
> Privacy Disclaimer: This message contains confidential information and is
> intended only for the named addressee. If you are not the named addressee
> you should not disseminate, distribute or copy this email. Please delete
> this email from your system and notify the sender immediately by replying
> to this email.  If you are not the intended recipient you are notified that
> disclosing, copying, distributing or taking any action in reliance on the
> contents of this information is strictly prohibited.
>
> The Alternative Investments division of UMB Fund Services provides a full
> range of services to hedge funds, funds of funds and private equity funds.
> Any tax advice in this communication is not intended to be used, and cannot
> be used, by a client or any other person or entity for the purpose of (a)
> avoiding penalties that may be imposed on any taxpayer or (b) promoting,
> marketing, or recommending to another party any matter addressed herein.
> **
> 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
> **
>



-- 
*
CatBase - Top Dog in Data Publishing
tel: +44 (0) 207 118 7889
w: http://www.catbase.com
skype: pat.bensky
*
**
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 into variable vs. Records in Table

[Jeffrey Kain via 4D_Tech]

I was thinking that I could just create a semaphore instead of creating a 
record. The code that would otherwise be querying would just be calling Test 
Semaphore... but a method to clear that semaphore would be tricky since only 
the process that creates it can kill it.

And I kind of wonder if Semaphore is just as "expensive" as Records in Table, 
since both involve a little bit of network traffic.

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

> On Oct 19, 2017, at 1:17 PM, Arnaud de Montard via 4D_Tech 
> <4d_tech@lists.4d.com> wrote:
> 
> I'd call that "the stowaway" instead, but you get the idea… 
> 
> Of course it doesn't fit your need if the goal is a "real time" answer. 

**
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 into variable vs. Records in Table

[Arnaud de Montard via 4D_Tech]

> Le 19 oct. 2017 à 18:25, Jeffrey Kain via 4D_Tech <4d_tech@lists.4d.com> a 
> écrit :
> 
> Does anyone know the relative performance impact on a server of calling a 
> QUERY wrapped inside a SET QUERY DESTINATION (Into Variable...), compared to 
> calling Records In Table?
> 
> If you had to do one a hundred times a minute or more, which would you 
> choose? I'm guessing the server can get the record count of a table much 
> faster than any type of query, but I haven't logged it or measured it yet.

Makes me remind an Olivier Deschanels' demo during last world tour, "the 
backpack", because he used Records in table(s) as example. 

Instead of direct calls to server to get that, he packs it in an object and 
stores it in a "departure area". Then, when a "do it now" request must be send, 
the content of the departure area is added and the whole stuff is send. On 
server side, unpack, execute, pack, send back. Back to client, unpack. 

I'd call that "the stowaway" instead, but you get the idea… 

Of course it doesn't fit your need if the goal is a "real time" answer. 

-- 
Arnaud 




**
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
**