OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

SOLVED - Join command out of memory error
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dr_nuke
General User
General User


Joined: 08 Feb 2005
Posts: 17

PostPosted: Thu Mar 18, 2010 7:02 am    Post subject: SOLVED - Join command out of memory error Reply with quote

Hi!

I have two tables with similar information that I want to compare. Normally I would create and populate each table and then do an outerjoin so I can see what data is similar and what is different in the two tables.

Each table has 3 fields. I am joining on Field 1 in both tables and want to show the other 2 fields from each table beside the joined info.

Table 1 has 6214 records x 3 fields. Table 2 has 15,159 records x 3 fields.

I get an "out of memory" error when I try to view a cross join (outer) of table1.field1 to table2.field2. For the most part, the info in table 1 should appear in table 2. I expect only a few hundred exceptions. So probably less than 15,500 rows total.

I've tried a left outer join to just test but this also get an out of memory error. I've run this type of project often in other db products. It isn't very complicated and shouldn't need too much memory.

Why doesn't this work using Base? (I though HSQLDB was pretty robust?) How do I design the query to work with Base?

I understand that I can increase the memory in the preferences but how do I determine what memory size should be used and make sure it is available for this query?

Thanks in advance.


Last edited by dr_nuke on Sun Mar 21, 2010 11:42 am; edited 1 time in total
Back to top
View user's profile Send private message
r4zoli
Super User
Super User


Joined: 17 May 2005
Posts: 570
Location: Budapest, Hungary

PostPosted: Thu Mar 18, 2010 12:50 pm    Post subject: Reply with quote

You reach the default Java heap size limit, increase it, under tools>Options>Java>Parameters add to Java startup parameters, depending on your memory size, if you have enough RAM: use Xmx512m, and Xms512m, add each value separately.
Use same values for each parameter.
I think the defaults are -Xms32m -Xmx128m.
Back to top
View user's profile Send private message
dr_nuke
General User
General User


Joined: 08 Feb 2005
Posts: 17

PostPosted: Thu Mar 18, 2010 3:43 pm    Post subject: Reply with quote

Thanks.

I don't understand what

Quote:
Xmx512m, and Xms512m


is supposed to mean. Could you explain?

I'm on a Mac. There is nothing in the Preferences:Java:Parameters at this time. I guess it is using some default.(??)

I have 4GB RAM.
Back to top
View user's profile Send private message
r4zoli
Super User
Super User


Joined: 17 May 2005
Posts: 570
Location: Budapest, Hungary

PostPosted: Thu Mar 18, 2010 10:55 pm    Post subject: Reply with quote

Quote:
I don't understand what

Quote:
Xmx512m, and Xms512m


is supposed to mean. Could you explain?

This is set java heap to 512MB, on linux and win.

I have no experience with Mac, no idea how to set this on it.

May be others can help on it.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Mar 19, 2010 5:36 am    Post subject: Reply with quote

On a Mac all application settings are accessible from a central place instead of Edit>Preferences or Tools>Options within the respective application. Don't know where to find the central place but it should be easy to find.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Mar 19, 2010 7:57 am    Post subject: Reply with quote

dr_nuke:

You said:

dr_nuke wrote:
I get an "out of memory" error when I try to view a cross join (outer) of table1.field1 to table2.field2. For the most part, the info in table 1 should appear in table 2. I expect only a few hundred exceptions. So probably less than 15,500 rows total.

I would like to suggest a different possibility / reason for your "out of memory" message.

That is, I suspect that your SQL in the "cross join" is NOT constructed correctly. That is, I suspect that it is returning, not less than 15,500 rows ( as you suspect ), but rather, 6214 * 15159 rows . . . that is . . . 94198026 rows . . . and therefore . . . an out of memory situation.

It would be helpful, if you could post here, the actual SQL you are running . . . to confirm . . .
dr_nuke wrote:

I am joining on Field 1 in both tables

Sliderule
Back to top
View user's profile Send private message
dr_nuke
General User
General User


Joined: 08 Feb 2005
Posts: 17

PostPosted: Fri Mar 19, 2010 10:04 am    Post subject: Reply with quote

Thanks for your help.

Here is the sql for the query.
Code:
SELECT "update_sid"."sid_no" AS "sidnew", "update_sid"."rev_no" AS "revnew", "cos_sids"."sid_no" AS "sidold", "cos_sids"."rev_no" AS "revold", "cos_sids"."trigger" AS "trigold" FROM "cos_sids" AS "cos_sids" CROSS JOIN "update_sid" AS "update_sid" ORDER BY "sidold" ASC


The join is on the update_sid from each file.

I can't find any description of the supported SQL commands in the help. Is there such a document somewhere to be found? I would like to put the query result into a new table. In the past I would use something like "INTO TABLENAME".

Thanks again.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Mar 19, 2010 11:22 am    Post subject: Reply with quote

dr_nuke:

You asked:

dr_nuke wrote:
I can't find any description of the supported SQL commands in the help. Is there such a document somewhere to be found? I would like to put the query result into a new table. In the past I would use something like "INTO TABLENAME".

For documentation on the the OpenOffice Base 'embedded' database, see the link below:

http://www.hsqldb.org/doc/guide/ch09.html

I took your SQL, and, just arranged it for easier 'reading' . . . you entered:
Code:
SELECT
   "update_sid"."sid_no" AS "sidnew",
   "update_sid"."rev_no" AS "revnew",
   "cos_sids"."sid_no" AS "sidold",
   "cos_sids"."rev_no" AS "revold",
   "cos_sids"."trigger" AS "trigold"

FROM "cos_sids" AS "cos_sids" CROSS JOIN "update_sid" AS "update_sid" 

ORDER BY "sidold" ASC

and, the simple answer is, YES . . . your result set ( and therefore the 'out of memory' message ) is because you created your SQL improperly. That is, you did NOT 'relate' the two tables, ergo . . . assuming your tables contained 6214 and 15159 records, it would result in 94198026 records . . . NOT WHAT YOU WANTED. Smile

While I am NOT 100% sure of what you want . . . BUT . . . I suspect you want something like this:

Code:
SELECT
   "update_sid"."sid_no" AS "sidnew",
   "update_sid"."rev_no" AS "revnew",
   "cos_sids"."sid_no" AS "sidold",
   "cos_sids"."rev_no" AS "revold",
   "cos_sids"."trigger" AS "trigold"

FROM "cos_sids" AS "cos_sids" LEFT JOIN "update_sid" AS "update_sid" ON "cos_sids"."sid_no" = "update_sid"."sid_no"

ORDER BY "cos_sids"."sid_no" ASC


Now, if the ABOVE ( or any other SQL you end up with ) and you really want to 'CREATE' a new table ( rather than just a VIEW ) . . . it would be something like:

Code:
SELECT
   "update_sid"."sid_no" AS "sidnew",
   "update_sid"."rev_no" AS "revnew",
   "cos_sids"."sid_no" AS "sidold",
   "cos_sids"."rev_no" AS "revold",
   "cos_sids"."trigger" AS "trigold"

INTO CACHED "MyNewTableName" FROM "cos_sids" AS "cos_sids" LEFT JOIN "update_sid" AS "update_sid" ON "cos_sids"."sid_no" = "update_sid"."sid_no"

ORDER BY "cos_sids"."sid_no" ASC

Please see the link below for a recent discussion on this subject.

http://www.oooforum.org/forum/viewtopic.phtml?t=96950

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
dr_nuke
General User
General User


Joined: 08 Feb 2005
Posts: 17

PostPosted: Fri Mar 19, 2010 12:47 pm    Post subject: Reply with quote

Thanks for the help and link, Sliderule.

I am trying to do an outer join.

I don't know if all the records in table update_sid are also in cos_sid. So I need to do the outer join. My gut says that there are about 100 records in cos_sid (6500+ records) that don't appear in update_sid (15600+ records). Of course there are many in update_sid that don't appear in cos_sid.

My understanding is that CROSS JOIN is the HSQLDB equivalent of outer join??? Or should I be using a LEFT OUTER/RIGHT OUTER (if these are allowed?) as opposed to LEFT JOIN?

I am attempting to join update_sid.sid_no to cos_sid.sid_no.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Mar 19, 2010 1:08 pm    Post subject: Reply with quote

dr_nuke:

You asked:

dr_nuke wrote:
Or should I be using a LEFT OUTER/RIGHT OUTER (if these are allowed?) as opposed to LEFT JOIN?

If it were me, I would try . . . LEFT OUTER JOIN first, and see what happens. That is, since I do NOT know which table contains the most . . . what you probably want is LEFT OUTER JOIN ( if not, RIGHT OUTER JOIN ) depending on which table contains the records. In either case, you should NOT be out of memory, IF you 'relate' the query on the common field between the tables "sid_no" .

You might find the following link helpful, as a tutorial on SQL Join statements:

http://www.sql-tutorial.net/SQL-JOIN.asp

I hope this helps, please be sure to let me / us know. Also, for others reading this forum site, it would be helpful if you could provide the 'exact' SQL that you found working for you. Smile

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
dr_nuke
General User
General User


Joined: 08 Feb 2005
Posts: 17

PostPosted: Fri Mar 19, 2010 1:51 pm    Post subject: Reply with quote

Thanks for the additional links.

Unfortunately, I'm still fighting this.

The LEFT JOIN yields all the records in table cos_sid but no additional records from update_sid.

The RIGHT JOIN yields all the records in table update_sid but no additional records from cos_sid.

I can't get the CROSS JOIN to work. I cleaned up the SQL a bit. This is the new error message:
Quote:
The data content could not be loaded.
SQL Status: S0022
Error code: -28

Column not found: sid_no in statement
[SELECT
"update_sid"."sid_no" AS "sidnew",
"update_sid"."rev_no" AS "revnew",
"cos_sids"."sid_no" AS "sidold",
"cos_sids"."rev_no" AS "revold",
"cos_sids"."trigger" AS "trigold"

FROM "cos_sids" CROSS JOIN "update_sid"

ON "cos_sids"."sid_no" = "update_sid"."sid_no"

ORDER BY "sidold" ASC]


I tried CROSS OUTER JOIN and just CROSS, and got a syntax error. I don't understand why it can't find sid_no in the statement.

I'm stumped as to why this isn't working.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Mar 19, 2010 2:08 pm    Post subject: Reply with quote

dr_nuke:

You said:

dr_nuke wrote:
Column not found: sid_no in statement

That is probably because you have misspelled it. Very important point, the names must be entered EXACTLY as you have defined them in your database. This includes CASE . . . UPPER / Mixed / lower.

For example, "update_sid"."sid_no" is NOT the same as "Update_Sid"."Sid_No", NOR is it the same as "UPDATE_SID"."SID_NO"

Second, your ORDER BY should be the actual name from your database, I would assume "cos_sids"."sid_no" ( see my example above where I did modify your ORDER BY clause.

Lastly, I think you really should be using a LEFT OUTER JOIN, but . . . since I do NOT have your database, I cannot say for sure.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
dr_nuke
General User
General User


Joined: 08 Feb 2005
Posts: 17

PostPosted: Fri Mar 19, 2010 2:26 pm    Post subject: Reply with quote

I checked each table again and the query.

I'm having a hard time believing this is a typo/spelling error.

I use the exact same query for the LEFT JOIN and RIGHT JOIN and both run OK without any error.

All I did was substitute the CROSS JOIN into the same query and then I get the error.

Error also has this: (SQL Status: S0022
Error code: - 28 ) I can't find a list of error codes.

Any other reason for the error message?
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Mar 19, 2010 2:50 pm    Post subject: Reply with quote

Per HSQL documentation found at:

http://www.hsqldb.org/doc/guide/ch09.html#select-section

HSQL documentation: http://www.hsqldb.org/doc/guide/ch09.html#select-section wrote:

If CROSS JOIN is specified no ON expression is allowed for the join.


Therefore, that is the reason for the error message. BUT, you DO need the 'relationship' between the two tables, ergo, the way to go is with a LEFT OUTER JOIN.

Now, to make sure 'things' are right with the names, etc . . .

If you run the following Query, how many records do you get?

Code:
SELECT
   "update_sid"."sid_no" AS "sidnew",
   "update_sid"."rev_no" AS "revnew",
   "cos_sids"."sid_no" AS "sidold",
   "cos_sids"."rev_no" AS "revold",
   "cos_sids"."trigger" AS "trigold"

FROM "cos_sids" AS "cos_sids",
     "update_sid" AS "update_sid"

WHERE "cos_sids"."sid_no" = "update_sid"."sid_no"

ORDER BY "cos_sids"."sid_no" ASC


Sliderule
Back to top
View user's profile Send private message
dr_nuke
General User
General User


Joined: 08 Feb 2005
Posts: 17

PostPosted: Sat Mar 20, 2010 8:10 am    Post subject: Reply with quote

Hi Sliderule.

Thanks for your continued help. I ran the query:

Code:
SELECT
   "update_sid"."sid_no" AS "sidnew",
   "update_sid"."rev_no" AS "revnew",
   "cos_sids"."sid_no" AS "sidold",
   "cos_sids"."rev_no" AS "revold",
   "cos_sids"."trigger" AS "trigold"

FROM "cos_sids" AS "cos_sids",
     "update_sid" AS "update_sid"

WHERE "cos_sids"."sid_no" = "update_sid"."sid_no"

ORDER BY "cos_sids"."sid_no" ASC


And I get 2846 records. This makes sense since by default if you don't use a specific join, HSQLDB assumes an inner join. Ch9 of the above link.

So I also tried the CROSS JOIN without a ON clause as per your note above. It doesn't work. I get the following error.

Quote:
The data content could not be loaded.

SQL Status: 37000
Error code: -11

Unexpected token ORDER, requires ON in statement [SELECT
"update_sid"."sid_no" AS "sidnew",
"update_sid"."rev_no" AS "revnew",
"cos_sids"."sid_no" AS "sidold",
"cos_sids"."rev_no" AS "revold",
"cos_sids"."trigger" AS "trigold"

FROM "update_sid" CROSS JOIN "cos_sids"

ORDER BY "cos_sids"."sid_no" ASC]

The SQL command leading to this error is:

SELECT
"update_sid"."sid_no" AS "sidnew",
"update_sid"."rev_no" AS "revnew",
"cos_sids"."sid_no" AS "sidold",
"cos_sids"."rev_no" AS "revold",
"cos_sids"."trigger" AS "trigold"

FROM "update_sid" CROSS JOIN "cos_sids"

ORDER BY "cos_sids"."sid_no" ASC


I thank you for your continued effort. I need to use a full OUTER JOIN. From the results set the following is concluded.

Table update_sid has 15,159 records. (I over estimated before)
Table cos_sid has 6,214 records

An INNER JOIN shows that there are 2,846 records where sid_no is the same in both tables. A LEFT OUTER JOIN shows 15,159 records and a RIGHT OUTER JOIN shows 6,214 records.

I would expect that the CROSS JOIN (OUTER JOIN) would show a total of 15,681 records [(15,159 - 2,846) + (6,214-2,846)].

Neither of the JOIN queries that we have tried yields the required number of records. Any idea why? Is this a bug?

I think that I might be able to get to the correct result by doing an INNER JOIN of a LEFT OUTER JOIN and a RIGHT OUTER JOIN.(??) A bit complicated.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group