Hacker News Reader

Comment by iagovar | Hacker News Reader

Excel is nice as a simple sandbox, and PowerQuery is amazing IMO, the problem is that it's basically impossible to work with large datasets. If you didn't know about it I recommend you to play a bit with it.

And check SQLiteStudio for Windows, is nice too.

View on HN

I handle hundreds of millions of rows in excel trivially.

PowerPivot is extremely amazing.

PowerBI is quite nice too.

Most people don't realize that Excel (like PowerBI) has an in-memory, compressed, column store database inside of it.

Loading hundreds of millions of rows into it takes a while, but given a commensurate amount of RAM and a reasonable data model (single table or star schema), performing aggregations with a pivot table is pretty snappy.

Well, for me with my personal PC with a quad core and 24gb DDR3, it gets unusable for about 1 million rows, but it's true that most of my datasets have around 10 columns or so.

You should be using PowerPivot in Excel.

Here's a video showing rapid pivots of 100m row dataset

https://youtu.be/0WwFJ0Zg3d8?t=2285

Mmm, I was using PowerQuery and then use the editor to do the transformations (don't remember exact names sorry), and I just couldn't cope with it.

Does SQLiteStudio let you cancel an unexpectedly slow query part way through? DB Browser for SQLite doesn't seem to, and I don't really know what I'm doing so often accidentally set off a query that can't use indices without doing "EXPLAIN QUERY PLAN" first.

It does.

> DB Browser for SQLite

If it helps, our development builds for quite a few months have:

https://nightlies.sqlitebrowser.org/latest/

Our recent 3.12.0-alpha1 release does as well:

https://nightlies.sqlitebrowser.org/latest/

The first beta release for 3.12.0 should be out next week. There's not much change in it though (mostly language string changes), as the alpha1 has turned out to be really stable. :)

Reply on news.ycombinator.com