Re: Having big trouble trying to improve performance of XML parsing...

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 1 May 2005 02:53:11 -0700
Message-ID: <1114941191.913200.150700@g14g2000cwa.googlegroups.com>

Hi Beri, given the XML is stored as CLOBs, you will always suffer this performance penalty. There is no quick fix.

> 1)Is there other way I could parse the
> XML to be more efficient as time?
> 2)I saw that increasing the number
> of values obtained with extractValue
> the performance of parsing decreases.

If this is more than a one-off data migration exercise, I suggest you move from storing XML as a CLOB to storing and manipulating the XML as schema validated table columns of XMLTYPE. This would most likely solve your immediate scalability and performance problems. See also "Indexing XMLType Columns" here (watch URL wrap):
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10790/xdb04cre.htm#i1031628

If your options limit you to using XML stored as CLOBs, something you might wish to investigate is indexing with "Oracle Text".

Regards

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

berin..._at_gmail.com wrote:
> Hi Oracle masters,
>
> I'm having big trouble trying to improve performance of XML parsing.
> With an XML of size 30k it takes around ONE SECOND.
>
> I'm using extractvalue() to extract values. Here is the code which
> inserts 3 rows in AMD_ADDRESS_NAME table :
>
>
> declare
> myClobWithXML CLOB;
> begin
> select CLOB_VAL into myClobWithXML from ADDRESS_TABLE where ID=1;
>
>
> for c2rec in (SELECT
> extractvalue(x,xpathPrefix || 'nameInfo['||nr||']/nameType/t­ext()')
> as
> nameType,
> extractvalue(x,xpathPrefix ||

'nameInfo['||nr||']/nameLine1/­text()')
> as
> nameLine1,
> extractvalue(x,xpathPrefix ||

'nameInfo['||nr||']/nameLine2/­text()')
> as
> nameLine2,
> extractvalue(x,xpathPrefix ||
> 'nameInfo['||nr||']/effectiveD­ate/text()') as effectiveDate,
> extractvalue(x,xpathPrefix ||
> 'nameInfo['||nr||']/expiration­Date/text()') as expirationDate
> FROM (SELECT xmltype(QUEUE_INTERFACE.XML_GL­OBAL_VAR) x FROM dual)
> ---FROM (SELECT xmltype( myClobWithXML ) x FROM dual)
> , qt_dummy_numbers
> where extractvalue(x,xpathPrefix ||
> 'nameInfo['||nr||']/nameType/t­ext()') IS NOT NULL
> and extractvalue(x,xpathPrefix ||
> 'addressInfo['||nr||']/address­Type/text()') IS NOT NULL)
> --would be: table(XMLSequence())
> loop
>
>
> INSERT INTO AMD_ADDRESS_NAME (ID, NAME_TYPE, NAME_LINE1,
NAME_LINE2,
> EFFECTIVE_DATE, EXPIRATION_DATE)
> VALUES (SEQ_ADDRESS_NAME.NEXTVAL, c2rec.nameType, c2rec.nameLine1,
> c2rec.nameLine2, to_date(c2rec.effectiveDate,'m­m/dd/yyyy
> hh24:mi:ss'),to_date(c2rec.exp­irationDate,'mm/dd/yyyy
hh24:mi:ss'));
>
>
> end loop;
>
>
> commit;
> end;
>
>
> In this example I select only a few values ( nameType, nameLine1,
> nameLine2, effectiveDate, expirationDate ). For the real case I
select
> around 30 values.
>
>
> Problems:
> 1)Is there other way I could parse the XML to be more efficient as
> time?
> 2)I saw that increasing the number of values obtained with
extractValue
>
> the performance of parsing decreases. Could be that for each value
> obtained with extractValue() it parses the XML?
>
>
> Many Thanks,
> Beri
> PL/SQL Developer
Received on Sun May 01 2005 - 04:53:11 CDT