Jegan Kunniya
01-23-2012, 01:24 AM
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
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