Uploaded image for project: 'jUDDI'
  1. jUDDI
  2. JUDDI-89

JDBC Datastore performance improvements

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 0.9rc4
    • Fix Version/s: 3.1.5
    • Component/s: None
    • Labels:
      None
    • Environment:
      Oracle

      Description

      Hi,

      Please find a huge patch attached.

      We have used Juddi with more than an hundred business services and we had some issues. This patch correct these issues :

      • Juddi was very slow with more than 1000 business services.
      • Oracle cannot handle more than 1000 elements in SQL "IN" request, so Juddi crashed when a business entity contained more than 1000 services.

      For the "IN" problem we added a method in util/Config which gives the maximum element the db can handle in IN requests.

      For the performance problem we had to rewrite a lot of the JDBC datastore. Basically the thing is to avoid to do too much sql requests by grouping them. As an example instead of the method fetchService() we use the method fetchServices(), etc.

      Thanks to these corrections we had great performance improvement : the getbusinessdetail used to take more than 40 seconds on big business entity, now it takes about 6 seconds.

      This patch applies to v0.9rc4.

      Regards,

      Rémi Flament.

      1. juddi.patch
        332 kB
        Rémi Flament
      2. juddi3.patch
        0.5 kB
        Rémi Flament
      3. patch2.patch
        8 kB
        Rémi Flament

        Issue Links

          Activity

          Hide
          rflament Rémi Flament added a comment -

          Here is a second patch that correct bugs added by the first one :

          Index: BindingCategoryTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/BindingCategoryTable.java,v
          retrieving revision 1.4
          diff -u -r1.4 BindingCategoryTable.java
          — BindingCategoryTable.java 11 Jul 2006 09:13:02 -0000 1.4
          +++ BindingCategoryTable.java 13 Jul 2006 08:13:34 -0000
          @@ -153,6 +153,8 @@
          PreparedStatement statement = null;
          ResultSet resultSet = null;

          + if (bindingsKeys!=null && !bindingsKeys.isEmpty())
          + {
          try

          { DynamicQuery query = new DynamicQuery(); @@ -207,6 +209,8 @@ }

          catch (Exception e)

          { /* ignored */ }
          }
          + }
          + return results;
          }

          /**
          Index: BindingDescTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/BindingDescTable.java,v
          retrieving revision 1.4
          diff -u -r1.4 BindingDescTable.java
          — BindingDescTable.java 11 Jul 2006 09:13:02 -0000 1.4
          +++ BindingDescTable.java 13 Jul 2006 08:13:34 -0000
          @@ -144,6 +144,8 @@
          PreparedStatement statement = null;
          ResultSet resultSet = null;

          + if (bindingsKeys!=null && !bindingsKeys.isEmpty())
          + {
          try { DynamicQuery query = new DynamicQuery(); @@ -197,6 +199,8 @@ } catch (Exception e) { /* ignored */ }

          }
          + }
          + return results;
          }

          /**
          Index: BindingTemplateTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/BindingTemplateTable.java,v
          retrieving revision 1.4
          diff -u -r1.4 BindingTemplateTable.java
          — BindingTemplateTable.java 11 Jul 2006 09:13:02 -0000 1.4
          +++ BindingTemplateTable.java 13 Jul 2006 08:13:34 -0000
          @@ -239,6 +239,8 @@
          PreparedStatement statement = null;
          ResultSet resultSet = null;

          + if (bindingsKeys!=null && !bindingsKeys.isEmpty())
          + {
          try

          { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -293,6 +295,8 @@ }

          catch (Exception e)

          { /* ignored */ }
          }
          + }
          + return results;
          }

          /**
          @@ -358,6 +362,8 @@
          PreparedStatement statement = null;
          ResultSet resultSet = null;

          + if (servicesKeys!=null && !servicesKeys.isEmpty())
          + {
          try { DynamicQuery query = new DynamicQuery(); @@ -426,6 +432,8 @@ } catch (Exception e) { /* ignored */ }

          }
          + }
          + return results;
          }

          /**
          Index: BusinessServiceTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/BusinessServiceTable.java,v
          retrieving revision 1.3
          diff -u -r1.3 BusinessServiceTable.java
          — BusinessServiceTable.java 11 Jul 2006 09:13:02 -0000 1.3
          +++ BusinessServiceTable.java 13 Jul 2006 08:13:34 -0000
          @@ -198,6 +198,9 @@
          ResultSet resultSet = null;
          ArrayList services = new ArrayList();

          + if (servicesKeys!=null && !servicesKeys.isEmpty())
          + {
          +
          try

          { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -246,6 +249,8 @@ }

          catch (Exception e)

          { /* ignored */ }
          }
          + }
          + return services;
          }

          /**
          Index: ServiceCategoryTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/ServiceCategoryTable.java,v
          retrieving revision 1.4
          diff -u -r1.4 ServiceCategoryTable.java
          — ServiceCategoryTable.java 11 Jul 2006 09:13:02 -0000 1.4
          +++ ServiceCategoryTable.java 13 Jul 2006 08:13:34 -0000
          @@ -152,6 +152,8 @@
          PreparedStatement statement = null;
          ResultSet resultSet = null;

          + if (servicesKeys!=null && !servicesKeys.isEmpty())
          + {
          try { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -206,6 +208,8 @@ } catch (Exception e) { /* ignored */ }

          }
          + }
          + return results;
          }

          /**
          Index: ServiceDescTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/ServiceDescTable.java,v
          retrieving revision 1.3
          diff -u -r1.3 ServiceDescTable.java
          — ServiceDescTable.java 11 Jul 2006 09:13:02 -0000 1.3
          +++ ServiceDescTable.java 13 Jul 2006 08:13:34 -0000
          @@ -143,7 +143,9 @@
          Map results = new HashMap();
          PreparedStatement statement = null;
          ResultSet resultSet = null;
          -
          +
          + if (servicesKeys!=null && !servicesKeys.isEmpty())
          + {
          try

          { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -196,6 +198,8 @@ }

          catch (Exception e)

          { /* ignored */ }
          }
          + }
          + return results;
          }

          /**
          Index: ServiceNameTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/ServiceNameTable.java,v
          retrieving revision 1.3
          diff -u -r1.3 ServiceNameTable.java
          — ServiceNameTable.java 11 Jul 2006 09:13:02 -0000 1.3
          +++ ServiceNameTable.java 13 Jul 2006 08:13:34 -0000
          @@ -146,6 +146,8 @@

          Map results = new HashMap();

          + if (servicesKeys!=null && !servicesKeys.isEmpty())
          + {
          try { DynamicQuery query = new DynamicQuery(); query.append("("); @@ -200,6 +202,8 @@ } catch (Exception e) { /* ignored */ }

          }
          + }
          + return results;
          }

          /**
          Index: TModelInstanceInfoDescTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/TModelInstanceInfoDescTable.java,v
          retrieving revision 1.4
          diff -u -r1.4 TModelInstanceInfoDescTable.java
          — TModelInstanceInfoDescTable.java 11 Jul 2006 09:13:02 -0000 1.4
          +++ TModelInstanceInfoDescTable.java 13 Jul 2006 08:13:34 -0000
          @@ -155,6 +155,9 @@
          ResultSet resultSet = null;
          Map results = new HashMap();

          +
          + if (bindingsKeys!=null && !bindingsKeys.isEmpty())
          + {
          try

          { DynamicQuery query = new DynamicQuery(); @@ -216,6 +219,8 @@ }

          catch (Exception e)

          { /* ignored */ }
          }
          + }
          + return results;
          }

          /**
          Index: TModelInstanceInfoTable.java
          ===================================================================
          RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/TModelInstanceInfoTable.java,v
          retrieving revision 1.4
          diff -u -r1.4 TModelInstanceInfoTable.java
          — TModelInstanceInfoTable.java 11 Jul 2006 09:13:02 -0000 1.4
          +++ TModelInstanceInfoTable.java 13 Jul 2006 08:13:34 -0000
          @@ -169,6 +169,8 @@
          ResultSet resultSet = null;
          Map results = new HashMap();

          + if (bindingsKeys!=null && !bindingsKeys.isEmpty())
          + {
          try { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -248,6 +250,8 @@ } catch (Exception e) { /* ignored */ }

          }
          + }
          + return results;
          }

          /**

          Show
          rflament Rémi Flament added a comment - Here is a second patch that correct bugs added by the first one : Index: BindingCategoryTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/BindingCategoryTable.java,v retrieving revision 1.4 diff -u -r1.4 BindingCategoryTable.java — BindingCategoryTable.java 11 Jul 2006 09:13:02 -0000 1.4 +++ BindingCategoryTable.java 13 Jul 2006 08:13:34 -0000 @@ -153,6 +153,8 @@ PreparedStatement statement = null; ResultSet resultSet = null; + if (bindingsKeys!=null && !bindingsKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); @@ -207,6 +209,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /** Index: BindingDescTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/BindingDescTable.java,v retrieving revision 1.4 diff -u -r1.4 BindingDescTable.java — BindingDescTable.java 11 Jul 2006 09:13:02 -0000 1.4 +++ BindingDescTable.java 13 Jul 2006 08:13:34 -0000 @@ -144,6 +144,8 @@ PreparedStatement statement = null; ResultSet resultSet = null; + if (bindingsKeys!=null && !bindingsKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); @@ -197,6 +199,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /** Index: BindingTemplateTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/BindingTemplateTable.java,v retrieving revision 1.4 diff -u -r1.4 BindingTemplateTable.java — BindingTemplateTable.java 11 Jul 2006 09:13:02 -0000 1.4 +++ BindingTemplateTable.java 13 Jul 2006 08:13:34 -0000 @@ -239,6 +239,8 @@ PreparedStatement statement = null; ResultSet resultSet = null; + if (bindingsKeys!=null && !bindingsKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -293,6 +295,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /** @@ -358,6 +362,8 @@ PreparedStatement statement = null; ResultSet resultSet = null; + if (servicesKeys!=null && !servicesKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); @@ -426,6 +432,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /** Index: BusinessServiceTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/BusinessServiceTable.java,v retrieving revision 1.3 diff -u -r1.3 BusinessServiceTable.java — BusinessServiceTable.java 11 Jul 2006 09:13:02 -0000 1.3 +++ BusinessServiceTable.java 13 Jul 2006 08:13:34 -0000 @@ -198,6 +198,9 @@ ResultSet resultSet = null; ArrayList services = new ArrayList(); + if (servicesKeys!=null && !servicesKeys.isEmpty()) + { + try { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -246,6 +249,8 @@ } catch (Exception e) { /* ignored */ } } + } + return services; } /** Index: ServiceCategoryTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/ServiceCategoryTable.java,v retrieving revision 1.4 diff -u -r1.4 ServiceCategoryTable.java — ServiceCategoryTable.java 11 Jul 2006 09:13:02 -0000 1.4 +++ ServiceCategoryTable.java 13 Jul 2006 08:13:34 -0000 @@ -152,6 +152,8 @@ PreparedStatement statement = null; ResultSet resultSet = null; + if (servicesKeys!=null && !servicesKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -206,6 +208,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /** Index: ServiceDescTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/ServiceDescTable.java,v retrieving revision 1.3 diff -u -r1.3 ServiceDescTable.java — ServiceDescTable.java 11 Jul 2006 09:13:02 -0000 1.3 +++ ServiceDescTable.java 13 Jul 2006 08:13:34 -0000 @@ -143,7 +143,9 @@ Map results = new HashMap(); PreparedStatement statement = null; ResultSet resultSet = null; - + + if (servicesKeys!=null && !servicesKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -196,6 +198,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /** Index: ServiceNameTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/ServiceNameTable.java,v retrieving revision 1.3 diff -u -r1.3 ServiceNameTable.java — ServiceNameTable.java 11 Jul 2006 09:13:02 -0000 1.3 +++ ServiceNameTable.java 13 Jul 2006 08:13:34 -0000 @@ -146,6 +146,8 @@ Map results = new HashMap(); + if (servicesKeys!=null && !servicesKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); query.append("("); @@ -200,6 +202,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /** Index: TModelInstanceInfoDescTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/TModelInstanceInfoDescTable.java,v retrieving revision 1.4 diff -u -r1.4 TModelInstanceInfoDescTable.java — TModelInstanceInfoDescTable.java 11 Jul 2006 09:13:02 -0000 1.4 +++ TModelInstanceInfoDescTable.java 13 Jul 2006 08:13:34 -0000 @@ -155,6 +155,9 @@ ResultSet resultSet = null; Map results = new HashMap(); + + if (bindingsKeys!=null && !bindingsKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); @@ -216,6 +219,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /** Index: TModelInstanceInfoTable.java =================================================================== RCS file: g:/local/cvs/cvs-uddi/juddi/src/java/org/apache/juddi/datastore/jdbc/TModelInstanceInfoTable.java,v retrieving revision 1.4 diff -u -r1.4 TModelInstanceInfoTable.java — TModelInstanceInfoTable.java 11 Jul 2006 09:13:02 -0000 1.4 +++ TModelInstanceInfoTable.java 13 Jul 2006 08:13:34 -0000 @@ -169,6 +169,8 @@ ResultSet resultSet = null; Map results = new HashMap(); + if (bindingsKeys!=null && !bindingsKeys.isEmpty()) + { try { DynamicQuery query = new DynamicQuery(); query.append("(" + selectInSQL); @@ -248,6 +250,8 @@ } catch (Exception e) { /* ignored */ } } + } + return results; } /**
          Hide
          rflament Rémi Flament added a comment -

          Another bug corrected :

          — C:\salto\FindServiceByNameQuery.java Thu Jul 13 17:26:14 2006
          +++ C:\salto\FindServiceByNameQuery2.java Thu Jul 13 17:26:25 2006
          @@ -173,7 +173,7 @@
          if (keysIn == null)
          return;

          • sql.append("AND ( S.SERVICE_KEY IN (");
            + sql.append("AND S.SERVICE_KEY IN (");

          int keyCount = keysIn.size();
          int count = 0;
          @@ -193,7 +193,7 @@
          }
          }

          • sql.append(")) ");
            + sql.append(") ");
            }

          /**

          Show
          rflament Rémi Flament added a comment - Another bug corrected : — C:\salto\FindServiceByNameQuery.java Thu Jul 13 17:26:14 2006 +++ C:\salto\FindServiceByNameQuery2.java Thu Jul 13 17:26:25 2006 @@ -173,7 +173,7 @@ if (keysIn == null) return; sql.append("AND ( S.SERVICE_KEY IN ("); + sql.append("AND S.SERVICE_KEY IN ("); int keyCount = keysIn.size(); int count = 0; @@ -193,7 +193,7 @@ } } sql.append(")) "); + sql.append(") "); } /**
          Hide
          kurtstam Kurt T Stam added a comment -

          Hey Tom,

          If you have an example with lots of services then maybe we can check if this patch helps.

          Show
          kurtstam Kurt T Stam added a comment - Hey Tom, If you have an example with lots of services then maybe we can check if this patch helps.
          Hide
          kurtstam Kurt T Stam added a comment -

          moving to 3.0

          Show
          kurtstam Kurt T Stam added a comment - moving to 3.0
          Hide
          kurtstam Kurt T Stam added a comment -

          Unfortunately it looks like our code has changed too much for the patch to be useful any longer. However we issue still exist in the current codebase and it is time to address it as other users have run into the same issue. We should look into splitting the IN clause up into multiple sections like:

          bla_id in (2,3,5,6,12,19)

          to

          bla_id in (2,3)
          or
          bla_id in (5,6)
          or
          bla_id in (12,19)

          Also, instead of IN you can use = ANY, or = SOME, or EXISTS.
          It could also increase performance of the query....

          So we need to come up with some good test case and then go after this.

          --Kurt

          Show
          kurtstam Kurt T Stam added a comment - Unfortunately it looks like our code has changed too much for the patch to be useful any longer. However we issue still exist in the current codebase and it is time to address it as other users have run into the same issue. We should look into splitting the IN clause up into multiple sections like: bla_id in (2,3,5,6,12,19) to bla_id in (2,3) or bla_id in (5,6) or bla_id in (12,19) Also, instead of IN you can use = ANY, or = SOME, or EXISTS. It could also increase performance of the query.... So we need to come up with some good test case and then go after this. --Kurt
          Hide
          kurtstam Kurt T Stam added a comment -

          This is now fixed (with the fix applied as part of JUDDI-569)

          Show
          kurtstam Kurt T Stam added a comment - This is now fixed (with the fix applied as part of JUDDI-569 )

            People

            • Assignee:
              kurtstam Kurt T Stam
              Reporter:
              rflament Rémi Flament
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development