PL/SQL

May 3, 2007

Powermultiset applied to groups

Filed under: CAST, COLLECT, powermultiset, XMLAGG, XMLELEMENT — Michael Moore @ 5:59 pm

This article has moved to

Mike’s PL/SQL NotesĀ 

Advertisements

March 8, 2007

XMLELEMENT performance problem (PL/SQL)

Filed under: example, PL/SQL, pl/sql code, XMLELEMENT — Michael Moore @ 10:03 pm

-- 22 seconds on 9i, 2 seconds on 10g
-- Also, a weird thing is that speed does not decrease in a linear  way
 with the number of --iterations. Instead, speed seems to decrease exponentially.
 If you increase the iterations from --200 to 400 , 
the timing goes from 2 seconds to 9.2 seconds.

SET timing on

DECLARE
   x            INTEGER           := 0;
   audit_info   VARCHAR2 ( 2000 );
BEGIN
   WHILE x < 300
       LOOP
             SELECT
                XMLELEMENT ( "DEALER_SERVICE_KEY", '234234' )
             || XMLELEMENT ( "PARENT_KEY", '234234' )
             || XMLELEMENT ( "CAP_TOTAL_KEY", '234234' )
             || XMLELEMENT ( "CAP_ENTITY", '234234' )
             || XMLELEMENT ( "CAP_LAST_RESET_DATE", '234234' )
             || XMLELEMENT ( "CAP_LIMIT", '234234' )
             || XMLELEMENT ( "CURRENT_CNT", '234234' )
             || XMLELEMENT ( "DATE", sysdate)
             || XMLELEMENT ( "CAP_PROFILE2TX_CAP_PROFILE", '234234' )
             || XMLELEMENT ( "WHERE_CLAUSE", '234234' )
             || XMLELEMENT ( "PRODUCT2TX_PRODUCT", '234234' )
             || XMLELEMENT ( "PRODUCT_KEY", '234234' )
             || XMLELEMENT ( "PRODUCT_NAME", '234234' )
             || XMLELEMENT ( "RATE", '234234' )
             || XMLELEMENT ( "GL_NUMBER", '234234' )
             || XMLELEMENT ( "AP_NUMBER", '234234' )
             || XMLELEMENT ( "COMMISSIONS_NUMBER", '234234' )
             || XMLELEMENT ( "INVOICE_STATUS", 'NOTPROCESSED' )
             || XMLELEMENT ( "INVOICE_STATUS_DATE", sysdate)
        INTO audit_info
        FROM DUAL;

      x := x + 1;
   END LOOP;

END;

Blog at WordPress.com.