4 ms·

I work at a power plant that uses Wonderware - a SCADA system that stores its time-series data in its own proprietary database that is accessed as a linked serv

I work at a power plant that uses Wonderware - a SCADA system that stores its time-series data in its own proprietary database that is accessed as a linked server via SQL Server. That means any query I want to fetch can't be optimized by the SQL Engine and makes any kind of analysis very expensive. From my understanding, something like an integrated extension to the server (not a linked/foreign server) would be great because the query optimizer would be able to plan its queries based on that information. Has anyone been in a situation like this and taken steps to mitigate the inefficiencies of a system like this?

Dangeranger 7y ago
This is exactly what Timescale does with PostgreSQL. Timescale is an extension to the database, and all the existing database query planning systems just work. I am not aware if MS SQLServer has anything equivalent.
- guscost 7y ago
  SQL Server has a column-oriented index, which makes certain aggregations in wide tables much faster than anything Postgres can do currently: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/get-started-with-columnstore-for-real-time-operational-analytics https://docs.microsoft.com/en-us/sql/relational-databases/in... I’m not aware of any out-of-the-box support for smart partitioning by time (Timescale’s main feature). You could set that kind of thing up manually but it would be a fair bit of work.
  - greggyb 7y ago
    You definitely don't want to use that columnstore for ingesting time series data. If you need real time reporting, you are better served with row-store. Columnstore engines tend to be optimized for read, rather than write. I know for certain that Microsoft's columnstore technology is not a good fit for true real time applications.
    - guscost 7y ago
      I haven’t used the columnstore in production and can’t vouch for it either. It makes sense that k-ordered write performance on column-oriented data would suffer, do you know of a good benchmark measuring this? Also I don’t like the phrase “true real time applications” at all. It is less useful than even Microsoft’s OLAP-Cube-ETL-OLTP word salad, and embedded developers in particular are going to be cringing if they read that.
      - greggyb 7y ago
        Fair. I use it as a shorthand for reporting scenarios with required data latency measured less than a second from transaction to reporting tier. Yes, there's an entire world of performance below 1 second, but it's a useful threshold. When I've been benchmarking SQL Server for such use cases, we've always found its columnar indices to be too much overhead.
    - billgraz 7y ago
      You can build a columnstore index in two ways in SQL Server. The easiest is as a non-clustered index on a regular rowstore table. Your inserts are going into the rowstore directly. This physically stores the rows as a rowstore with a columnstore index on top. The second is as a clustered columnstore index. This stores the rows directly as a columnstore. That's the way I'd store time series data. In this case, all the inserts go into a "delta rowstore" and are moved by a background service into the clustered columnstore structure. Your inserts proceed as fast as any insert into a rowstore. There is overhead to move the data since it's doing a fair bit of compression. But that's going to be true of anything that does compression. If you want really fast ingest, you can use an InMemory OLTP (aka Hekaton) structure and layer a column store index on top of that. And that InMemory structure can be persisted to disk. That gives you much better performance than a plain rowstore. You'd probably want to write something to move the data to a longer term structure but that would be the fastest way to ingest it I can think of. Using SQL Server :)
      - greggyb 7y ago
        It's a good overview you've shared of the storage technologies in SQL Server. I agree on memory-optimized being ideal for a real time service. The overhead of column indexing has ruled it out whenever I've benchmarked it for real time use cases. Last time was on SQL 2016, so latest optimizations may have brought it to par.

Dangeranger 7y ago

This is exactly what Timescale does with PostgreSQL. Timescale is an extension to the database, and all the existing database query planning systems just work. I am not aware if MS SQLServer has anything equivalent.

guscost 7y ago
SQL Server has a column-oriented index, which makes certain aggregations in wide tables much faster than anything Postgres can do currently: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/get-started-with-columnstore-for-real-time-operational-analytics https://docs.microsoft.com/en-us/sql/relational-databases/in... I’m not aware of any out-of-the-box support for smart partitioning by time (Timescale’s main feature). You could set that kind of thing up manually but it would be a fair bit of work.
- greggyb 7y ago
  You definitely don't want to use that columnstore for ingesting time series data. If you need real time reporting, you are better served with row-store. Columnstore engines tend to be optimized for read, rather than write. I know for certain that Microsoft's columnstore technology is not a good fit for true real time applications.
  - guscost 7y ago
    I haven’t used the columnstore in production and can’t vouch for it either. It makes sense that k-ordered write performance on column-oriented data would suffer, do you know of a good benchmark measuring this? Also I don’t like the phrase “true real time applications” at all. It is less useful than even Microsoft’s OLAP-Cube-ETL-OLTP word salad, and embedded developers in particular are going to be cringing if they read that.
    - greggyb 7y ago
      Fair. I use it as a shorthand for reporting scenarios with required data latency measured less than a second from transaction to reporting tier. Yes, there's an entire world of performance below 1 second, but it's a useful threshold. When I've been benchmarking SQL Server for such use cases, we've always found its columnar indices to be too much overhead.
  - billgraz 7y ago
    You can build a columnstore index in two ways in SQL Server. The easiest is as a non-clustered index on a regular rowstore table. Your inserts are going into the rowstore directly. This physically stores the rows as a rowstore with a columnstore index on top. The second is as a clustered columnstore index. This stores the rows directly as a columnstore. That's the way I'd store time series data. In this case, all the inserts go into a "delta rowstore" and are moved by a background service into the clustered columnstore structure. Your inserts proceed as fast as any insert into a rowstore. There is overhead to move the data since it's doing a fair bit of compression. But that's going to be true of anything that does compression. If you want really fast ingest, you can use an InMemory OLTP (aka Hekaton) structure and layer a column store index on top of that. And that InMemory structure can be persisted to disk. That gives you much better performance than a plain rowstore. You'd probably want to write something to move the data to a longer term structure but that would be the fastest way to ingest it I can think of. Using SQL Server :)
    - greggyb 7y ago
      It's a good overview you've shared of the storage technologies in SQL Server. I agree on memory-optimized being ideal for a real time service. The overhead of column indexing has ruled it out whenever I've benchmarked it for real time use cases. Last time was on SQL 2016, so latest optimizations may have brought it to par.