On 31 Dec 2008, 05:00, Brish <[email protected]> wrote: > H2 doesn't shrink the data, or index files so it's possible there is > empty space in it. Did you delete and/or update a lot of records after > the original insert? If not that you probably won't be able to reduce > the size.
I did inserts only. I read another post about minimum record size of 128 bytes which I'm suspecting to be the problem here. I was thinking maybe there's a way around it (different table types or data types). > You can't defrag a database but you can rebuild it using the above > method. Thanks. I tried that and the queries run faster (but still 15 seconds or so). I'm suspecting records to be stored in a scattered way due to the compound key. That could lead to a lot of page reads (or what it's called in h2) when I read a lot of records that don't follow the order in which the data was inserted (data is inserted by time, so querying with a fixed ITEM_ID will retrieve records scattered over the table). But I don't know how h2 works so I'm just guessing here. > > -Other hints to improve execution speed (e.g. "partitioning" by > > item_id)? > > Try putting "explain" in front of your query to see what h2 is doing. It's using the primary key and its using index sorting, so it seems ok. > You might want to try to analyze the database. That helps sometimes. Didn't work this time. > You might also want to try to change the order of the primary key so > that created is first, and item_id is second. Yes, I'll definitely try that! > H2 is slow for large queries. If the query returns a lot of values > there isn't anything you can do to speed it up. Ok, didn't know that. I just looked at the performance comparison page where it seemed to be faster than most other engines. Maybe someone could add another test to that page which compares table sizes and large table queries? I suspect, in this case MySQL would be much smaller and therefore faster. In the meantime, I might try denormalizing data by storing say 20 values per record and doing part of the selecting in the code... Thanks a lot, Peter W --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
