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
[email protected]
*****************************************
**********************************************************************
4D Internet Users Group (4D iNUG)
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:[email protected]
**********************************************************************
