5 ms·

You are not supposed to have a GUI view when you have 100K+ entries. Excel misguides people to think that database management is easy. The reality is it is not

You are not supposed to have a GUI view when you have 100K+ entries. Excel misguides people to think that database management is easy. The reality is it is not possible to have a GUI view of middle-large databases with millions of entries because it is computationally impossible

andi999 5y ago
Why shd that be computationally impossible?
JacobThreeThree 5y ago
>You are not supposed to have a GUI view when you have 100K+ entries. Why not?
- KronisLV 5y ago
  Would also like to know, seems like a perfect use case for the Flyweight design pattern - just get the total count of records for when needed (e.g. size of scrollbar) and show the stuff that should be on screen at any given moment, loading more in dynamically. Throw in allowing doing operations on ranges that are off screen and you should be good.
screye 5y ago
One thing I've learnt over the years, is that users do not care about the inherent complexity of your problem. Like water, they will always choose the path of least resistance. It is why people would rather copy-paste documents than learn git, despite versioning being inherently complex. It is why people complain about android, but only use 1st party preinstalled apps or freemium ad-infested crap. Excel works and it is easy. It does not matter how hard the underlying problem is. I hate having to use excel too, but I have found myself periodically relying on it when timelines get too narrow and having a ready-made interactive dashboard is convenient. That being said, I can't imagine using it for a use-case where the rows-of-interest are greater than a few dozen.
- Aloha 5y ago
  a few thousand or more is practical, it depends on what the data is, if the data is inherently non-relational (a master parts list, a user list, a bunch of logged data points) then all is well, it breaks down based on how big the data set is.. and what the dataset is.
chasd00 5y ago
100k+ is pretty manageable in the Excel GUI. When you get above 1M then it starts getting dicey. I have a junior dev on my team that is very good at python. I only consider him "jr" in the language/tech he's assigned to work on daily, in python i consider him pretty solid. When we need data work done on csv's larger than I can ask an Analyst to do in Excel I give it to him to write some python against. Finding and exploiting his python skills has won the guy a couple bumps in base pay.
- Aloha 5y ago
  Very much agreed - and the upper limit is a little more fuzzy, and has more to do with file size, record complexity (how many columns), and what you want to do with the data. At some point the overall pokeyness of excel when dealing with large datasets overcomes the inertia of "everyone already knows it" and "we'd need a environment to spin up something more complex".
- charlieyu1 5y ago
  I’m probably outdated on this, but old Excel format have an upper limit of 65536 rows and the new format has a limit of 1M rows. So that’s where I would estimate the limit of upper bound. Could be less, can’t really think it’s possible to have more.
shadowgovt 5y ago
It's not computationally impossible; it's a hard problem that nobody has solved because they haven't found the niche to monetize it. A user only has a tiny screen relative to the size of the backing data. The hard part is efficiently fetching the data to populate that screen. But it's not unsolvable; just tricky. Approaches like building realtime indexes speculatively based on likely next user query could help. Google Sheets actually works a bit like this already, as it's backed by an online datastore.
- Existenceblinks 5y ago
  Non-tech people are used to all-in-one data mindset (load everything into memory manner). The only query type they have experienced is a dumb full-text search. Most people don't have know filter keywords for google search, twitter search etc. Even though they are used to scrolling concept (view portion of data), they have no idea of querying to see 1 interested thing at a time.

andi999 5y ago

Why shd that be computationally impossible?

JacobThreeThree 5y ago

>You are not supposed to have a GUI view when you have 100K+ entries. Why not?

KronisLV 5y ago
Would also like to know, seems like a perfect use case for the Flyweight design pattern - just get the total count of records for when needed (e.g. size of scrollbar) and show the stuff that should be on screen at any given moment, loading more in dynamically. Throw in allowing doing operations on ranges that are off screen and you should be good.

screye 5y ago

One thing I've learnt over the years, is that users do not care about the inherent complexity of your problem. Like water, they will always choose the path of least resistance. It is why people would rather copy-paste documents than learn git, despite versioning being inherently complex. It is why people complain about android, but only use 1st party preinstalled apps or freemium ad-infested crap. Excel works and it is easy. It does not matter how hard the underlying problem is. I hate having to use excel too, but I have found myself periodically relying on it when timelines get too narrow and having a ready-made interactive dashboard is convenient. That being said, I can't imagine using it for a use-case where the rows-of-interest are greater than a few dozen.

Aloha 5y ago
a few thousand or more is practical, it depends on what the data is, if the data is inherently non-relational (a master parts list, a user list, a bunch of logged data points) then all is well, it breaks down based on how big the data set is.. and what the dataset is.

chasd00 5y ago

100k+ is pretty manageable in the Excel GUI. When you get above 1M then it starts getting dicey. I have a junior dev on my team that is very good at python. I only consider him "jr" in the language/tech he's assigned to work on daily, in python i consider him pretty solid. When we need data work done on csv's larger than I can ask an Analyst to do in Excel I give it to him to write some python against. Finding and exploiting his python skills has won the guy a couple bumps in base pay.

Aloha 5y ago
Very much agreed - and the upper limit is a little more fuzzy, and has more to do with file size, record complexity (how many columns), and what you want to do with the data. At some point the overall pokeyness of excel when dealing with large datasets overcomes the inertia of "everyone already knows it" and "we'd need a environment to spin up something more complex".
charlieyu1 5y ago
I’m probably outdated on this, but old Excel format have an upper limit of 65536 rows and the new format has a limit of 1M rows. So that’s where I would estimate the limit of upper bound. Could be less, can’t really think it’s possible to have more.

shadowgovt 5y ago

It's not computationally impossible; it's a hard problem that nobody has solved because they haven't found the niche to monetize it. A user only has a tiny screen relative to the size of the backing data. The hard part is efficiently fetching the data to populate that screen. But it's not unsolvable; just tricky. Approaches like building realtime indexes speculatively based on likely next user query could help. Google Sheets actually works a bit like this already, as it's backed by an online datastore.

Existenceblinks 5y ago
Non-tech people are used to all-in-one data mindset (load everything into memory manner). The only query type they have experienced is a dumb full-text search. Most people don't have know filter keywords for google search, twitter search etc. Even though they are used to scrolling concept (view portion of data), they have no idea of querying to see 1 interested thing at a time.