Behind the Curtain: Database Whoa’s

Aside from the newly released Custom Response Buttons, we did a bit of work behind the scenes relocating our main database server to a beefier piece of hardware in our Texas datacenter.
This resulted in around 0.1s of increased lag when loading up a page through our website or mobile interfaces, barely noticeable in most cases.

Our support team however started noticing huge slowdowns in their ability to browse through our websites, along with a couple of administrators for large counties with multiple agencies.
It turns out one of our authentication methods is using multiple queries for each agency a user is a part of, which doesn’t pose much of a problem when turnaround to the database is 10ms, but quickly adds up when queries start taking 100ms or so.

I’ll be working on optimizing this, either by removing the extra queries and putting them into the workflow somewhere else, or by combining them into an already existing query.

The advantage of putting the queries elsewhere in the workflow are that they will no longer affect every page request, and will only be called when needed.
The disadvantage of course is the proliferation of operations in our interface, which is rapidly growing as we add more nuances and functionality. We have talked about a web interface revamp in the near future, so this may be the correct solution until we can get to that project.

The advantage of putting the queries into one big one is that it will keep the logic in the same place, thus keeping our workflows streamlined and simple.
The disadvantage is that the query may become so big an cumbersome that it is too complex to work efficiently, as well as maintain in the future.

Either way, we are due for a reworking of the website in the near future as neither is a great solution, and will require some restructuring in order to remain scale-able for the future.

Leave a Reply

Your email address will not be published. Required fields are marked *