Over a million developers have joined DZone.

Slow SQL Queries Are Killing Your Recommendation Engine

In today’s fast-paced world, users won’t wait minutes for your recommendation engine to query the database.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

In today’s fast-paced world, users won’t wait minutes for your recommendation engine to query the database.

In fact, you’re lucky if they’re willing to wait more than a few seconds.

The question is then: Is your relational database performing at the speed required to offer relevant and insightful recommendations to your users? If not, your slow SQL queries could be to blame.

In this series on SQL strain, we’ll dive into the causes – and cures – of relational database (RDBMS) performance issues, including the future-proof alternative of graph databases.

Previously, we’ve covered five sure signs of SQL strain and a comparison between RDBMS and graph database models. This week, we’ll dive into the specific example of a recommendation engine and compare relational databases with graph databases in terms of both data modeling and writing queries.

Data Modeling in a Relational Database Versus a Graph Database

In a relational database, data modeling for a simple recommendation engine requires creating multiple tables. You’d create separate tables for customers, orders and products, as well as intermediate JOIN tables that represent which customers purchased which products in which order.

In contrast, data models for a graph database can be easily sketched on the back of a napkin. A customer purchased a certain product. The customer-to-product relationship is stored as part of storing the order. Other customers may have purchased the same product.

With this simple database model, you can easily ask, “What other products did those people purchase?”

Modeling a recommendation engine with a graph database is whiteboard-friendly. It isn’t just intuitive for developers but for everyone familiar with the business domain.

In other words, with a graph database the logical model – the way we think about the problem – corresponds to the physical model – the way the data is stored, queried and visualized by the database.

For many interesting data problems people are facing today – especially building relevant recommendation engines – it’s not enough to know that two entities (products, users, ratings, reviews, etc.) are connected. After all, a relational database can tell us that much.

It’s also important to know about those data connections: their meaning, their significance and their strength, weight or quality. In our recommendation engine example these might be questions like, Which products are related and what’s the strength of that relationship? Which users have left a high-quality (or low-quality) reviews for a particular product? How many friends-of-friends bought a similar product?

A fully featured graph database answers these types of data relationship questions, enabling you to incorporate relevant information about the characteristics or strength of those connections.

SQL Queries Vs. Graph Database Queries

Unlike relational SQL queries, graph database queries are straightforward to write and understand.

Graph databases often have their own syntax for such queries. In the case of Neo4j this syntax is governed by a simple but expressive language called Cypher that is purpose-built for traversing data relationships.

Cypher queries are much simpler than SQL queries. In fact, a long SQL query can frequently be compressed to many fewer lines in Cypher.

Here’s a sample Cypher query for a hypothetical recommendation engine:

MATCH (u:Customer {customer_id:’customer-one’})-[:BOUGHT]->(p:Product)<- [:BOUGHT]-(peer:Customer)-[:BOUGHT]->(reco:Product) 
WHERE not (u)-[:BOUGHT]->(reco) 
RETURN reco as Recommendation, count(*) as Frequency 

This Cypher query says that for each customer who bought a product, the recommendation engine should look at the products that peer customers have purchased and then suggest them to the current user. The WHERE clause removes products that the customer has already purchased, since we don’t want to recommend something the customer has already bought.

Each of the arrows in the MATCH clause of the Cypher query represents a relationship that would be modeled as a many-to-many JOIN table in a relational model with two JOINs each. So even this simple query encompasses six JOINs across tables.

Here’s the equivalent SQL query:

SELECT product.product_name as Recommendation, count(1) as Frequency 
FROM product, customer_product_mapping, (SELECT cpm3.product_id, cpm3.customer_id 
          FROM Customer_product_mapping cpm, Customer_product_mapping cpm2, Customer_product_mapping cpm3 
          WHERE cpm.customer_id = ‘customer-one’ 
          and cpm.product_id = cpm2.product_id 
          and cpm2.customer_id != ‘customer-one’ 
      and cpm3.customer_id = cpm2.customer_id 
      and cpm3.product_id not in (select distinct product_id 
          FROM Customer_product_mapping cpm 
          WHERE cpm.customer_id = ‘customer-one’) 
   ) recommended_products 
WHERE customer_product_mapping.product_id = product.product_id 
and customer_product_mapping.product_id in recommended_products.product_id 
and customer_product_mapping.customer_id = recommended_products.customer_id 
GROUP BY product.product_name 
ORDER BY Frequency desc

This SQL query not only suffers from performance issues due to the JOIN complexity but it will also degrade in performance as the given dataset gets larger. When it comes to building your recommendation engine, this level of performance is simply unacceptable.

If your recommendation engine is powered by a relational database, users won’t wait for your suggestions to load, even if they are precisely relevant. And if your users won’t wait, your business suffers.

It’s time to choose the right tool for the job, moving your recommendation engine to a graph database that reduces database query times from minutes to milliseconds. And with somanyways to get started quickly, adding a graph database to your recommendation engine is the best investment of time you can make.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.

database,sql,nosql,data queries,recommendation engine

Published at DZone with permission of Andreas Kollegger, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}