Query slow with XML field in results

Query slow with XML field in results

Postby azuckerman » Mon 13 Oct 2014 19:21

I have a query executing on a table that has a CLOB column type that contains XML data.

Using this query (without the CLOB column), everything is fast and smooth:

SELECT si.INVOICE_NUM, si.INVOICE_DT, si.EXPIRE_DT, si.CLI_LEGAL_NAME, si.READY
FROM INVOICE si

However, adding the CLOB column causes significant delays to moving through the results:

SELECT si.INVOICE_NUM, si.INVOICE_DT, si.EXPIRE_DT, si.CLI_LEGAL_NAME, si.READY, si.INVOICE_XML
FROM INVOICE si

What can I do to make the scrolling more like the first query when using the second?
azuckerman
 
Posts: 4
Joined: Mon 13 Oct 2014 19:11

Re: Query slow with XML field in results

Postby alexa » Tue 14 Oct 2014 07:25

Could you please provide us the CREATE definition of the table and a couple of records with data so we can reproduce it on our side?

Also, please provide us the version of the Oracle server and Oracle client (if applicable).
alexa
Devart Team
 
Posts: 2171
Joined: Fri 24 Jun 2011 14:17

Re: Query slow with XML field in results

Postby azuckerman » Tue 14 Oct 2014 15:47

I cannot post the data as it is private invoice data.

Code: Select all
CREATE TABLE INVOICE (
  INVOICE_NUM    VARCHAR2(8 BYTE)  NOT NULL,
  INVOICE_DT     DATE              NOT NULL,
  EXPIRE_DT      DATE,
  CLI_NUM        VARCHAR2(8 BYTE)  NOT NULL,
  CLI_LEGAL_NAME VARCHAR2(50 BYTE) NOT NULL,
  CREATE_DT      DATE              DEFAULT TRUNC(sysdate) NOT NULL,
  PRINTED        NUMBER(*, 0)      DEFAULT 0 NOT NULL,
  ONHOLD         NUMBER(*, 0)      DEFAULT 0 NOT NULL,
  INVOICE_XML    CLOB              NOT NULL,
  READY          NUMBER(*, 0)      DEFAULT 0 NOT NULL,
  CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_NUM, INVOICE_DT)
)
TABLESPACE USERS
STORAGE (INITIAL 64 K
         NEXT 1 M
         MAXEXTENTS UNLIMITED)
LOGGING;


Server version information:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Client version information:
Windows 7 64bit
11.2.0
64bit

An example of the XML:
Code: Select all
<?xml version="1.0" encoding="utf-16"?>
<Invoice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Cli_Num>01234567</Cli_Num>
  <Comm_Id>987654</Comm_Id>
  <Invoice_Num>00000001</Invoice_Num>
  <Invoice_Dt>2014-10-01T00:00:00</Invoice_Dt>
  <Cli_Legal_Name>COMPANY NAME</Cli_Legal_Name>
  <Contact_Full_Name>CONTACT NAME</Contact_Full_Name>
  <Contact_Full_Address>PO BOX 12345
AUSTIN TX 78701</Contact_Full_Address>
  <Expire_Dt>2014-10-30T00:00:00</Expire_Dt>
  <todays_Dt>2014-10-14T16:54:22.7291554-05:00</todays_Dt>
  <Total_Amt>$420.00</Total_Amt>
  <Credit_Amt>$0.00</Credit_Amt>
  <Pending_Amt>$0.00</Pending_Amt>
  <Total_Amt_To_Pay>$420.00</Total_Amt_To_Pay>
  <Late_Amt>$630.00</Late_Amt>
  <Delinquent_Amt>$840.00</Delinquent_Amt>
  <Account_Types>
    <anyType xsi:type="xsd:string">0911</anyType>
  </Account_Types>
  <Details>
    <anyType xsi:type="Invoice_Detail">
      <Acct_Num>0000001</Acct_Num>
      <Facility_Name>LOCATION NAME</Facility_Name>
      <Facility_Street />
      <Facility_Full_Address />
      <Acct_Type_Cd>0911</Acct_Type_Cd>
      <Acct_Type_Desc>PRODUCT</Acct_Type_Desc>
      <_Fee_Amt>420</_Fee_Amt>
      <_Credit_Amt>0</_Credit_Amt>
      <_Late_Amt>630</_Late_Amt>
      <_Delinquent_Amt>840</_Delinquent_Amt>
      <Worksheets_Included>N/A</Worksheets_Included>
      <Expire_Dt>0001-01-01T00:00:00</Expire_Dt>
      <Fee_Amt>$420.00</Fee_Amt>
      <Credit_Amt>$0.00</Credit_Amt>
      <Late_Amt>$630.00</Late_Amt>
      <Delinquent_Amt>$840.00</Delinquent_Amt>
    </anyType>
  </Details>
  <Details_X />
  <Details_Y />
  <Details_Z />
</Invoice>
azuckerman
 
Posts: 4
Joined: Mon 13 Oct 2014 19:11

Re: Query slow with XML field in results

Postby alexa » Wed 15 Oct 2014 08:36

Thank you for the reply. We are currently investigating this issue and will get back to you as soon as possible.

I cannot post the data as it is private invoice data.

You can send a reply straight to our support system at supportATdevartDOTcom, so we will keep further correspondence with you on this issue via e-mail.
alexa
Devart Team
 
Posts: 2171
Joined: Fri 24 Jun 2011 14:17


Return to dbForge for Oracle