Topic
  • 6 replies
  • Latest Post - ‏2012-04-04T14:06:38Z by VV4H_mario_fernandes
VV4H_mario_fernandes
43 Posts

Pinned topic Long running query

‏2012-04-03T16:29:17Z |
My query is taking a long time to run.

There are 2 sub queries. One is selecting from a xml table and the other from a relational table.
they each take less than 1 second when i run them separately
when i join then they take 30 mins or more
I see some tablescans on the xml_client in the attached explain plan.
Can anyone suggest how to improve the performance
SELECT XML_CLIENT_ID, LEGAL_NAME, COUNTRY_OF_RESIDENCE, COUNTRY_OF_RISK,
jbpm_task.task_name, task_creation_date ,user
, REGION
FROM KASPER.XML_CLIENT AS C,
XMLTABLE('$d/kasperSchema/TDSClient' PASSING C.XML_CLIENT AS "d" COLUMNS
XML_CLIENT_ID VARCHAR (100) PATH 'kasperClient/client/@id',
LEGAL_NAME VARCHAR (500) PATH 'kasperClient/client/legalEntity/legalName/text()',
COUNTRY_OF_RISK VARCHAR (100) PATH 'kasperClient/client/legalEntity/countryOfRisk/text()',
COUNTRY_OF_RESIDENCE VARCHAR (100) PATH 'kasperClient/client/address[1]/country/text()',
REGION VARCHAR (100) PATH 'fn:string-join(kasperClient/client/legalEntity/clientRegion/text(),",")'
)
AS X

inner join
(

SELECT B.STRINGVALUE_ AS CLIENT_ID, A.NAME_ AS TASK_NAME, A.CREATE_ AS TASK_CREATION_DATE,
A.ACTORID_ AS USER
FROM (KASPER.JBPM_TASKINSTANCE AS A LEFT OUTER JOIN (
SELECT STRINGVALUE_, TOKEN_
FROM KASPER.JBPM_VARIABLEINSTANCE
WHERE NAME_ = 'CLIENT_ID') AS B ON A.TOKEN_ = B.TOKEN_)
WHERE A.ISOPEN_ = 1
) jbpm_task
on
XML_client_id = jbpm_task.client_id

for read only
;
Updated on 2012-04-04T14:06:38Z at 2012-04-04T14:06:38Z by VV4H_mario_fernandes
  • VV4H_mario_fernandes
    43 Posts

    Re: Long running query

    ‏2012-04-03T16:32:39Z  
    There is indexes on both the client_id columns in the join clause.
  • VV4H_mario_fernandes
    43 Posts

    Re: Long running query

    ‏2012-04-03T16:58:10Z  
    There is indexes on both the client_id columns in the join clause.
    Sorry, the information about indexes is incorrect.
    There is no index on the STRINGVALUE_ AS CLIENT_ID column.
    There is an index on the XML_CLIENT_ID column
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Long running query

    ‏2012-04-03T18:48:39Z  
    Sorry, the information about indexes is incorrect.
    There is no index on the STRINGVALUE_ AS CLIENT_ID column.
    There is an index on the XML_CLIENT_ID column
    Hi Mario,

    in the explain I can see that a table scan is being performed on the right side of a nested loop join, i.e. the XML column is being scanned multiple times, which is obviously a poor execution plan.

    Without knowing more details, my initial idea is to move the join predicate into the row-generating expression of the XMLTABLE function, as shown below. This assumes that you have an XML index defined on /kasperSchema/TDSClient/kasperClient/client/@id.

    If this does not produce acceptable performance, please open a ticket (PMR) with us so that we can investigate in more detail and determine the optimal solution.

    
    SELECT XML_CLIENT_ID, LEGAL_NAME, COUNTRY_OF_RESIDENCE, COUNTRY_OF_RISK, jbpm_task.task_name, task_creation_date ,user , REGION FROM KASPER.XML_CLIENT AS C, XMLTABLE(
    '$d/kasperSchema/TDSClient[kasperClient/client/@id = $cid]' PASSING C.XML_CLIENT AS 
    "d", jbpm_task.client_id as 
    "cid" COLUMNS XML_CLIENT_ID VARCHAR (100)        PATH   
    'kasperClient/client/@id', LEGAL_NAME VARCHAR (500)           PATH   
    'kasperClient/client/legalEntity/legalName/text()', COUNTRY_OF_RISK  VARCHAR (100)     PATH   
    'kasperClient/client/legalEntity/countryOfRisk/text()', COUNTRY_OF_RESIDENCE VARCHAR (100) PATH   
    'kasperClient/client/address[1]/country/text()', REGION VARCHAR (100)               PATH   
    'fn:string-join(kasperClient/client/legalEntity/clientRegion/text(), ",") 
    ')  AS X , (SELECT B.STRINGVALUE_ AS CLIENT_ID, A.NAME_        AS TASK_NAME, A.CREATE_      AS TASK_CREATION_DATE, A.ACTORID_     AS USER FROM (KASPER.JBPM_TASKINSTANCE AS A LEFT OUTER JOIN (SELECT STRINGVALUE_, TOKEN_ FROM KASPER.JBPM_VARIABLEINSTANCE WHERE NAME_ = 
    'CLIENT_ID') AS B ON A.TOKEN_ = B.TOKEN_) WHERE A.ISOPEN_ = 1) jbpm_task ;
    

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • VV4H_mario_fernandes
    43 Posts

    Re: Long running query

    ‏2012-04-03T19:51:10Z  
    Hi Mario,

    in the explain I can see that a table scan is being performed on the right side of a nested loop join, i.e. the XML column is being scanned multiple times, which is obviously a poor execution plan.

    Without knowing more details, my initial idea is to move the join predicate into the row-generating expression of the XMLTABLE function, as shown below. This assumes that you have an XML index defined on /kasperSchema/TDSClient/kasperClient/client/@id.

    If this does not produce acceptable performance, please open a ticket (PMR) with us so that we can investigate in more detail and determine the optimal solution.

    <pre class="jive-pre"> SELECT XML_CLIENT_ID, LEGAL_NAME, COUNTRY_OF_RESIDENCE, COUNTRY_OF_RISK, jbpm_task.task_name, task_creation_date ,user , REGION FROM KASPER.XML_CLIENT AS C, XMLTABLE( '$d/kasperSchema/TDSClient[kasperClient/client/@id = $cid]' PASSING C.XML_CLIENT AS "d", jbpm_task.client_id as "cid" COLUMNS XML_CLIENT_ID VARCHAR (100) PATH 'kasperClient/client/@id', LEGAL_NAME VARCHAR (500) PATH 'kasperClient/client/legalEntity/legalName/text()', COUNTRY_OF_RISK VARCHAR (100) PATH 'kasperClient/client/legalEntity/countryOfRisk/text()', COUNTRY_OF_RESIDENCE VARCHAR (100) PATH 'kasperClient/client/address[1]/country/text()', REGION VARCHAR (100) PATH 'fn:string-join(kasperClient/client/legalEntity/clientRegion/text(), ",") ') AS X , (SELECT B.STRINGVALUE_ AS CLIENT_ID, A.NAME_ AS TASK_NAME, A.CREATE_ AS TASK_CREATION_DATE, A.ACTORID_ AS USER FROM (KASPER.JBPM_TASKINSTANCE AS A LEFT OUTER JOIN (SELECT STRINGVALUE_, TOKEN_ FROM KASPER.JBPM_VARIABLEINSTANCE WHERE NAME_ = 'CLIENT_ID') AS B ON A.TOKEN_ = B.TOKEN_) WHERE A.ISOPEN_ = 1) jbpm_task ; </pre>
    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Hi Mathias

    I am getting the following error

    "JBPM_TASK.CLIENT_ID" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.12.79

    Thanks

    Mario
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Long running query

    ‏2012-04-03T20:12:28Z  
    Hi Mathias

    I am getting the following error

    "JBPM_TASK.CLIENT_ID" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.12.79

    Thanks

    Mario
    Oops! My excuse is that I don't have your table definitions to test this :-).

    See if the following works. If not, please open a ticket and include the DDLs for all three tables and all their indexes.

    
    SELECT XML_CLIENT_ID, LEGAL_NAME, COUNTRY_OF_RESIDENCE, COUNTRY_OF_RISK, jbpm_task.task_name, task_creation_date ,user , REGION FROM KASPER.XML_CLIENT AS C, (SELECT B.STRINGVALUE_ AS CLIENT_ID, A.NAME_        AS TASK_NAME, A.CREATE_      AS TASK_CREATION_DATE, A.ACTORID_     AS USER FROM (KASPER.JBPM_TASKINSTANCE AS A LEFT OUTER JOIN (SELECT STRINGVALUE_, TOKEN_ FROM KASPER.JBPM_VARIABLEINSTANCE WHERE NAME_ = 
    'CLIENT_ID') AS B ON A.TOKEN_ = B.TOKEN_) WHERE A.ISOPEN_ = 1) jbpm_task, XMLTABLE(
    '$d/kasperSchema/TDSClient[kasperClient/client/@id = $cid]' PASSING C.XML_CLIENT AS 
    "d", jbpm_task.client_id as 
    "cid" COLUMNS XML_CLIENT_ID VARCHAR (100)        PATH   
    'kasperClient/client/@id', LEGAL_NAME VARCHAR (500)           PATH   
    'kasperClient/client/legalEntity/legalName/text()', COUNTRY_OF_RISK  VARCHAR (100)     PATH   
    'kasperClient/client/legalEntity/countryOfRisk/text()', COUNTRY_OF_RESIDENCE VARCHAR (100) PATH   
    'kasperClient/client/address[1]/country/text()', REGION VARCHAR (100)               PATH   
    'fn:string-join(kasperClient/client/legalEntity/clientRegion/text(), ",") 
    ')  AS X;
    



    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • VV4H_mario_fernandes
    43 Posts

    Re: Long running query

    ‏2012-04-04T14:06:38Z  
    Oops! My excuse is that I don't have your table definitions to test this :-).

    See if the following works. If not, please open a ticket and include the DDLs for all three tables and all their indexes.

    <pre class="jive-pre"> SELECT XML_CLIENT_ID, LEGAL_NAME, COUNTRY_OF_RESIDENCE, COUNTRY_OF_RISK, jbpm_task.task_name, task_creation_date ,user , REGION FROM KASPER.XML_CLIENT AS C, (SELECT B.STRINGVALUE_ AS CLIENT_ID, A.NAME_ AS TASK_NAME, A.CREATE_ AS TASK_CREATION_DATE, A.ACTORID_ AS USER FROM (KASPER.JBPM_TASKINSTANCE AS A LEFT OUTER JOIN (SELECT STRINGVALUE_, TOKEN_ FROM KASPER.JBPM_VARIABLEINSTANCE WHERE NAME_ = 'CLIENT_ID') AS B ON A.TOKEN_ = B.TOKEN_) WHERE A.ISOPEN_ = 1) jbpm_task, XMLTABLE( '$d/kasperSchema/TDSClient[kasperClient/client/@id = $cid]' PASSING C.XML_CLIENT AS "d", jbpm_task.client_id as "cid" COLUMNS XML_CLIENT_ID VARCHAR (100) PATH 'kasperClient/client/@id', LEGAL_NAME VARCHAR (500) PATH 'kasperClient/client/legalEntity/legalName/text()', COUNTRY_OF_RISK VARCHAR (100) PATH 'kasperClient/client/legalEntity/countryOfRisk/text()', COUNTRY_OF_RESIDENCE VARCHAR (100) PATH 'kasperClient/client/address[1]/country/text()', REGION VARCHAR (100) PATH 'fn:string-join(kasperClient/client/legalEntity/clientRegion/text(), ",") ') AS X; </pre>


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    Thanks Matthias. The query now takes 1/2 second as compared to 30 minutes.
    Ur the Best!!

    :-)