Re: Confused about ORDA
Thanks Lahav,
This does put it more into perspective.
I actually was trying to find out which way to go to optimize the speed as much
as possible.
And although a difference of 150 ms does sound ridiculous, I assume that with
more data, and used repetitively in loops, it will be a substantial difference.
Rudy Mortier
Two Way Communications bvba
> On 30 Jul 2018, at 06:37, lists via 4D_Tech <4d_tech@lists.4d.com> wrote:
>
> Rudy,
>
> I think part of the reason might be that with ORDA you actually have the data
> loaded and ready to use in the resulting collection, where as with QUERY you
> have a selection of records, but to use any of the data you still need to
> load it somehow.
>
> Also, using QUERY, regardless of speed, allow you access to one selection of
> records only, where as with ORDA, you can just execute the same query with
> different parameters in to a different collection and have as many different
> selection of records at the same time.
>
> I would think that a real test should be a complete task rather than one
> isolated part, and then make a decision as to which option makes more sense
> for that given task.
>
> Regards,
>
> Lahav
>
> -Original Message-
> From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Two Way
> Communications via 4D_Tech
> Sent: Sunday, July 29, 2018 5:47 AM
> To: 4D iNug Technical <4d_tech@lists.4d.com>
> Cc: Two Way Communications
> Subject: Confused about ORDA
>
> I am a bit confused… isn’t ORDA supposed to be faster than traditional
> queries?
>
> I did a test with a table containing 120,000 records. The query is on 4
> indexed fields and returns 54,000 records.
>
> Doing the query with ORDA takes 175 milliseconds.
> Doing the query with QUERY takes 4 milliseconds.
>
> Why??
>
> ORDA:
> $oQRYoption:=New object("queryPlan";False;"queryPath";False)
>
> $oTLD:=ds.T_Loggers_data.query("TLR.FK_REAP =:1 AND TLD_timestamp >=:2 AND
> TLD_timestamp <=:3 AND Excluded
> =:4";[REAP_sections]FK_REAP;$TSsectionStart;$TSsectionEnd;False;$oQRYoption)
>
>
>
> Traditional:
> QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*)
>
> QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*)
> QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*)
> QUERY([T_Loggers_data];[T_loggers_readings]Excluded=False)
>
>
> Rudy Mortier
> Two Way Communications bvba
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
> **
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Confused about ORDA
for a fair comparison,
I think the "query path" feature should be activated on both sides (ORDA and
QUERY).
you will get the time required to complete the query.
surrounding a block of code with "Milliseconds" (if that is how you got the
measurements) does not feel right in this context.
> Doing the query with ORDA takes 175 milliseconds.
> Doing the query with QUERY takes 4 milliseconds.
>
> Why??
>
> ORDA:
> $oQRYoption:=New object("queryPlan";False;"queryPath";False)
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
RE: Confused about ORDA
Rudy,
I think part of the reason might be that with ORDA you actually have the data
loaded and ready to use in the resulting collection, where as with QUERY you
have a selection of records, but to use any of the data you still need to load
it somehow.
Also, using QUERY, regardless of speed, allow you access to one selection of
records only, where as with ORDA, you can just execute the same query with
different parameters in to a different collection and have as many different
selection of records at the same time.
I would think that a real test should be a complete task rather than one
isolated part, and then make a decision as to which option makes more sense for
that given task.
Regards,
Lahav
-Original Message-
From: 4D_Tech <4d_tech-boun...@lists.4d.com> On Behalf Of Two Way
Communications via 4D_Tech
Sent: Sunday, July 29, 2018 5:47 AM
To: 4D iNug Technical <4d_tech@lists.4d.com>
Cc: Two Way Communications
Subject: Confused about ORDA
I am a bit confused… isn’t ORDA supposed to be faster than traditional queries?
I did a test with a table containing 120,000 records. The query is on 4 indexed
fields and returns 54,000 records.
Doing the query with ORDA takes 175 milliseconds.
Doing the query with QUERY takes 4 milliseconds.
Why??
ORDA:
$oQRYoption:=New object("queryPlan";False;"queryPath";False)
$oTLD:=ds.T_Loggers_data.query("TLR.FK_REAP =:1 AND TLD_timestamp >=:2 AND
TLD_timestamp <=:3 AND Excluded
=:4";[REAP_sections]FK_REAP;$TSsectionStart;$TSsectionEnd;False;$oQRYoption)
Traditional:
QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*)
QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*)
QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*)
QUERY([T_Loggers_data];[T_loggers_readings]Excluded=False)
Rudy Mortier
Two Way Communications bvba
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Confused about ORDA
Nope it always worked this way. If you have * at end of line and do not identify what query format will be & (and) is assumed This QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*) QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*) QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*) QUERY([T_Loggers_data];[T_loggers_readings]Excluded=False) is equivalent to QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*) QUERY([T_Loggers_data];&;[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*) QUERY([T_Loggers_data];&;[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*) QUERY([T_Loggers_data]&;;[T_loggers_readings]Excluded=False) Chuck Miller Voice: (617) 739-0306 Informed Solutions, Inc. Fax: (617) 232-1064 mailto:cjmillerinformed-solutions.com Brookline, MA 02446 USA Registered 4D Developer Providers of 4D and Sybase connectivity http://www.informed-solutions.com This message and any attached documents contain information which may be confidential, subject to privilege or exempt from disclosure under applicable law. These materials are intended only for the use of the intended recipient. If you are not the intended recipient of this transmission, you are hereby notified that any distribution, disclosure, printing, copying, storage, modification or the taking of any action in reliance upon this transmission is strictly prohibited. Delivery of this message to any person other than the intended recipient shall not compromise or waive such confidentiality, privilege or exemption from disclosure as to this communication. > On Jul 29, 2018, at 4:12 PM, Robert McKeever via 4D_Tech > <4d_tech@lists.4d.com> wrote: > > Either they have changed the way QUERY works, or you are missing …&;… after > the first semi-colon on lines 2, 3, and 4 of the QUERY. So, it looks like you > are really simply searching for just the last line. Probably would be quicker. ** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **
Re: Confused about ORDA
On Jul 29, 2018, at 2:00 PM, Rudy Mortier wrote:
> I am a bit confused… isn’t ORDA supposed to be faster than traditional
> queries?
>
> I did a test with a table containing 120,000 records. The query is on 4
> indexed fields and returns 54,000 records.
>
> Doing the query with ORDA takes 175 milliseconds.
> Doing the query with QUERY takes 4 milliseconds.
>
> Why??
>
> ORDA:
> $oQRYoption:=New object("queryPlan";False;"queryPath";False)
>
> $oTLD:=ds.T_Loggers_data.query("TLR.FK_REAP =:1 AND TLD_timestamp >=:2 AND
> TLD_timestamp <=:3 AND Excluded
> =:4";[REAP_sections]FK_REAP;$TSsectionStart;$TSsectionEnd;False;$oQRYoption)
>
>
>
> Traditional:
> QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*)
>
> QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*)
> QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*)
> QUERY([T_Loggers_data];[T_loggers_readings]Excluded=False)
Might be the same reason a query run from the native 4D language is ALWAYS
faster than the same query done via 4D SQL. The reason… the time to parse the
raw text and convert that into a form the 4DDB engine needs.
Example:
QUERY([Patients];[Patients]LastName="smith”)
4D can very easily convert this line of code into 4DDB code and then execute it
because it is already tokenized by the method editor. Parsing is very easy.
Nothing to look up. It knows it has a valid table number and a valid field
number. Making the 4DDB engine code is then fairly easy.
C_LONGINT($recordsInSelection_l)
Begin SQL
SELECT COUNT(*) FROM Patients
WHERE LastName = 'smith'
INTO :$recordsInSelection_l
End SQL
4D must take the text "SELECT COUNT(LastName) FROM Patients WHERE LastName =
'smith' INTO :$recordsInSelection_l” and parse it. It has to figure out what
are the field names and convert them to a field number, figure out what is a
table name and convert that to a table number and parse the WHERE clause. Once
it knows all that it can build the 4DDB engine code needed to make it happen.
All that takes some milliseconds.
That’s my understanding. Miyako or JPR and the 4D gurus that know the guts of
4D better than I can correct me where I’m wrong.
ORDA has to do text parsing too. And it’s parsing is most likely much more
complex than the SQL parsing. ORDA has so many more possibilities. I’d be
curious to see the above comparison done in ORDA and see if it is as faster or
slower that SQL.
Also, it was never my impression that ORDA would result in faster execution
times. It was always presented to me that ORDA was a tool for developers that
would allow you to write code faster than you can now. Particularly when you
need to do some complex operations. What takes many lines of native 4D code can
new be done in a single line of ORDA. Faster for the developer, but maybe a tad
bit slower on execution.
Tim
*
Tim Nevels
Innovative Solutions
785-749-3444
timnev...@mac.com
*
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Confused about ORDA
Exactly, the ‘&’ is not mandatory. Both queries return the same result.
Rudy Mortier
Two Way Communications bvba
> On 29 Jul 2018, at 22:23, Douglas von Roeder via 4D_Tech
> <4d_tech@lists.4d.com> wrote:
>
> Bob:
>
> I think that's a shortcut from many years ago so it's an implicit "&".
>
> --
> Douglas von Roeder
> 949-336-2902
>
>
> On Sun, Jul 29, 2018 at 1:12 PM Robert McKeever via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
>
>> Either they have changed the way QUERY works, or you are missing …&;…
>> after the first semi-colon on lines 2, 3, and 4 of the QUERY. So, it looks
>> like you are really simply searching for just the last line. Probably would
>> be quicker.
>>
>>> On Jul 29, 2018, at 4:47 AM, Two Way Communications via 4D_Tech <
>> 4d_tech@lists.4d.com> wrote:
>>>
>>> I am a bit confused… isn’t ORDA supposed to be faster than traditional
>> queries?
>>>
>>> I did a test with a table containing 120,000 records. The query is on 4
>> indexed fields and returns 54,000 records.
>>>
>>> Doing the query with ORDA takes 175 milliseconds.
>>> Doing the query with QUERY takes 4 milliseconds.
>>>
>>> Why??
>>>
>>> ORDA:
>>> $oQRYoption:=New object("queryPlan";False;"queryPath";False)
>>
>>> $oTLD:=ds.T_Loggers_data.query("TLR.FK_REAP =:1 AND TLD_timestamp >=:2
>> AND TLD_timestamp <=:3 AND Excluded
>> =:4";[REAP_sections]FK_REAP;$TSsectionStart;$TSsectionEnd;False;$oQRYoption)
>>
>>>
>>>
>>> Traditional:
>>>
>> QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*)
>>
>>> QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*)
>>> QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*)
>>> QUERY([T_Loggers_data];[T_loggers_readings]Excluded=False)
>>>
>>>
>>> Rudy Mortier
>>> Two Way Communications bvba
>>>
>>> **
>>> 4D Internet Users Group (4D iNUG)
>>> Archive: http://lists.4d.com/archives.html
>>> Options: https://lists.4d.com/mailman/options/4d_tech
>>> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
>>> **
>>
>> _
>> Bob McKeever http://www.mswl.com <
>> http://www.mswl.com/>
>> McKeever's Software Wizardry
>> Port Coquitlam, B.C.
>> bobmckee...@mac.com
>>
>>
>>
>>
>> **
>> 4D Internet Users Group (4D iNUG)
>> Archive: http://lists.4d.com/archives.html
>> Options: https://lists.4d.com/mailman/options/4d_tech
>> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
>> **
> **
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Confused about ORDA
Bob:
I think that's a shortcut from many years ago so it's an implicit "&".
--
Douglas von Roeder
949-336-2902
On Sun, Jul 29, 2018 at 1:12 PM Robert McKeever via 4D_Tech <
4d_tech@lists.4d.com> wrote:
> Either they have changed the way QUERY works, or you are missing …&;…
> after the first semi-colon on lines 2, 3, and 4 of the QUERY. So, it looks
> like you are really simply searching for just the last line. Probably would
> be quicker.
>
> > On Jul 29, 2018, at 4:47 AM, Two Way Communications via 4D_Tech <
> 4d_tech@lists.4d.com> wrote:
> >
> > I am a bit confused… isn’t ORDA supposed to be faster than traditional
> queries?
> >
> > I did a test with a table containing 120,000 records. The query is on 4
> indexed fields and returns 54,000 records.
> >
> > Doing the query with ORDA takes 175 milliseconds.
> > Doing the query with QUERY takes 4 milliseconds.
> >
> > Why??
> >
> > ORDA:
> > $oQRYoption:=New object("queryPlan";False;"queryPath";False)
>
> > $oTLD:=ds.T_Loggers_data.query("TLR.FK_REAP =:1 AND TLD_timestamp >=:2
> AND TLD_timestamp <=:3 AND Excluded
> =:4";[REAP_sections]FK_REAP;$TSsectionStart;$TSsectionEnd;False;$oQRYoption)
>
> >
> >
> > Traditional:
> >
> QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*)
>
> > QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*)
> > QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*)
> > QUERY([T_Loggers_data];[T_loggers_readings]Excluded=False)
> >
> >
> > Rudy Mortier
> > Two Way Communications bvba
> >
> > **
> > 4D Internet Users Group (4D iNUG)
> > Archive: http://lists.4d.com/archives.html
> > Options: https://lists.4d.com/mailman/options/4d_tech
> > Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> > **
>
> _
> Bob McKeever http://www.mswl.com <
> http://www.mswl.com/>
> McKeever's Software Wizardry
> Port Coquitlam, B.C.
> bobmckee...@mac.com
>
>
>
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
Re: Confused about ORDA
Either they have changed the way QUERY works, or you are missing …&;… after the
first semi-colon on lines 2, 3, and 4 of the QUERY. So, it looks like you are
really simply searching for just the last line. Probably would be quicker.
> On Jul 29, 2018, at 4:47 AM, Two Way Communications via 4D_Tech
> <4d_tech@lists.4d.com> wrote:
>
> I am a bit confused… isn’t ORDA supposed to be faster than traditional
> queries?
>
> I did a test with a table containing 120,000 records. The query is on 4
> indexed fields and returns 54,000 records.
>
> Doing the query with ORDA takes 175 milliseconds.
> Doing the query with QUERY takes 4 milliseconds.
>
> Why??
>
> ORDA:
> $oQRYoption:=New object("queryPlan";False;"queryPath";False)
>
> $oTLD:=ds.T_Loggers_data.query("TLR.FK_REAP =:1 AND TLD_timestamp >=:2 AND
> TLD_timestamp <=:3 AND Excluded
> =:4";[REAP_sections]FK_REAP;$TSsectionStart;$TSsectionEnd;False;$oQRYoption)
>
>
>
> Traditional:
> QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*)
>
> QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*)
> QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*)
> QUERY([T_Loggers_data];[T_loggers_readings]Excluded=False)
>
>
> Rudy Mortier
> Two Way Communications bvba
>
> **
> 4D Internet Users Group (4D iNUG)
> Archive: http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
> **
_
Bob McKeever http://www.mswl.com
Confused about ORDA
I am a bit confused… isn’t ORDA supposed to be faster than traditional queries?
I did a test with a table containing 120,000 records. The query is on 4 indexed
fields and returns 54,000 records.
Doing the query with ORDA takes 175 milliseconds.
Doing the query with QUERY takes 4 milliseconds.
Why??
ORDA:
$oQRYoption:=New object("queryPlan";False;"queryPath";False)
$oTLD:=ds.T_Loggers_data.query("TLR.FK_REAP =:1 AND TLD_timestamp >=:2 AND
TLD_timestamp <=:3 AND Excluded
=:4";[REAP_sections]FK_REAP;$TSsectionStart;$TSsectionEnd;False;$oQRYoption)
Traditional:
QUERY([T_Loggers_data];[T_loggers_readings]FK_REAP=[REAP_sections]FK_REAP;*)
QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp>=$TSsectionStart;*)
QUERY([T_Loggers_data];[T_Loggers_data]TLD_timestamp<=$TSsectionEnd;*)
QUERY([T_Loggers_data];[T_loggers_readings]Excluded=False)
Rudy Mortier
Two Way Communications bvba
**
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**
