3 ms·

The benchmarks are aimed at OLAP not OLTP workloads. If I had a large query set to run on each vendor I'd be more likely to hit compatibility issues which coul

The benchmarks are aimed at OLAP not OLTP workloads. If I had a large query set to run on each vendor I'd be more likely to hit compatibility issues which could mean fewer benchmarks going out. As it is I spend a lot of time getting hardware and software vendors together for these benchmarks. If caches were being hit I'd expect a lot more DBs hitting single millisecond times in my benchmarks but as far as I can see, there is a clear delta between the various setups I've tested: http://tech.marksblogg.com/benchmarks.html http://tech.marksblogg.com/benchmarks.html

pbecotte 9y ago
This is true, but there are steps between the two. It would certainly be possible to, for each query, record both the first time and the fastest time. You could also add a couple queries that are a little harder- a query with a self-join and a window function would certainly be interesting- without getting too far down the road of building a query set that will be hard to run on different systems. Even without those though, this is a good read, thanks for it!
georgewfraser 9y ago
My criticisms are specifically about OLAP use cases. Real OLAP queries have joins and subqueries. I hear you about compatibility. I think this benchmark would be greatly improved by rewriting the query to include a subquery and a fact-to-fact join using the same table. For example, you could do a subquery where you find the longest and shortest ride for each driver each day, and then join that back to the full table and calculate something. That would do a much better job of exercising the key features of the query planner, while still being simple and compatible.
- buremba 9y ago
  Real OLAP queries usually don't have JOINs. Instead, the data is denormalized at ingestion nad WINDOW, CTE ans subqueries are used at query time.
  - georgewfraser 9y ago
    This was true 10 years ago. Many people building data warehouses today just use a normalized schema and write views with lots of joins. It’s a much simpler workflow and data warehouses have gotten so fast that this actually works.
    - buremba 9y ago
      I don't know any technology that makes the JOINs efficient enough for low latency queries. If you're joining two tables that have billions of rows, you need too much memory and CPU resources in order to be able to map them. Do you know any DBMS that focuses on OLAP systems and suggest normalization?
  - StreamBright 9y ago
    I can just second that. Most DWHs arr denormalized because columnar storage formats love repetition.

pbecotte 9y ago

This is true, but there are steps between the two. It would certainly be possible to, for each query, record both the first time and the fastest time. You could also add a couple queries that are a little harder- a query with a self-join and a window function would certainly be interesting- without getting too far down the road of building a query set that will be hard to run on different systems. Even without those though, this is a good read, thanks for it!

georgewfraser 9y ago

My criticisms are specifically about OLAP use cases. Real OLAP queries have joins and subqueries. I hear you about compatibility. I think this benchmark would be greatly improved by rewriting the query to include a subquery and a fact-to-fact join using the same table. For example, you could do a subquery where you find the longest and shortest ride for each driver each day, and then join that back to the full table and calculate something. That would do a much better job of exercising the key features of the query planner, while still being simple and compatible.

buremba 9y ago
Real OLAP queries usually don't have JOINs. Instead, the data is denormalized at ingestion nad WINDOW, CTE ans subqueries are used at query time.
- georgewfraser 9y ago
  This was true 10 years ago. Many people building data warehouses today just use a normalized schema and write views with lots of joins. It’s a much simpler workflow and data warehouses have gotten so fast that this actually works.
  - buremba 9y ago
    I don't know any technology that makes the JOINs efficient enough for low latency queries. If you're joining two tables that have billions of rows, you need too much memory and CPU resources in order to be able to map them. Do you know any DBMS that focuses on OLAP systems and suggest normalization?
- StreamBright 9y ago
  I can just second that. Most DWHs arr denormalized because columnar storage formats love repetition.