Issues with DELETE query in SourceProEd10

[Jegan Kunniya]

Hello there,

I use Roguewave in some of my applications.

Recently these application has been migrated to SourceProEd10 from SourceProEd8 and have hit with performance issues in the DELETE query.

Here is the scenario of my issue

Eg:

RWDBTable table = db.table(table_name);
RWDBDeleter deleter = table.deleter();

deleter.where(table[“date_time”] == in_datetime);

dbcontroller->Delete(delete);

…

The generated SQL in SourceProEd10 looks like

Delete table_name t11
Where t11.date_time = in_datetime;

Where as in RogueWave 8 it is,

Delete table_name
Where date_time = TO_DATE(in_datetime, dateFormat);

This impacts my SQL PLAN as i have partitoned the table based on the datetime. Since the TO_DATE function is not generated, the SQL optimizer uses a different PLAN than the one it was using in ORACLE8.

Can you throw some light on this.? Any help is greatly appreciated. Thanks in advance

~Jegan

[gtruong_rw]

Hi Jegan,

Both SourcePro Ed 8 and Ed 10 use placeholder (: ph) for datetime data type. If you turn on the RWDBTracer::NoPlaceHolderSQL, you would see that the content of the placeholder is the TO_DATE() function (see below). This is the behavior I see for both SourcePro Ed 8 and 10.


01/25/2012 15:59:49 SQL> DELETE times1 WHERE dt = TO_DATE('2012/01/25 15:59:49','YYYY/MM/DD HH24:MI:SS')

01/25/2012 15:59:49 SQL> DELETE times1 WHERE dt = : ph10

RWDBTracer& aTracer = db.tracer();
aTracer.setOn(RWDBTracer::SQL);
//aTracer.setOn(RWDBTracer::BoundBuffers);
aTracer.setOn(RWDBTracer::NoPlaceHolderSQL);
aTracer.stream(cout);
...
RWTime now;
RWDBDeleter deleter = tbl.deleter();
deleter.where(tb_dt == now );
RWDBResult res = deleter.execute(cn);