|
Folks, We are using SQL Anywhere as an embedded database server using the capi to communicate with the dbeng12 process. For our solution we treat database files as individual data stores for each users' projects. As in, each user's project gets its own database file. Obviously, as the size of a user's project grows, so does the database file. In addition, some operations increase the file size to do some work, but then don't consume all of the extra space added... What I want to know is if there is an appropriate way/time to get the database engine to take out the excess space in the database file to leave the user with a file that is as small as possible. Is it, for example, something that would be appropriate to do when the user is closing their project after making changes? What downsides would there be to doing something like that? Could it be slow for large databases for example? Cheers, Dan |
|
Database files will only grow. If you are not encrypting the database file, you can compress it after usage (zip), using this approach you would gain much more space than just removing the empty pages. Thanks for the advice guys, I'll take that back to the developers and see what we can come up with. The idea of compressing (using zip or something like it) when the user closes the project might well work. Cheers, Dan
(04 Apr '13, 18:30)
Dan Cleyne
|
|
Free space is good, because when new data is inserted the server doesn't have to grow the database file... it just uses the empty pages it already has. Generally, unless the amount of unused space is so extreme that it is causing problems (drives running out of space, backups taking ten times longer, etc), shrinking databases just for the sake of reducing the file size is a waste of time... especially if the makes administration of an embedded application more complicated. If you want to unload and reload to reorganize the data, that's another discussion... but you might want to ALTER DBSPACE ADD after that to grow the file again to accommodate future growth. |
|
Alternatively, consider compressing your fastest growing tables to begin with. I have no idea what the performance penalty would be, but seeing as each user has his 'own' db file, you should be fine. If memory serves me right, compressible tables came out in ASA10 |
