Relational Database are a great way to structure data, but they have their warts. Certain kinds of structures don’t model well relationally, some are difficult to tune for performance, and some queries are just expensive no matter what. Still, with some smart design choices, some indexes, and some tuning of the execution plan, you can make things work.

Hambai approached a tuning problem with that perspective. The database had a huge pile of financial information- stock transactions, commodity valuations, and currency exchange rates. When it was new, queries were fast, but now, years on, performance ground to a halt. One query that drew his attention was one for accessing the latest exchange rate for four different currencies. It was run frequently, and each access took up to thirty seconds.

Buried deep in a Postgres stored procedure, Hambai found this:

CREATE LOCAL TEMP TABLE cur_index_temp (
   rate_id INTEGER
);

INSERT INTO cur_index_temp(rate_id) VALUES(1);
INSERT INTO cur_index_temp(rate_id) VALUES(8);
INSERT INTO cur_index_temp(rate_id) VALUES(5);
INSERT INTO cur_index_temp(rate_id) VALUES(6);

FOR trec IN SELECT rate_id FROM cur_index_temp
LOOP
       SELECT INTO l_res.name c1.name || '/' || c2.name
       FROM currency c1 INNER JOIN rates ON (c1.id = rates.curr1_id) INNER JOIN currency c2 ON (c2.id = rates.curr2_id)
  WHERE rates.id = trec.rate_id;

    l_res.url = '/valuten/analysis.php?rid=30&s=&type=1&itemid=' || trec.rate_id::text;

  SELECT INTO l_res.last, l_res.change rates_history.last, rates_history.open
          FROM rates_history
            WHERE rates_history.rate_id = trec.rate_id
         ORDER BY rates_history.chk_time DESC
            LIMIT 1;

        l_res.change = round( CAST(l_res.last - l_res.change AS numeric), 2 );
     RETURN NEXT l_res;
END LOOP;

It wasn’t that the original developer had used loops where a smartly-designed query could have sufficed that bother Hambai. It wasn’t that this block of code was buried deep in a 5,000 line stored procedure that mostly contained dead branches that would never execute. It wasn’t that with millions of records on the rates_history table, and no indexes, the ORDER BY clause was punishingly slow. It was that this particular “algorithm” for data-access was used for all of their time-based pricing data, including stock prices. With all the spaghetti code and general mess, it was easier to rewrite each stored procedure from scratch than it was to try and fix anything.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!