Commerce Speed Check: How one man doubled our CRS search speed.

One of our values here at Inntopia is innovation. Whether it’s the product or ourselves, we’re always trying to improve. So when Matt Sontum discovered a way to increase search speed in the Inntopia central reservation system, he dug into the code and delivered a big performance boost for our clients and their guests.

—

Gregg: Let’s start with the basics, Matt. What’s your title here at Inntopia, translate that title into what that means you typically do during the day?
Matt: My title is Senior SQL Database Administrator and Developer. I am a database administrator for all SQL Servers at Inntopia which means that I monitor them for any issues and respond to job failures. However my primary day to day work has been SQL development. Writing new functionality, optimizing existing stored procedures for performance, enforcing coding standards and mentoring developers to write more efficient SQL.

Gregg: And how long have you been with the company?
Matt: I have been with the company a little over a year and a half.

Gregg: One of the things you work on is search, correct? Give us a quick overview of who uses this search and what they are searching for?
Matt: The most recent performance improvement to search was for something called IP Common Universal Search. This is what a guest uses to search for different pieces of their vacation – lodging, activities, etc. – in the Central Reservation System. The search accepts a fairly open ended set of parameters – Arrival Date, Departure Date, Number of Adults, Number of Children, Product Category, Product Supplier – and then returns the products that fit those parameters.

Gregg: Why is that such a unique challenge?
Matt: There are many business rules that need to be applied regarding which products to return based on the parameters supplied. For example, a product for a supplier might not appear in the search because it was restricted on the sales channel. They also vary considerably based on product type—the rules for air travel are very different from car rentals, which are very different from lodging and so forth.

Gregg: And you found a way to improve that performance, correct? What’s the layman’s version of what you did?
Matt: I was able to improve the performance of the search by performing three modifications. The first was converting a large cursor loop to set-based processing so different parts of the same process could run at the same time..

The second was eliminating an unnecessary join. A join is something that happens inside a database when you ask it to look up data from two different tables. Joins take time to accomplish, so removing one can be a big deal.

The final was eliminating a special package type check that was no longer in use. Unused elements become fairly common in software that’s been around awhile, but our focus on constant innovation means we have a structure and people in place to find and remove them.

Gregg: And what kind of performance improvements did you see?
Matt: Between those three modifications, I was able to halve the time required for searches to return and halve the CPU usage on the database server required to perform those searches.

The arrow represents when Matt’s change was made, colors represent any time an execution plan is compiled.

Gregg: Wow! That’s awesome, Matt. How does this compare to a typical performance improvement you might work on?
Matt: As a percentage of the original, this is actually less impressive than some of the recent performance improvements I have found. I’ve found improvements of 10x, 100x and even 800x in other stored procedures.

However, because of the large volume of calls to this stored procedure, and its demand on the CPU, this improvement is likely the most significant improvement I’ve found. Especially with how important this search is to our clients and their guests.

Gregg: When your job deals with performance, how exciting is it to find stuff like that?
Matt: It was very exciting to see the results trickle in on the first day I put this into place. When I was testing these changes on a copy of the production server I was seeing only a 30% improvement, so it was great to see the actual results exceed that.

Gregg: Any high fives or chest bumps in the office that day?
Matt: No high fives or chest bumps though, because we work in different offices :).

Gregg: Next time I see you Matt, high fives all around.
Matt: You got it.

Gregg: Let me wrap up with a couple of final questions. Sometimes it’s easy to think a man with your skills should only be building new things. Why is this role of continuous improvement so important to the overall platform?
Matt: As the system becomes more complex and we have more customers with more data in a shared database, overall system performance will degrade. For that reason it is important to continuously improve the system to counteract this and ensure that customers are receiving the best possible experience when using the site.

It’s also important to improve existing processes to ensure that developers are able to work efficiently in the code base, which becomes increasingly important as we onboard new people and the size of the engineering team grows.

Gregg: Any final shoutouts to other team members who are similarly doing great stuff behind the scenes?
Matt: Everyone on my team is dedicated to improving the performance of the overall platform, and all have done a great job in this capacity. As for other developers outside my team I would like to call out Jonathan Little and Paul Wagner on their rock-solid technical expertise and work behind the scenes to ensure that major application deployments go smoothly.

Gregg: I know you’re busy, Matt, so thanks for taking the time to give us a peek behind the curtain. Fascinating stuff.
Matt: My pleasure!