Charts that use every row of a big result

SQLly charts used to read the first 10,000 rows. Now sums, histograms and line charts use every row, even of results spilled to disk, spikes included.

By

SQLly's charts used to read the first 10,000 rows of a result and quietly call that the data. Since v1.37 every row counts: sums, averages and histograms use the whole result, even one spilled to disk, and a line chart of a million rows keeps its spikes.

The 10,000-row shortcut

When I built the Chart tab, I capped it at the first 10,000 rows so a big result couldn't stall the app. The chart said so in a note, which is better than nothing. But a bar chart of "revenue by region" that only summed the first 10,000 of 400,000 order lines isn't a smaller version of the truth. It's just wrong, with a footnote.

And results big enough to spill to disk didn't get a chart at all: the tab locked with "Result too large".

Now every row feeds the chart

The chart reads the result as a stream, in memory or straight from the spill file, on a background task, with a Charting… label and a percentage while it works. What it keeps depends on the chart:

  • Bar, pie and donut with an aggregate (Sum, Average, Count and so on) keep one running total per category, so every row lands in its bar. The bars you see are still capped at 200, ranked by your Order setting, because past that a bar is thinner than a pixel.
  • Histograms count every value into their bins, not a sample.
  • Line, area and scatter charts of more rows than they can draw split the rows into consecutive buckets and keep each bucket's minimum and maximum. A million rows become a couple of hundred points, and a note says how many rows they summarize.
A line chart of one million rows of a noisy wave with occasional spikes, drawn as a few hundred points that keep every spike, with a note saying how many rows the points summarize
A million-row line chart. The spikes are single rows, and they survive the downsampling.

Why min and max, not averages

The obvious way to shrink a million points is to average each bucket. That's also the way to make your outliers disappear: one bad reading averaged with 4,999 normal ones is a slightly-less-normal point. For a database tool that's backwards, because the weird row is usually why you're looking. Keeping each bucket's minimum and maximum means a single spike still reaches the top of the chart. And if you click it, SQLly jumps to that row.

Clicking still finds the rows

Clicking a bar, slice or histogram bin on a normal result filters the grid to the rows behind it. On a spilled result that works too: SQLly collects the matching rows from the spill file in the background into a small file of their own and shows them on the Grid tab under a banner, with Clear to get back. It's the same drill-through the pivot got. On a line or scatter chart, a click jumps to that point's row.

What's still capped

  • A chart draws at most 200 categories and 4 series. Those caps are about what's readable, and the chart says when one kicked in.
  • To decide which columns are numbers and which are labels, the chart looks at the first 10,000 rows. The values themselves come from every row.
  • A category chart over a column with more than 100,000 distinct values tracks the first 100,000 and says so. If you're bar-charting 100,000 categories, though, a bar chart isn't really what you need.
A histogram in the Chart tab binning one numeric column of a result
Histograms now count every value of the column, not a sample.

All of this is in v1.37. Charts of small results got better along the way, since anything over 10,000 rows used to be partly ignored.