At Century Interactive, we know presenting data in a clear and useful way is one of the keys to helping our customers understand what it takes to own their phones.  One of our favorite ways to do this is by using detailed graphs that span anywhere from one day to several months.  The advantage of displaying extensive amounts of data is that our customers can quickly identify trends in calling activity and overall business performance.

Sharing this information makes our customers happy, but it can be a major headache for the developers who create the reports.  Five weeks of data for one company usually involves thousands of database rows that have to be combed and neatly organized.  Not only do our customers want to see the extra data, they want it to be available nearly instantaneously.  The task becomes a balance between server capabilities, database storage and overall load times.

According to surveys done by Akamai and Gomez.com, nearly half of web users expect a site to load in 2 seconds or less, and they tend to abandon a site that hasn’t loaded within 3 seconds.  Armed with this information, how do we display all our useful metrics without having users feel frustrated while looking at our beautiful loading .gif? Here are three of our easier solutions:

1.  Cut down the number of “order by” statements in the SQL queries.  Removing one “order by” statement from a SQL query returning 9,000 rows cut execution time from 3,156 milliseconds to 2,078 milliseconds.  That’s over one critical second of time we saved each one of our customers each time they visit that report.

2.  Create aggregate join tables.  By creating new tables that only reference information from other tables, we can cut down execution time to only the rows in the new table.  For example, this is helpful when we only want data from certain customers.  There’s no reason to go through our entire customer base when we only want data from dealerships.  By identifying dealership customers in a separate table, the query can skip thousands of rows on each page load.

3.  Utilize AJAX to make requests behind the scenes.  Incorporating AJAX into our reports not only cuts down on initial load times, it lets users choose what data they want to see.  The beauty of this is that the user gets more options and the amount of information we have to initially load can be cut considerably.  For example, our new CRISP score report shows a line graph with the individual “C”, “R/I”, “S” or “P” score plotted against the top 5 dealership’s over the last five weeks.  Upon page load, the only score that is shown is the “C” or “connect” score.  By adding buttons which let the user pick what score to show, we don’t have to load the other scores until the user decides to look at them.