[Mondrian] SqlTupleReader.readTuples generates huge IN list causing java.sql.SQLException

Peter Tran ptran at prospricing.com
Thu Jun 21 15:27:23 EDT 2007


Hi Julian,

That didn't seem to work.  There's an issue with this implementation
which has an existing tracker.

http://sourceforge.net/tracker/index.php?func=detail&aid=1630754&group_i
d=35302&atid=414613

I still owe you a test case.  :)

However, even with our fix it still doesn't work.  I think this logic is
using a different code path.

We'll investigate.

The question is why Mondrian has to do these two fetches?  Can't it be
combined into one SQL?

1)  SqlMemberSource.getMemberChildren to get all Products at
BULK_DYNAMIC level.

    select "PA_PRODUCT"."BULK_DYNAMIC" as "c0" 
    from "PA_PRODUCT" "PA_PRODUCT" 
    group by "PA_PRODUCT"."BULK_DYNAMIC" 
    order by "PA_PRODUCT"."BULK_DYNAMIC" ASC

2)  SqlTupleReader.readTuples to get all Products at PPC using a massive
IN list
    From step #1 above.

    select "PA_PRODUCT"."BULK_DYNAMIC" as "c0", 
           "PA_PRODUCT"."PPC" as "c1" 
    from "PA_PRODUCT" "PA_PRODUCT" 
    where ("PA_PRODUCT"."BULK_DYNAMIC" in ( .. Results from #1 ..)

The above logic could be done as one fetch from the database and
performs much faster.

1)  select "PA_PRODUCT"."BULK_DYNAMIC" as "c0", 
           "PA_PRODUCT"."PPC" as "c1" 
    from "PA_PRODUCT" "PA_PRODUCT" 
    group by "PA_PRODUCT"."BULK_DYNAMIC" , "PA_PRODUCT"."PPC"
    order by "PA_PRODUCT"."BULK_DYNAMIC" ASC, "PA_PRODUCT"."PPC" ASC

Thanks,
-Peter


-----Original Message-----
From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org]
On Behalf Of Julian Hyde
Sent: Wednesday, June 20, 2007 6:46 PM
To: 'Mondrian developer mailing list'
Subject: RE: [Mondrian] SqlTupleReader.readTuples generates huge IN
listcausing java.sql.SQLException

> Step 2 will fail, because it creates a SQL statement that is 8,377,847
> characters long!
> 
> The JDBC driver fails with "java.sql.SQLException: No more 
> data to read
> from socket"

Judging by that error message, the query crashed Oracle. Well done! :)

We have a property which is supposed to control this. Can you try
setting the property mondrian.rolap.maxConstraints=100 and see if the
query behaves differently?

What version of mondrian/oracle are you using?

Julian

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian


The information contained in this email may be confidential and/or legally privileged. It has been sent for the sole use of the intended recipient(s). If the reader of this message is not an intended recipient, you are hereby notified that any unauthorized review, use, disclosure, dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please contact the sender by reply email and destroy all copies of the original message. Thank you





More information about the Mondrian mailing list