Marketing Metrix
Marketing Metrix provides services for matching end customer profiles to marketing lists for businesses. For example, based on a list of end clients (usually name plus address) they can generate profiles of these clients preferences, spending habbits, political leanings and other such details.
Using custom databases, matching software and statistical analysis packages they can build profiles of the end customers to help organizations in making business decisions based on these aggregated profiles (income levels, entertainment preferences, vacation habits, car ownership, etc.).
The challenge was that the databases, more then 50Gb in size, took between many days and multiple weeks to search, match and build datasets that could be used by the statistical packages. Some level of optimization, both database and process, was required to improve business turnaround times for their clients.
Solution
The key factor to improving the database performance was not just hardware related. Doubling the hardware performance would not yield the performance required. A reworking of the overall processing of the data was required. The following items were addressed as part of the solution that reduced the overall processing times from the days-weeks to several hours.
- MSSQL server's databases were moved to separate and fast partitions for performance
- System optimizations were made to minimize the number of writes to the disks as the solution was I/O bound
- Using SQL a less precise matched subset of match candidates was created - quicker to generate but would also include false positives
- Processing only the subset instead of the global dataset for precise matching resulted in an overall process that was an order of magnitude faster
- Generalization of the process and instruction of the staff
- Development of SQL scripts to perform specialized matching such as distance to the coast, population density, etc.
Tags: PHP, MSSQL, MSSQL Server, SQL, workflow