Results 1 to 2 of 2

Thread: Issues with DELETE query in SourceProEd10

  1. #1

    Issues with DELETE query in SourceProEd10

    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

  2. #2
    Junior Member gtruong_rw's Avatar
    Join Date
    Mar 2008
    Posts
    13
    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);

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •