Pivoting results too big for memory
SQLly's Pivot tab used to lock on results spilled to disk. Now it pivots every row of a million-row result in the background, with drill-through.
Until v1.37, a result big enough to spill to disk locked SQLly's Pivot tab with "Result too large". Now it pivots every row of a million-row result in the background, memory stays flat, and double-clicking a cell still shows you the rows behind it.
The lock was an honest cop-out
SQLly spills big results to disk: past 50,000 rows (or 64 MiB) the rows go to a file and the grid pages them back. That keeps a 50-million-row query from eating your RAM. But the pivot was written for rows in memory, so for spilled results I just locked the tab and told you to aggregate in SQL first.
That was honest, at least. It was also annoying, because the whole point of a pivot is that you don't
know the shape you want yet. Rewriting a GROUP BY every time you want to drag a field to
a different axis isn't exploring, it's typing.
What it does now
Open the Pivot tab on a spilled result and it works like any other. Each time you change the layout, SQLly reads the spill file from start to finish on a background task and feeds every row into the pivot as it goes. A Pivoting… label shows the percentage, the app stays responsive, and memory doesn't grow with the row count, because only the pivot's totals are kept, never the rows.
It works with pivot directives too, so a saved query like this opens straight into the cross-tab however many rows it returns:
-- sqlly view: pivot
-- sqlly pivot: row:Category; column:Year; value:Revenue:sum
SELECT c.CategoryName AS Category, YEAR(o.OrderDate) AS [Year],
d.UnitPrice * d.Quantity AS Revenue
FROM dbo.OrderDetails d
JOIN dbo.Orders o ON o.OrderID = d.OrderID
JOIN dbo.Products p ON p.ProductID = d.ProductID
JOIN dbo.Categories c ON c.CategoryID = p.CategoryID;
Drill-through without loading everything
The other half of a pivot is asking "which rows made this number?" On a normal result, double-clicking a cell filters the grid to those rows. On a spilled result the grid only holds the window you're looking at, so there's nothing in memory to filter.
So SQLly does it on disk. A double-click starts a background pass over the spill file (Finding matching rows…) that copies just the matching rows into a second, smaller spill file. The Grid tab then shows that file under the usual banner, and Clear takes you back to every row. The drill file belongs to the result: running a new query or closing the tab deletes it, and if you double-click another cell before the first one finishes, the old pass is cancelled and its half-written file removed. I wrote tests for exactly that, because leaking temp files on every double-click would be a dumb way to fill a disk.
How it works
- The pivot builds incrementally. The engine used to take every row at once. Now it takes one row at a time and keeps only the running totals for each cell, so the same code serves a 50-row result in memory and a 50-million-row file.
- Stale scans get dropped. Drag a field while a scan is still running and that scan is cancelled; only the newest layout's result is ever shown.
- The Filters zone learns its values from the scan. The value picker for each field is filled in from the rows as they're read, up to 10,000 distinct values per field.
The honest limits
- Every layout change reads the whole file again. On a few hundred thousand rows you won't notice. On tens of millions you'll watch the percentage for a bit. I have a plan for making repeat pivots much faster, but it isn't built yet.
- Unsaved edits pause it. If you've staged edits in the grid of a spilled result, Pivot and Chart wait until you save or discard them.
- Sort, filter and diff still don't work on spilled results. Those need the whole set in memory at once. Pivot and charts don't, so they got done first.
It's in v1.37. Run something big and drag a field onto Rows.