Hacker News Reader

Comment by ta12653421 | Hacker News Reader

When reading such extreme numbers, I'm always thinking what I may be doing wrong, when my MSSQL based CRUD application warms up its caches with around 600.000 rows and it takes 30 seconds to load them from DB into RAM on my 4x3GHz machine :-D

Maybe I'm missing something fundamental here

View on HN

Yes - OLAP database are built with a completely different performance tradeoff. The way data is stored and the query planner are optimised for exactly these types of queries. If you're working in an oltp system, you're not necessarily doing it wrong, but you may wish to consider exporting the data to use in an OLAP tool if you're frequently doing big queries. And nowadays there's ways to 'do both ' e.g. you can run the duckdb query engine within a postgres instance

This type of stuff is usually hyperoptimized for no reason and serves no real purpose, you are doing just fine

Could you run some query like select sum(banch of columns) from my_table and see how long it will take?

600k rows is likely less than 1GB of data, and should take about second to load into RAM on modern nvme ssd raids.

Would OLAP be better than OLTP for those queries you're doing?

I also had misfortune working with MSSQL is it was so so unbearably slow, because i couldnt upload data in bulk. I guess its forbidden technology

Or you didn't use MSSQL properly, there are at least 2 or 3 ways to do bulk upload on MS SQL, not sure in today era.

Maybe? Don't know. I never had problemes bulk uploading into Postgres tho, it's right there in documentation and I don't have to have a weird executable on my corporately castrated laptop

https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-...

That's one way, another was BCP.

But yeah if you are using python and loading row by row, or a large amount into a large table that has a clustered index, chances are that it'll be dead slow but that's expected.

The documentation you providee requires for the file to be present on the server side, not the client side, which is very dufferent from postgres.

As for BCP executable, i couldnt find a way for it to accept any type of date[time] at all

Reply on news.ycombinator.com