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

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
    ACCEPTED ANSWER

    Re: Long running query

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

      Re: Long running query

      ‏2012-04-03T16:58:10Z  in response to VV4H_mario_fernandes
      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
        ACCEPTED ANSWER

        Re: Long running query

        ‏2012-04-03T18:48:39Z  in response to VV4H_mario_fernandes
        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
          ACCEPTED ANSWER

          Re: Long running query

          ‏2012-04-03T19:51:10Z  in response to MatthiasNicola
          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
            ACCEPTED ANSWER

            Re: Long running query

            ‏2012-04-03T20:12:28Z  in response to VV4H_mario_fernandes
            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/