4 Replies Latest reply on Jun 8, 2016 7:42 AM by Steven Hawkins

    Join subquery field - Performance issue

    Mark Tawk Novice

      I have the following query on H2 database that has a join linking a table through subquery field:

       

      select "Customer"."CustomerID"   as "Customer_CustomerID","Customer"."LastName" as "Customer_LastName","Customer"."BirthDate" as "Customer_BirthDate"

      ,(select "StreamCards_sub_f"."Merchant_MerchantID"  as "StreamCards_Merchant_Merchan" from  "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."StreamCards" "StreamCards_sub_f" where  ("Customer"."CustomerID" = "StreamCards_sub_f"."Customer_CustomerID")  LIMIT 0 , 1) as "SubQuery StreamCards"

      ,"Merchant"."Merchant" as "Merchant_Merchant","Merchant"."Category_CategoryID"   as "Merchant_Category_CategoryID","Category"."Category" as "Category_Category"

      from  "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."Customer" "Customer"

      LEFT JOIN "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."Merchant" "Merchant" ON   ( ("Merchant"."MerchantID"   =  (select "StreamCards_sub_f"."Merchant_MerchantID"  as "StreamCards_Merchant_Merchan" from  "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."StreamCards" "StreamCards_sub_f" where  ("Customer"."CustomerID" = "StreamCards_sub_f"."Customer_CustomerID")  LIMIT 0 , 1)) ) \n

      LEFT JOIN "h2dbimplify_data_cebankModel_fR"."implify_data_cebank"."Category" "Category" ON "Merchant"."Category_CategoryID" = "Category"."CategoryID"

      where  ( (DAYOFMONTH("Customer"."BirthDate") =  DAYOFMONTH( CURDATE() )) AND (MONTH("Customer"."BirthDate") =  MONTH( CURDATE() )) ) 

      LIMIT 0 , 10

       

      the below jdbc query executed by teiid is taking too much time (if the parenthesis grouping the from table with the first join is removed, the query executes fast and returns the right results):

       

      SELECT g_0."CustomerID" AS c_0, g_0."LastName" AS c_1, g_0."BirthDate" AS c_2, (SELECT g_4."Merchant_MerchantID" AS c_0 FROM "implify_data_cebank"."StreamCards" AS g_4 WHERE g_4."Customer_CustomerID" = g_0."CustomerID" LIMIT 1) AS c_3, g_1."Merchant" AS c_4, g_1."Category_CategoryID" AS c_5, g_3."Category" AS c_6 FROM ("implify_data_cebank"."Customer" AS g_0 LEFT OUTER JOIN "implify_data_cebank"."Merchant" AS g_1 ON g_1."MerchantID" = (SELECT g_2."Merchant_MerchantID" AS c_0 FROM "implify_data_cebank"."StreamCards" AS g_2 WHERE g_2."Customer_CustomerID" = g_0."CustomerID" LIMIT 1)) LEFT OUTER JOIN "implify_data_cebank"."Category" AS g_3 ON g_1."Category_CategoryID" = g_3."CategoryID" WHERE day_of_month(g_0."BirthDate") = ? AND month(g_0."BirthDate") = ? LIMIT 10


      Here is the execution plan:


      <?xml version='1.0' encoding='UTF-8'?><node name="AccessNode"><property name="Relational Node ID"><value>2</value></property><property name="Output Columns"><value>Customer_CustomerID (integer)</value><value>Customer_LastName (string)</value><value>Customer_BirthDate (timestamp)</value><value>SubQuery StreamCards (integer)</value><value>Merchant_Merchant (string)</value><value>Merchant_Category_CategoryID (integer)</value><value>Category_Category (string)</value></property><property name="Statistics"><value>Node Output Rows: 3</value><value>Node Next Batch Process Time: 0</value><value>Node Cumulative Next Batch Process Time: 0</value><value>Node Cumulative Process Time: 258613</value><value>Node Next Batch Calls: 2</value><value>Node Blocks: 1</value></property><property name="Cost Estimates"><value>Estimated Node Cardinality: 10.0</value></property><property name="Query"><value>SELECT g_0.CustomerID AS c_0, g_0.LastName AS c_1, g_0.BirthDate AS c_2, (SELECT g_4.Merchant_MerchantID AS c_0 FROM h2dbimplify_data_cebankModel_fR.implify_data_cebank.StreamCards AS g_4 WHERE g_4.Customer_CustomerID = g_0.CustomerID LIMIT 1) AS c_3, g_1.Merchant AS c_4, g_1.Category_CategoryID AS c_5, g_3.Category AS c_6 FROM (h2dbimplify_data_cebankModel_fR.implify_data_cebank.Customer AS g_0 LEFT OUTER JOIN h2dbimplify_data_cebankModel_fR.implify_data_cebank.Merchant AS g_1 ON g_1.MerchantID = (SELECT g_2.Merchant_MerchantID AS c_0 FROM h2dbimplify_data_cebankModel_fR.implify_data_cebank.StreamCards AS g_2 WHERE g_2.Customer_CustomerID = g_0.CustomerID LIMIT 1)) LEFT OUTER JOIN h2dbimplify_data_cebankModel_fR.implify_data_cebank.Category AS g_3 ON g_1.Category_CategoryID = g_3.CategoryID WHERE (DAYOFMONTH(g_0.BirthDate) = DAYOFMONTH(CURDATE())) AND (MONTH(g_0.BirthDate) = MONTH(CURDATE())) LIMIT 10</value></property><property name="Model Name"><value>h2dbimplify_data_cebankModel_fR</value></property><property name="Data Bytes Sent"><value>0</value></property><property name="Planning Time"><value>91</value></property></node>