search

ioBroker / ioBroker.sql

Store history data in SQL Database: MySQL, PostgreSQL or SQLite
MIT License
45 stars 25 forks source link

Add new number datatypes (integer) to increase performance #132

Open Percy2Live opened 4 years ago

Percy2Live commented 4 years ago

Currently the sql adapter only distinguishes between "Text", "Number" and "Bool". E.g. when selecting "Number" the data type in the created database is set to float. For most applications this works fine. Unfortunately I found out that there are some applications where it's a big downsize to store all numbers as float. I have a photovoltaik energy monitor which creates several hundred data points during a day. These data points are visualized in a graph by using Grafana.

The main issue with storing these values as float is that the performance of the database is very poor. A comparison to a database with plain integer values shows that the performance is significantly better with integers.

This also applies for some other applications where I noticed that the performance of the database (for number values) is not as good as it could be. Therefore it would be great if you could choose what kind of data type should be used.

Apollon77 commented 4 years ago

How a float schout be converted to int? Normal "round" logic (.5 up, else down?)

Percy2Live commented 4 years ago

Normal round logic should be sufficient for most applications. In case the original value isn't a decimal value but an integer, it doesn't even matter at all.