Hacker News Reader

Comment by sqldba | Hacker News Reader

SQL Server does all of these things (for comparison).

It compiles queries and stores them in a cache. In many cases it can parameterise simple ones so even if the exact text changes it will pull back the matching plan (and it stores all this data so you can analyse later what plans were compiled, what was reused, which ones were similar, which were recompiled later - though hardly anyone deep dives into it because it's a tar pit of info and why bother instead of just focusing on worst cases).

Also when the query pulls data into memory it stays there in the buffer. If the rows change the buffers aren't discarded, they're flushed to disk (along with the log records). So if your query pulls back the exact same data then it's from memory sure but it's not stale or anything.

I don't consider any of these problems so I'm surprised how they could be interpreted as bad in other products. I don't understand enough about them to say but it's fun to discus.

View on HN

From reading what you wrote, what I think you are talking about is statement caching where it can cache the "how do I run this" but still has to read each row (or indexed row) from the buffer cache.

The mysql query cache actually caches the results of the query, see https://dev.mysql.com/doc/refman/8.0/en/query-cache.html so it doesn't even need to look at the rows at all.

SQL Server may have a similar thing now, I'm not up to date on it, but wanted to call out the distinction based on how you described it.

No question, SQL Server has a lot of awesome qualities for the right use case and has come a long long way since the Sybase days.

SQL Server does some great things for the data that's in it but if you ever want to get the data out (Elastic Search / Hadoop / Kafka / etc) you are in for a world of pain (and bills). The lock in alone is reason enough to steer clear of it for virtually all use cases.

As you start running into scaling issues and need to search for solutions that are faster to implement you'll reach for Enterprise and then for more cores and before you know it you're stuck with a $16,000 / core scaling cost that you can't avoid because you can't get your data out to offload things easily.

Dealing with SQL Server on a customer facing system is the stuff of nightmares.

> if you ever want to get the data out ... you are in for a world of pain

What do you mean by "get data out"? I've not had any issues getting rows out of SQL Server to be indexed externally.

It's unclear what you mean about the difficulties of getting data out of SQL Server since it's really no harder or easier than getting data out of any other RDBMS. Scaling SQL Server, just like scaling any other RDBMS, can be challenging since traditional RDBMSs don't distribute well. While some workloads are CPU-limited, that is not true for all workloads so the per-core licensing model of SQL Server (which ~$7k per core for 2016 Enterprise edition, not $16k) is not always prohibitive if your workload is more disk or RAM-intensive. There are points of differentiation between SQL Server, PostgreSQL, and MySQL that might make one better suited to a particular workload than others, but those situations are dependent on the details of a particular workload and something as general as "getting data out" or "on a customer-facing system" aren't specific enough to identify any one of those as more suitable than another.

https://www.microsoft.com/en-us/sql-server/sql-server-2016-p...

Enterprise per core: $14,256

That's the price for a 2-core license pack.

The notation shows per core and says sold as a 2 core pack. Does that make the actual price $28k for the pack or $14k for 2 cores. The way they list it is not particularly clear.

The SQL Server licensing model is "per core", but the minimum licensing increment is 2 cores which costs (2 * $7k = $14k). It's obviously a confusing pricing scheme, but try pricing out Oracle some time!

Aside from the cost aspect of commercial software, this is false. It is no more difficult to feed your data to another system than it is with any other relational databases. There is certainly no bill involved with exporting your data. In fact, SQL Server includes the tools you need to do this with software like SQL Server Integration Services. SQL Server has its own implementation of the ANSI SQL standard but this is true of every database system. The majority of it is compatible, it's only when you use certain functions that you have to consider porting them. SQL Server is the database used for many customer facing applications and there is often no nightmare involved.

With a PostgreSQL foreign data wrapper, you can actively push data into other data sources at the transactional level. You have pub sub functionality built in to let outside systems listen and react to data changes as well.

With SQL Server your options are variations of polling the database. We even tried posing the question of utilizing Elastic Search to developers at the PASS Summit recently and were widely met with groans of "Yea...that's a pain to do with SQL Server. You're basically out of luck."

For an internal standalone system it's great. For moving data into it do do analytics, great. For anything close to a real time web facing system with a growth pattern...absolute nightmare.

polling the database

Not true https://msdn.microsoft.com/en-us/library/t9x04ed2(v=vs.110)....

This is an impressive attempt to backup the original statements about SQL Server. It sounds like you may not be all that familiar with how the "Foreign Data Wrappers" work in Postgres and you are not familiar with the equivalent in SQL Server: Linked servers. Both implementations use OLEDB or ODBC drivers to provide transactional consistency to external applications.

Reply on news.ycombinator.com