5 Replies Latest reply: May 5, 2014 10:10 AM by user12047959 RSS

    Cartesian Query

    user12047959

      Hi,

      I'm using Toplink 11g with Weblogic 10.3.0.

      The application is generating query having cartesian product on one single table; here goes an example;

       

      SELECT t0.ID_REQUEST,

      t0.RD_NUM_RETRY,

      t0.RD_START_TIME,

      t0.EVENT_ID,

      t0.RD_END_TIME,

      t0.MSISDN,

      t0.RD_STATUS,

      t0.RET_MESSAGE,

      t0.REQUEST,

      t0.RD_BILL_DATE,

      t0.SERVICE_CLASS,

      t0.CSP,

      t0.SERVICE_ID,

      t0.RET_CODE,

      t0.SERVICE_KEY,

      t0.RD_ID,

      t0.SERVICE_NAME,

      t0.RD_CESS,

      t0.TOTAL_AMOUNT,

      t0.BILLED_PARTIAL

      FROM BILLING_NOT_SUCCESS t0, BILLING_NOT_SUCCESS t1

      WHERE ( ( ( ( (t0.RD_STATUS = 'PARKED') AND (t0.RD_END_TIME > SYSDATE))

      AND (t0.RD_CESS = 0))

      AND (t1.MSISDN = xxxxxxxxx))

       

      Is there any particular reason for this or it's simply an error?

      I'm afraid of the owerhead generated by this cartesian product: How can I (should I?) avoid Toplink to generate query of this kind?

      Thank you veri much indeed.

      AND (t1.RET_CODE IN ('006', '201', '058')));

       

      L.D.

        • 1. Re: Cartesian Query
          cdelahun

          To help, you would need to show the code creating and executing the query - there will be a mistake in the code generating the expression criteria that is using a different expression builder for the MSISDN = xxxxxxxxx portion of the query, as it was likely to be included as a filter on the data being returned.

           

          Best Regards,
          Chris

          • 2. Re: Cartesian Query
            user12047959

            Hi,

            thank you very much for answering.

            I'm using toplink, included adding toplink.jar in classpath of my Weblogic project.

            Weblogic version is 10.3.0. (no JPA2.0). It's used to implement data access metods beeing used by SINGLETON Clustered application.

            Sessions are configured as SERVERSessions distributed by a broker.

            Sessions are configured in sessions.xml.

             

            Thi is the most external methos building the query:

            public List<BillingNotSuccess> getFNDRecords(String msisdn) throws Exception {

                 List<BillingNotSuccess> billRes = null;

                 Expression exp = null, exp1 = null;

                 exp = getConditions().and(exprBuild.get("msisdn").equal(msisdn));

                 String retCodeCondition = null;

                 String valueR = configurator.getParameterValue(Parameters.PARAM_RETRY_CODES_RELOAD);

                 logger.debug("mcss.retry.codes.reload: "+valueR);

                 if (valueR != null && !valueR.equalsIgnoreCase("")){

                      retCodeCondition = valueR;

                 }

                 String valueP = configurator.getParameterValue(Parameters.PARAM_RETRY_CODES_PARTIAL);

                 logger.debug("mcss.retry.codes.partial: "+valueP);

                 if (valueP != null && !valueP.equalsIgnoreCase("")){

                      retCodeCondition = (retCodeCondition!=null)?retCodeCondition+","+valueP:valueP;

                 }

                 if (retCodeCondition != null){

                      exp1 = exprBuild.get("retCode").in(retCodeCondition.split(","));

                 }

                 billRes =  readWithConditionOrdering(BillingNotSuccess.class, "rdStartTime", exp.and(exp1));

                 traceResult(billRes);

                 return billRes;

            }

             

            This is the method used to build AND conditions shared by other business methods:

            private Expression getConditions() throws Exception {

                 Expression exp = null, exp1 = null, exp2 = null;

                 exprBuild = new ExpressionBuilder(BillingNotSuccess.class);

                 exp = exprBuild.get("rdStatus").equal("PARKED");

                 exp1 = exprBuild.get("rdEndTime").greaterThan(new Date());

                 exp2 = exprBuild.get("rdCess").equal(0);

                 return exp.and(exp1).and(exp2);

            }

             

            Thank you very much again and please let me know if I can help with any further details.

            Best regards.

             

            L.D.

            • 3. Re: Cartesian Query
              user12047959

              One more thing:

              the table BILLING_NOT_SUCCESS has a CLOB column: REQUEST.

              Thanks.

               

              L.D.

              • 4. Re: Cartesian Query
                cdelahun

                The code looks fine if it were running in a single thread.  My guess is that you are running this code concurrently; this code is not thread safe as the getConditions method is changing the exprBuild instance used in other methods.  If this method is called by a different process, it would explain the issue you are seeing where the last two conditions use a different expressionbuilder than the first ones created within the getConditions method. 

                 

                If this is what is occuring, you will need to look at the code to make it thread safe.  A quick fix though would be to use the expressionbuilder from the expression returned from getConditions rather than use exprBuild within getFNDRecords.  Something like:

                exp = getConditions();

                ExpressionBuilder exprBuild = exp.getBuilder();//this is either named getBuilder or getExpressionBuilder, I'm not sure which.

                exp = exp.and(exprBuild.get("msisdn").equal(msisdn));

                ..

                • 5. Re: Cartesian Query
                  user12047959

                  Hi,

                  thank you very much.

                  I tested it was a multithreading problem.

                  My aproach has been to move the code of getConditions() inside the business method getFNDRecords: maybe not elegant but fast and safe.

                  Thanks.

                   

                  L.D.