I would hazard a guess that the CURRENT_DATE() function is called for every
record (row) in the table.
to verify:
create a project method "CD" published for SQL:
$0:=Current date
place a break point.
---
then do
ARRAY DATE($ad;0)
Begin SQL
SELECT Field_2 from Table_1
WHERE {FN CD() AS TIMESTAMP} = Field_2
INTO :$ad;
End SQL
or
ARRAY DATE($ad;0)
Begin SQL
SELECT Field_2 from Table_1*/
WHERE Field_2 = {FN CD() AS TIMESTAMP}
INTO :$ad;
End SQL
either way, the method is called for all records in table.
if, for example we use a subquery:
ARRAY DATE($ad;0)
Begin SQL
SELECT Field_2 from Table_1
WHERE Field_2 = (SELECT {FN CD() AS TIMESTAMP} FROM Table_1 LIMIT 1)
INTO :$ad;
End SQL
then of course CD() is called only once.
by the way, SQL does not support native date (only) type except when cast from
a 4D expression or literals.
http://doc.4d.com/4Dv15/4D/15/4D-SQL-engine-implementation.300-2288119.en.html
{ d '2013-10-02' }
{ t '13:33:41' }
{ ts '1998-05-02 01:23:56.123' }
SQL functions such as CURRENT_DATE() seems to return a TIMESTAMP, which may or
may not match a native date value.
for that reason, a C_DATE FN wrapper method seems inevitable if you want to
query the database for date values using SQL.
> 2018/12/01 4:00、Jeffrey Kain via 4D_Tech <[email protected]>のメール:
>
> I'm trying to query 4D Server via the SQL engine from another application,
> and it seems like if you use CURRENT_DATE() or CURDATE(), the query is always
> sequential even if an index is available.
>
> Example:
>
> SELECT InvoiceID from Invoices
> WHERE CreatedDate=CURRENT_DATE()
>
> ... does a sequential scan of the Invoices table, even though
> Invoices.CreatedData is indexed. This takes a very long time - probably close
> to a half an hour.
>
> If I rewrite the where clause as:
>
> WHERE CreatedDate='2018-11-30'
>
> ... it takes milliseconds.
>
> Is there a trick to being able to write a script that can generically select
> against the current date? This is from some php code, fyi... but it's easily
> reproduced inside of 4D itself.
**********************************************************************
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]
**********************************************************************
