Chances are, if you’ve ever been to a college football game, you’ve heard the drum-line. The loud crashes of cymbals, tight tap of the snares, deep tones of the bass drums, you get the idea. In order to get these specific sounds, different tools are used. Quads use special mallets, as does the pit according to whatever instrument is being played. You wouldn’t use brushes on a snare drum just as you wouldn’t use bass beaters on a xylophone.

Writing Microsoft SQL queries is the same way. Just as specified mallets and sticks are used in drum-line, different SQL techniques are used depending on the situation.

---

select refname, lskinid, round(sum(leminutes), 2) as 'Minutes' from lskin
	join hproduct_lskin on frn_lskinid = lskinid
	join dnis on add_lskinid = frn_lskinid
	join archive.dbo.xcall_2014 on cf_frn_dnisid = dnisid
		and tz_date between '2014-01-01' and '2014-03-01'
where frn_hproductid = 1
group by refname, lskinid

Above is just a basic query; nothing special, just some joins. As you can see, it took 1 minute and 58 seconds to complete.

with CTE (refname, lskinid, dnisid)
as (
select refname, lskinid, dnisid from lskin
	join hproduct_lskin on frn_lskinid = lskinid
	join dnis on add_lskinid = frn_lskinid
where frn_hproductid = 1
)
select refname, lskinid, round(sum(leminutes), 2) as 'Minutes' 
from CTE c
	join archive.dbo.xcall_2014 on cf_frn_dnisid = c.dnisid
		and tz_date between '2014-01-01' and '2014-03-01'
group by refname, lskinid

This takes the same query and separates it into 2 parts using a CTE. Changing the technique cut the time down to only 8 seconds.

declare @TempTable table (refname varchar(155), lskinid int, dnisid int)

insert into @TempTable (refname,lskinid, dnisid)
(
select refname, lskinid, dnisid from lskin
	join hproduct_lskin on frn_lskinid = lskinid
	join dnis on add_lskinid = frn_lskinid
where frn_hproductid = 1
)
select refname, lskinid, round(sum(leminutes), 2) as 'Minutes' 
from @TempTable t
	join archive.dbo.xcall_2014 on cf_frn_dnisid = t.dnisid
		and tz_date between '2014-01-01' and '2014-03-01'
group by refname, lskinid

Again, just a different technique (using a Temp Table) cut the query down to 8 seconds.

---

Those are just a few examples of query techniques in SQL. So next time you have to write a query, make sure you’re using the right ‘instrument’ for the occasion.

What makes great software great? Does it have to look good? Does it have to have a great user interface? While those things are good and will make your application pleasant, those things alone won’t make software great. Software must work, it must work without errors, and it must work every time you try and use it. Some would say that the most important aspect of software is its reliability. If an application doesn’t work, pretty visuals and user interfaces are all for nothing.

So how do we make software reliable? Reliability goes hand-in-hand with functionality, so before you even start, you must decide what the software will do and have a basic plan of how it will accomplish its task. You might even create a barebones application as a proof-of-concept to ensure your methods will work and be a solid foundation to work off of. This initial prototype will actually help in squashing initial bugs and issues that could show up later in the process.

Once you know how you want to proceed and know that it will work, you begin building your application. First and foremost, you want it to function (obviously), but once you get it to function, it must be reliable. Your customers will begin to rely on this software, so it has to work fast and it has to work every time they access it. Working every time is more important than raw speed, so we’ll start there.

Software that doesn’t work is frustrating, but what’s more frustrating is software that works most of the time, but fails you when you most need it. To ensure your application will work each and every time you need it to, you must test it, test it, and test it. Test it until it works without fail, then test it again. Play the malicious user and try to break it. You wrote the code, you know where the vulnerabilities are; exploit those vulnerabilities and learn how to plug those holes. If your app uses forms for user input, validate those inputs, first on the user interface, and again within processing.

Make sure that nothing you can do will send the application into an unusable state. Even when you are sure that there is no way that your code can be broken, add in error handling. In the case that somehow you didn’t consider all possible scenarios that could lead to an error, you must have a way to gracefully handle these rare exceptions. Handling errors can be as simple as a generic message asking the user to please try again, to the most sophisticated algorithms that predict what the user was trying to do.

Once the application works without failure, you must ensure that it runs efficiently. If it feels slow and sluggish, this will be a source of frustration for your users.  With modern programming languages, efficiency of your code base isn’t as much of a concern as it once was, but you should still strive to be as efficient as possible. Simple is usually better. You shouldn’t create some off-the-wall, complicated function to do a task that can be quickly and easily accomplished by a method built in to your language or provided in a library. In most instances, it’s a waste of time. Also, anyone who comes behind you and tries to update your code or do maintenance will need to take time to figure out what the application is doing there.

If you are pulling data from a database, then the efficiency of your database is very important in the speed of your application. Not only do the tables need the appropriate indexes and keys, but the way in which you query the information must also be efficient. Simple and quick queries are best. Get in, get the information you need, and get out so that your application doesn’t tie up valuable resources on you database server. Any complicated calculations should be handled in the application layer, not the database. You should do simple aggregates (count, sum, etc.) within the database, then hand those off to the application to do the bulk of the data processing.

Just because these steps are taken, it doesn’t mean you are finished. Software can always be improved, and as developers we should strive to always be improving.  These also aren’t the only steps to take in making your software rock-solid, there are countless other aspects in which you can improve your application. These two, speed and reliability, are great building blocks to start with, though.

Before I go over the engine, I’m going to explain the other parts so that you have an idea of what our engine powers. So when you look at a car the first thing that you see is the paint, rims and accents of the car. This is the wonderful product of our design team. They ensure that our users have the Ultimate user experience by designing things with the user in mind. Next is the inside of the car, all the bells and whistles! This consist of our queries and ColdFusion coding that are not really outward facing. This part is the product of a variety of teams. All of these ensure the reports, processes and data gathering are done properly. Humanatic would be the person driving the car. You don’t get from point A to point B without driving. All of our call reviews go through a system maintained by a small amount of people. The maintenance of the car is important here as well. Here our Cases team goes through anything that is going wrong and meticulously hunts it down and fixes the bells and whistles. This is the heart of the Hacker Farm. I say this because it is not the most glamorous job, but it allows people to focus on other issues and not to be pulled in a thousand directions. Now that you have an idea of the process, to the Engine. This is the part of the car that no one ever sees. Most people never see the engine or know all it does but expect it to work none the less. One last important team is the Projects team, which is in essence is our GPS. You, know Google maps because they keep us on the right path. They tell us the projects to do and keep them in flow
The engine of all of our reports, queries, processes and even our data gathering all comes from our hardware. This gets broken down into our servers, dialtag boxes, email systems, databases, system updates to name a few of the engine components. The integrity of the databases are an extremely important task that is done. Since we don’t have a DBA it comes down to the team leads and the Systems team to ensure that they are running smoothly. Our servers are monitored highly. It would be like have a thousand sensors in your engine telling you exactly what is going wrong. We get emails and have systems set up that tell us the load of the server, if and when it went down, the loads that it has had over a period of time. Even our email systems are monitored for anything that could pop up. High email flow, services that crash and block reports being sent out. Our server have hard drive failures, system updates and configurations that all have to be taken care of. The way a lot of systems are set up are that they have redundant drives to hold the system up until we can replace the drive.

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.