5 ms·

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

by sqldba 10y ago

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.

znep 10y ago
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 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.
brightball 10y ago
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.
- infogulch 10y ago
  > 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.
- dhd415 10y ago
  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.
  - brightball 10y ago
    https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing https://www.microsoft.com/en-us/sql-server/sql-server-2016-p... Enterprise per core: $14,256
    - dhd415 10y ago
      That's the price for a 2-core license pack.
      - brightball 10y ago
        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.

by sqldba 10y ago

znep 10y ago

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 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.

brightball 10y ago

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.

infogulch 10y ago
> 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.
dhd415 10y ago
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.
- brightball 10y ago
  https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing https://www.microsoft.com/en-us/sql-server/sql-server-2016-p... Enterprise per core: $14,256
  - dhd415 10y ago
    That's the price for a 2-core license pack.
    - brightball 10y ago
      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.