On Dec 30, 10:31 am, Peter W <[email protected]> wrote: > Some questions: > -Any way to reduce the table and index size? 90% overhead = lots of I/ > O
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. If you have done a lot of updates/deletes you can do a script to like this: script to '/temp/backup.sql'; Backup the original database, then deleted it. Connect to the same database connection (it will created a new empty database). Then do something like this: runscript from '/temp/backup.sql'; That will rebuild the entire database so there shouldn't be a lot of empty space in it. > -Does table fragmentation affect query speed? Can tables be > defragmented? Yes it affects the performance. You can't defrag a database but you can rebuild it using the above method. > -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. You might want to try to analyze the database. That helps sometimes. You might also want to try to change the order of the primary key so that created is first, and item_id is second. 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. Brish --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
