• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

I want to decrease size of my DB

 
Ranch Hand
Posts: 49
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I stored images as BLOB on my SQLite database , and then I wanted to decrease size of my database . I removed some images which are above 200kb but my db size is the same . How can I decrease size of my DB ? And why is size the same ? Does use  Db default size for images if I use Blob type ? Does it depend on size of images? Thanks in advance.
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you looked at SQLite's VACUUM command?
 
Miri Yusifli
Ranch Hand
Posts: 49
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Brian Tkatch wrote:Have you looked at SQLite's VACUUM command?




Yes i used it
 
Brian Tkatch
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And vacuum didn't reduce the size? That sounds odd.

Perhaps you can ask on the SQLite support email list.
 
Bartender
Posts: 1868
81
Android IntelliJ IDE MySQL Database Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here are a few suggestions:
  • There are a number of Pragma statements https://sqlite.org/pragma.html which may help out.
  • This site has a few tips as well http://www.whoishostingthis.com/compare/sqlite/optimize/.
  • If I recall correctly then triggers and stored procedures can significantly add to the database size. What if you make a copy of the database and on that copy delete any and all triggers and stored procedures? Are you then getting something more to your liking?
  • The size of the database may appear to be the same due to the file system. In Windows there is a 'file size', and there is a 'file size on disk'. I suspect something like this exists for other operating systems and file systems as well.
  • One (not necessarily you) can have larger then needed database due to poor table structures/design.
  • If possible, I suggest that you try to stay away from blob type fields as not only can then drastically increase the size of the database and the database table but they can drastically increase the time for a query to return a result set.


  •  
    Saloon Keeper
    Posts: 7592
    177
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    I don't know for sure one way or the other, but I think it's possible that sqlite works differently on Android than it does on a desktop OS. Maybe the file simply doesn't shrink.
     
    Marshal
    Posts: 4518
    572
    VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Miri Yusifli wrote:

    Brian Tkatch wrote:Have you looked at SQLite's VACUUM command?



    Yes i used it


    And what did you find ... ?

    Any reduction?
    Any errors reported?
    Did you make sure you didn't have any open transaction at the time?
    Did you have enough free space available (I think vacuum creates a temporary copy of the file)?
     
    reply
      Bookmark Topic Watch Topic
    • New Topic