cleanup slow database to speedup

Scriptcase 9 General
#1

Hi,

I want to share something with the forum.

Lately my SC was slow. When i wanted to change or edit an application and i had to switch between the various section, it felt that needed some seconds to switch to this section.

Since the data of all apps are stored in a sqlite database in a directory on your server and we all know that a database needs some maintenance a while, I tried to speed this up by compacting the database. The database can be quite big, in my case 622MB. And it WORKED. Since then SC reacts much faster when switching between the section. Also the volume of the database dropped with 20% to 500MB.

I did this: [LIST=1]

  • CLOSE SC9!!!
  • MAKE BACKUP of ../scriptcase9/devel/conf/scriptcase/nm_scriptcase.db in case something goes wrong
  • use a program to access the sqlite database nm_scriptcase.db. I downloaded and installed https://sqlitebrowser.org but there may be others and better.
  • open nm_scriptcase.db in the program
  • choose menu TOOLS/COMPACT DATABASE and wait
  • after compacting restart SC9 [/LIST] I use SC online so I first downloaded the original nm_scriptcase.db and uploaded it after compacting-process.

    Hope this is useful!

    • #2

    Thanks. I have the same problem and I will try you solution.

    #3

    Honestly, I can’t figure out why it should work. Sqlite is quite a fast database because of it’s design and the size of the database has no real influence on the performance. I have over 3.5 million records in it and it performs very fast. The occasional slow performance has mostly to do with conversions between versions and the amount of fields in the repository. Scriptcase needs to open tables and metadata often and that makes things slow.
    On huge databases it is advised - if you use sqlite - to make decent use of transactions. That really matters!