Client is an Australia based company and provides web based email marketing solution to their clients. Their web-based email marketing software integrates functions for email campaigns, SMS, online events management, online surveys and email newsletters. They have huge database for their marketing software. Database contains lots of data related to marketing campaign like newsletters for email and web marketing, newsletter subscribed users, newsletter´s readers behavior, event registrant behavior, SMS reader behavior tracking etc. It allows to generate various kind predefined set of reports to clients. They want to provide an interface for their clients to generate custom reports. They had built such interface but due to non structured and non-optimized query, query execution took longer to execute and cause crash of database so they approach to Radix for optimized and performance oriented query building.
- Allow users to generate reports based on custom query based on People, Newsletter, Events, SMSs behavior.
- Optimized database query for better system performance and fast result without crashing or locking of the database while generating results.
- As database query operation take longer time to get execute, Database query operation do not put users on hold/wait till results get generated. Allow users to perform other task till results get generated.
- Complete lack of documentation
- Database architecture diagram not in place
- Lack of awareness about the developed modules, within the Clients team as the application was built over a long period of time and team members have changed
- Short timeframe allotted for the optimization
- Demanded all round revamp
- Limited budget
- No structured code and lack of uniformity as the development had been handled by many members
Radix understands their requirement and existing database. Radix suggested some performance related tips to be implemented into existing database structure.
In this solution, first we had find out performance related issues into existing database. Based on study of their existing database radix suggested use of numeric key as primary identification key instead of the randomly generated alpha-numeric key. Also radix suggests to apply indexing at required fields into existing tables wherever required.
Radix has studied existing query building process and found that queries are not written in optimized manner. In existing application, query was written using multiple joins for similar kind of rules. Radix had redeveloped query building application to generate more optimized queries by groping similar kind of rules and use of mysql´s aggregate functions. Hence, we had decrease execution speed of the query which delivers results faster and void the problem of the table locking & crashing.
Even though, prepared database query was much optimized but due to large size of database and complex combination of selection criteria, query execution took time to generate reports. This leads users to put on hold/wait for longer to generate results and it also stops the user from performing other task. Radix had found solution to queue queries for execution and in background queries gets execute and generate results. Radix has used process forking technologies for continues watch on query queue. Process forking is work same as scheduler with the difference that it continues execution of process in background.
- Optimized database queries
- Increase execution speed of queries.
- More structured and organized queries.
- User can navigate to other pages while results get generating.