Post navigation

Common Table Expression (CTE) SQLServer Queries with J

I’ve been blogging long enough to observe that your awesome posts are often ignored while your little “one-offs” sometimes strike hit gold. This is particularly true for “code example” posts. When I’m trolling for code — the geek equivalent of trolling for babes — I don’t want to read about the author’s programming philosophy or what the hell he thinks about the idiot in the White House. Just show me the code and shut up!

CTE queries essentially create temporary virtual tables during query execution. They are similar to nested SQL queries but are easier to code, more general, (see recursive CTE queries), and often perform better than their convoluted equivalents. This example creates two virtual tables cte1 and cte2 that are then used to compute a quick histogram.

You can call CTE queries with the J ODBC interface. The following assumes a SQLServer ODBC connection dsnhistory.

They scale to tens of millions of data points; returning results in a few seconds on my laptop. The SQL CTE, shown above, takes about three seconds running on 180,000 row tables on my employer’s full warp servers. If I could convince the masses to adopt languages like J a large part of my job would disappear. Fortunately, the world is hostile to terse elegance!

having the WITH _ AS _ can prevent deep nesting of queries, actually a prime use for the ‘variable’ concept, although the setting of a variable that is only used once doesn’t really add much of a time savings.

I suspect measurements of the J verbs histogram and histogram2 do not include the creation of the arguments to each, something the SQL seems to include.

A =: createHistogramArg ” NB. perhaps this takes a lot of time
]histogram A NB. ‘a few seconds’ … maybe so