12 Replies Latest reply on Mar 14, 2005 11:13 AM by radl01

Generated SQL Sentence too big

pablojavierpy Feb 24, 2005 7:15 AM

I am using:

jboss 3.2.7
java 5 (sun's jsdk-1.5.01)
Sybase ASE Express 12.5 (for Linux)
Fedora Core 2 (RedHat)

I have to E-EJBs (Order and OrderDetails). I have no problem with relations, but, when I created an order with many details (over 1000 details) and tried to retrieve it, I am having a problem with generated SQL.

Generated SQL looks like this:

2005-02-24 08:58:14,536 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCLoadEntityCommand.OrderLineItemEJB] Executing SQL: SELECT or
derLineItemId, number FROM orders_details WHERE (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLin
eItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?)
OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLi
neItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?)
 OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderL
ineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?
) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (order
LineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR (orderLineItemId=?) OR

And it goes on, and on, and on.

It is too big for SYBASE to process it.

The exception I am getting is:

2005-02-24 08:58:21,920 ERROR [org.jboss.ejb.plugins.LogInterceptor] TransactionRolledbackLocalException in method: public abstract py.com.roshka.wops4.document.orderLineItem.data.OrderLineItemEJBData py.com.roshka.wops4.document.orderLineItem.interfac
es.OrderLineItemEJBLocal.getData(), causedBy:
com.sybase.jdbc3.jdbc.SybSQLException: Message empty.

Any ideas? Can I change the way that SQL is being generated? I think it is kind of inneficient. It might be better a simple join on Order's primary key.

I read about lazy-load, eager-load, but I don't know if that would help me.

Thanks a lot in advance!

1. Re: Generated SQL Sentence too big

sviluppatorefico Feb 24, 2005 4:01 PM (in response to pablojavierpy)

may you give me the ejb-jar.xml and jbosscmp-jdbc.xml? i seem that ,seeing the query, read-ahead is not useful to optimize because the query is related only to the primary key......optimization with read-ahead you can use it when you are aware of the groups of fields that you want to load.
Actions
2. Re: Generated SQL Sentence too big

pablojavierpy Feb 25, 2005 5:25 AM (in response to pablojavierpy)

I don't think it has anything to do with ejb-jar and/or jbosscmp-jdbc.xml since it is wornking just fine for Order EJBs with few OrderDetails EJBs.

Anyway, I am still researching and wondering if it is a BUG in JBOSS CMP engine. Anyway, I have found that since Jboss 3.2.4 there is a new feature called "left join read ahead". It seems to be what I am needing?

Any opinions?
Actions
3. Re: Generated SQL Sentence too big

triathlon98 Feb 25, 2005 6:15 AM (in response to pablojavierpy)

You can configure the ùaximum number of records which are fetched in one go using fetch-size (probably in the default section of your jbosscmp-jdbc.xml file.

Joachim
Actions
4. Re: Generated SQL Sentence too big

pablojavierpy Feb 25, 2005 8:27 AM (in response to pablojavierpy)

Joachim,

Thanks for the response.

Do you think "Maximum number of records fetched" would affect the way SQL sentence is generated? I think it would only change some parameters in JDBC's PreparedStatement, and, in that way, I would be still receiving SQL error from SYBASE's driver.

Anyway, I will give it a try.

Thanks a lot.

Any other ideas?
Actions
5. Re: Generated SQL Sentence too big

pablojavierpy Feb 27, 2005 11:16 AM (in response to pablojavierpy)

I would like to try if "left join read ahead" is a possible solution. Any ideas of how to try generating "left-join read-ahead" tags with XDOCLET?
Actions
6. Re: Generated SQL Sentence too big

radl01 Feb 28, 2005 4:58 AM (in response to pablojavierpy)
try to look at this conf in standardjbosscmp

<read-ahead> <strategy>on-find</strategy> <page-size>1000</page-size>  <eager-load-group>*</eager-load-group> </read-ahead>

Jan
Actions
7. Re: Generated SQL Sentence too big

pablojavierpy Feb 28, 2005 9:57 AM (in response to pablojavierpy)

I will give it a try. But I still think it is a BUG. Because it doesn't have anything to do with "page size" or records fetched. I has to do with an error in JCONNECT when executing the SQL sentence.

Cheers
Actions
8. Re: Generated SQL Sentence too big

pablojavierpy Mar 1, 2005 6:43 AM (in response to pablojavierpy)

Page Size didn't help. Any other ideas? Is there someone at JBOSS advocated to SYBASE testing so I can ask him/her directly?

Thanks in advance.
Actions
9. Re: Generated SQL Sentence too big

triathlon98 Mar 1, 2005 8:31 AM (in response to pablojavierpy)

You should have used "fetch-size", not "page-size".

It does work.

Joachim
Actions
10. Re: Generated SQL Sentence too big

pablojavierpy Mar 3, 2005 5:25 AM (in response to pablojavierpy)

Is there someone at JBOSS advocated to SYBASE testing so I can ask him/her directly?

Thanks in advance.
Actions
11. Re: Generated SQL Sentence too big

pablojavierpy Mar 10, 2005 6:31 AM (in response to pablojavierpy)

It definitely is a problem related to Sybase's limits on some query expressions. It happens even if I run it directly through "isql". Same error: "Message Empty". It is kind of sad, because I think error should be a little more expressive in order to help developer.

Anyway, is there a way I can change the way SQL Sentence is generated. Should "left-join read-ahead" help? If so, is there XDoclet support for it yet?

Who is in charge of JBOSS's Sybase implementation on the project?

Thanks in advance.
Actions
12. Re: Generated SQL Sentence too big

radl01 Mar 14, 2005 11:13 AM (in response to pablojavierpy)

Hi Pablo,

we use ASE 12.5.x with CMPs and CMRs in JBoss. If you have some question here is my email radl@fss.cz.

Is your SQL statement generated for CMR ? I mean full of OR sentences ?

if yes try to reduce page-size parameter

Jan
Actions

Go to original post