We are a small business that created appointment scheduling software. It needs to be updated.
1. When I check my database that support my system, I found one of the major data table holds over 3.5 million records . I took a simple test to run a database query to this table and it takes about 12 seconds to get back the result. This data table is the source of all your reports/forms -- so you can imagine in daily use when everybody want to open a form all the queries are queued, it took even more time to response.
2. Some figures of my current system:
it has 53 custom defined form/report (template)
it defines 927 form fields
it now holds 92687 saved forms/report in DB
it now has 23596 cases (user case)
We need to Backup and remove some old forms from the database -- purely to reduce that single large table (bottle-neck) -- if possible, we can develop some new module that reads the old forms
Suggestions are welcome.