Query filter by Param AsSQLTimeStamp is terribly slow?

[gb]

Hi,

I have a table with around 2 million records like

CREATE TABLE SOMETABLE (
SOMEKEY NUMBER(*, 0) NOT NULL,
SOMEDATE DATE,
SOMEOTHER NUMBER(*, 0)
)

If I run a SELECT with a TSQLQuery *qr:

qr->SQL->Text = "SELECT SOMEKEY "
"FROM SOMETABLE WHERE SOMEDATE=:SOMEDATE";
qr->ParamByName("SOMEDATE")->AsSQLTimeStamp = DateTimeToSQLTimeStamp(Date());
qr->Open();

Takes about 18 seconds (just the Open() statement) ! However, the very same SELECT as

qr->SQL->Text = "SELECT SOMEKEY "
"FROM SOMETABLE WHERE SOMEDATE=TO_DATE('18-02-09','dd-mm-yy')";
qr->Open();

It's blazing fast (way less than a second).

Any thoughts? And, yes there is a (composite) index in place.

Thank you for any help.

gb

[gb]

Just to add that even with an explicit single index on that field it won't help.

CREATE INDEX SOMEDATE_TEST_IDX ON SOMETABLE (SOMEDATE);

nor

ANALYZE TABLE SOMETABLE COMPUTER STATISTICS

it still seems to be doing a *full* table scan when using

"qr->ParamByName("SOMEDATE")->AsSQLTimeStamp = DateTimeToSQLTimeStamp(Date());"

[Plash]

DbxOda uses TIMESTAMP data type for parameters of ftTimeStamp type. This can be a cause of the problem.
Try to add CAST to your SQL:

Code: Select all

SELECT SOMEKEY FROM SOMETABLE WHERE SOMEDATE = CAST(:SOMEDATE AS DATE)

[gb]

Yes,

"WHERE SOMEDATE = CAST(:SOMEDATE AS DATE)"

works quite well. Is there any connection option that I can set to do this cast implicitly? I've thousands of SQL statements and most of them are shared across in more than a single database engine type, so it would be a problem to add this type cast everywhere.

gb

[gb]

Just to fully corroborate your hint,

Code: Select all

SELECT SOMEKEY FROM SOMETABLE WHERE SOMEDATE = CAST(TO_DATE('18-02-09','dd-mm-yy') AS TIMESTAMP)

Is also extremely slow.

It's true that the underlying table has field SOMEDATE created as DATE instead of TIMESTAMP, but I can't use

Code: Select all

qr->SQL->Text = "SELECT SOMEKEY "
"FROM SOMETABLE WHERE SOMEDATE=:SOMEDATE";
qr->ParamByName("SOMEDATE")->AsDateTime = Date();

because DBX will give an error as soon as Open() is called:

Code: Select all

No value for parameter 'SOMEDATE'

Therefore, I'm being "forced" to use AsSQLTimeStamp instead.

Edit: Just to add that "forced" is not technically true, as I can resort to "AsDate" and it would work, but it's just that all code assumes that a DATE field can be handled as a DATETIME one.

Edit2: Incidentally, I use DATE as the underline database field type because it's precision is enough (up to the second - it's name may mislead on this) while the extra precision provided by TIMESTAMP as not been required (yet).
So, because I can't use AsDateTime (no value for parameter error), and AsDate truncates precision to whole days (not tested thought, just assumed), I resorted to the last option... AsSQLTimeStamp.

[Plash]

In the next build of the driver we will add the UseDateParams option that will enable mapping ftTimeStamp to the DATE Oracle type.

[gb]

Thank you, that should do it!

An appreciated *additional* fix, would be to allow the usage of

Code: Select all

qr->ParamByName("SOMEDATE")->AsDateTime = Now();

Because, as mentioned, at the moment it will error with

Code: Select all

No value for parameter 'SOMEDATE'

on Open();

It should've worked because SOMEDATE is a DATE field type, not a TIMESTAMP one (thought being able to transparently remap between both seems a good thing).

Edit: Just to add that while AsDate as in

Code: Select all

qr->ParamByName("SOMEDATE")->AsDate = Now();

do not crash, it indeed truncates hours and minutes (as expected - and tested).
That's why "AsDateTime" is required to work! Using "AsSQLTimeStamp" was just a workaround (in this case).

[Plash]

dbExpress itself does not support ftDateTime parameters starting with RAD Studio 2007.

[gb]

Yes, that was unfortunate from CodeGear to not include support for it.

It created the need to use "AsSQLTimeStamp" instead of "AsDateTime" on Parameters. I wonder how everyone else is using dbExpress to filter database fields of DATE type when DBX is implicitly casting them as TIMESTAMP which invalidates the use of any INDEX and causes a full table scan. Was it a known issue? It must have been... and what a really *huge* one.

Anyway, my question is if even dbExpress is not supporting ftDateTime natively, could DevArt add support for it? I admit that I've no idea how difficult would it be, just asking.

gb

[Plash]

No, we cannot make ftDateTime parameters work with RAD Studio 2007 and higher.

[gb]

In the next build of the driver we will add the UseDateParams option that will enable mapping ftTimeStamp to the DATE Oracle type.

I guess this will have to do it then.

When I initially faced this limitation, I worked my way around it patching parameter type into ftTimeStamp before any Open and ExecSQL on a subclassed TSQLQuery (thought I didn't immediately notice the index issues).

Was this (heavy performance penalty through the implicit cast) a known issue to DevArt before I raised it?

Edit: Just to add that the missing functionality of "AsDateTime" is at least acknowledged (thought it's kind of low on votes to fix it)
http://qc.embarcadero.com/wc/qcmain.aspx?d=34745
However, the proposed workaround of using AsSQLTimeStamp completely misses the point that it will invalidate the usage of any index forcing a full table scan. I'll be grateful to have the option to map it available.

[Plash]

We did not know about the performance issue with ftTimeStamp parameters before you have written about it.

[gb]

Plash, thank you for the clarification.

It's quite possible that even CodeGear is not aware of it (because of the suggested workaround in QC 34745). If they were I guess (hope?) it would be quite high on their priority list to be fixed ASAP. It's unfortunate that they are just about to release a new Update Pack to RAD2009 any time soon and this window of opportunity has surely slipped away (for now at least)

I would like to take the opportunity to request everyone that can vote on QC to rise the priority of this issue (34745). And yes, it should really make a difference (btw, you can vote up to 10 times on the same issue - or so I've been told).

[gb]

First, congratulations! Adding "UseDateParams=True" on TSQLConnection->Params does work on the new 4.40.15.

Therefore, it seems that DevArt provides the only available workaround for this issue (and a pretty good one - if I may say so).

Some heads up for the couple weeks. This issue relates to http://qc.embarcadero.com/wc/qcmain.aspx?d=34745
We can't use AsDateTime on DBX > Delphi7, and the official workaround is to use AsQLTimeStamp instead. That turned out unusable because without an explicit cast of TIMESTAMP to DATE, any index on on those fields would be ignored, making the query crawl with a full table scan.

Now, this has been reported to CodeGear back in 10/2/2006, but it seems to have been ignored until recently. At the moment, Nick Hodges has mentioned that the issue has been promoted to a B1 (MustFix) and will be fixed (alas not on the next third update pack, nor on any <D2009):
https://forums.codegear.com/message.jsp ... 4590#94590

On a related note, this may (or may not) have a root on the lack of a proper native TDBXDateTimeValue. I rised that question and it seems that the CodeGear R&D team has been made aware of it (according to Jim Tierney):
http://blogs.embarcadero.com/jimtierney ... 7#comments

Let's wait and see.

Back on topic, well done DevArt.