Pages

25 March, 2015

Readable and Fast Math in SQL

For my dissertation, I write a lot of SQL queries, doing some Math on the data. For instance, the following query computes the relative times from a numeric timestamp t, and scales the result up by 10000.

-- query 1
with Q    as (select t,v from csv_upload),
     Q_b  as (select min(t) as t_min, max(t) as t_max from Q)
select 10000 * (t - (select t_min from Q_b))
             / (select t_max - t_min from Q_b) as tr from Q

As you can see, I use CTEs to be able to read my code ;-). However, the select statements in the final subqueries, extracting scalar values from the computed relations with one record, impair the readability of the actual Math that is to be computed.

That is why modern SQL databases allow columns from parent subqueries to be used in nested child subqueries. The following query computes the same result.

-- query 2
with Q    as (select * from csv_upload),
     Q_b  as (select min(t) as t_min, max(t) as t_max from Q)
select (select 10000 * (t     - t_min)
                     / (t_max - t_min) from Q_b) as tr from Q

Finally, another, if not the best way of writing such queries is the following.

-- query 3
with Q    as (select * from csv_upload),
     Q_b  as (select min(t) as t_min, max(t) as t_max from Q)
select 10000 * (t     - t_min)
             / (t_max - t_min) as tr from Q,Q_b

Even though all three queries are very similar, and yield the same result, I saw notable differences in query execution time. In general, query 2 was a bit slower, and query 3 was a bit faster than the others.

Conclusion
For my queries, using nested columns improves readability but decreases performance. If you have computed relations with one record, such as the boundary subquery Qb, it is safe to join these relations with your data.

11 March, 2015

A Case for CoffeeScript: Object Composition

I have been using CoffeeScript for over four years now (since 2011) and will never go back.1
Here is a snippet that may tell you why. It uses several basic features of CoffeeScript that make code more readable and much shorter than the vanilla JavaScript version of the same code (at the right side).

# Use CoffeeScript and stay DRY! (Don't repeat yourself)     var $f, abc1, abc2, abc3, framework;
# For instance, by using short notation {a,b,c,...}
# for object composition from variables.                       $f = framework = (function() {
#                                                              var count;
# Here is a complete example, using the notation               count = 0;
# to reduce the number of lines of code (LoC)                  return {
# of an artificial object creation framework:                    createProp: function(name, n) {
                                                                   return "This is " + name + " no. " + n;
$f = framework = do ->                                           },
  count = 0                                                      enhanceProp: function(prop) {
  createProp:  (name,n) -> "This is #{name} no. #{n}"              return "" + prop + ", enhanced!";
  enhanceProp: (prop)   -> "#{prop}, enhanced!"                  },
  createAbcObject: ->                                            createAbcObject: function() {
    # 1. basic variable setup                                      var a, b, c;
    a = $f.createProp "a",count                                    a = $f.createProp("a", count);
    b = $f.createProp "b",count                                    b = $f.createProp("b", count);
    c = $f.createProp "c",count                                    c = $f.createProp("c", count);
                                                                   if (count === 0) {
    # 2. more fiddling with the variables ...                        a = $f.enhanceProp(a);
    if count == 0 then a = $f.enhanceProp a                        }
    count++                                                        count++;
                                                                   return {
    # 3. finally compose and return the a-b-c object                 a: a,
    {a,b,c}                                                          b: b,
                                                                     c: c
abc1 = $f.createAbcObject()                                        };
                                                                 }
abc2 = $f.createAbcObject()                                    };
                                                             })();
abc3 = $f.createAbcObject()
                                                             abc1 = $f.createAbcObject();
# You can also use it for DRY logging
# to avoid quoting var names                                 abc2 = $f.createAbcObject();

console.log "objects created", {abc1,abc2,abc3}              abc3 = $f.createAbcObject();

# OMG! Over 50% LoC saved. Even with all these               console.log("objects created", {
# comments, CoffeeScript is still shorter and more             abc1: abc1,
# readable than the JavaScript version of the code.            abc2: abc2,
#                                                              abc3: abc3
# Stay DRY! Use CoffeeScript!                                });


1Unless somebody pays me enough money to waste my time using vanilla JS ;-).