Kata SQL

SQL Tuning: Function Calls

Caution: This kata does not currently have any known supported versions for SQL. It may not be completable due to dependencies on out-dated libraries/language versions.

Your manager has given you a task to create a report to measure the impact of the next company-wide salary increase. The rules for the salary increase are encapsulated in the function pctIncrease, which takes a department_id as a parameter and returns the percent increase as a value between 0 and 1.

You’ve managed to create a query that would produce the desired results, but it is currently very slow and doesn’t finish in the required time window.

Your objective in this kata is to optimize this query. The success criteria is to manage to run the query within the allowed kata solution time window (12 seconds for SQL katas).

The provided initial solution produces the correct result, but it does not run within the allowed time window.

Data model

Table: Employees
----------------
employee_id   INT / PK
first_name    TEXT
last_name     TEXT
salary        DECIMAL
department_id INT / FK
Table: Departments
------------------
department_id   INT / PK
department_name TEXT

pctIncrease function signature:

pctIncrease(dept_id INT) RETURNS decimal

Solution given:

SELECT e.employee_id,
       e.first_name,
       e.last_name,
       d.department_name,
       e.salary AS old_salary,
       e.salary * (1 + pctIncrease(e.department_id)) AS new_salary
  FROM employees   e,
       departments d
 WHERE e.department_id = d.department_id
 ORDER BY 1;

Three ways to optimize:

1. Use Temp Table

CREATE TEMP TABLE department(
department_id integer,
department_name text,
increase decimal);
INSERT INTO department SELECT
department_id, department_name, pctIncrease(department_id) as increase
FROM departments;
SELECT e.employee_id, 
       e.first_name,
       e.last_name,
       d.department_name,
       e.salary AS old_salary,
       e.salary * 1 + d.increase as new_salary
       from Employees e,
       department d
       WHERE e.department_id = d.department_id
       ORDER BY 1

1. Use subquery

SELECT e.employee_id,
       e.first_name,
       e.last_name,
       d.department_name,
       e.salary as old_salary,
       e.salary*d.pct as new_salary
       from Employees e,
       (SELECT department_id,
       department_name, 
       1 + pctIncrease(department_id) pct 
       FROM departments) d
WHERE e.department_id = d.department_id
ORDER BY 1;

1. Use CTE

WITH CTE AS (SELECT d.department_id,
                    d.department_name,
                    pctIncrease(d.department_id) as pct
            FROM departments d
)
SELECT e.employee_id,
       e.first_name,
       e.last_name,       
       e.salary as old_salary,
       d.department_name,
       e.salary*(1+d.pct) as new_salary 
       FROM Employees e
       INNER JOIN CTE d
       ON e.department_id = d.department_id
       ORDER BY 1;

Link

Reference