Re: [sqlite] Performance in a case of big columns number
Thanks! I hope that it will work faster. If you have any links to articles describing such dark sides of SQLite or some techniques of using it such as this, I'll be very grateful if you write them here. Pavel Ivanov wrote: > Yes, you will be able to find information you need. You can store your > data like this: > > time | val_num | value | > |---|---| > [time_1] 1 [value_1.1] > [time_1] 2 [value_2.1] > ... > [time_1] [value_.1] > [time_2] 1 [value_1.2] > [time_2] 2 [value_2.2] > ... > [time_2] [value_.2] > [time_3] 1 [value_1.3] > [time_3] 2 [value_2.3] > ... > [time_3] [value_.3] > > Then your select will look like this: > > SELECT ... > FROM table_name t1, table_name t2, table_name t456, table_name t654 > WHERE t1.time > 1000 AND t1.time < 1500 > AND t1.time = t2.time > AND t1.time = t456.time > AND t1.time = t654.time > AND t1.val_num = 1 > AND t2.val_num = 2 > AND t456.val_num = 456 > AND t654.val_num = 654 > AND (t1.value > t2.value + 3 OR t456.value != t654.value) > > > It looks like it's more complicated but I believe it will work faster > than your multi-column approach. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance in a case of big columns number
Please sorry for my terrible Engilsh. :) Thanks for the answer. Yes, I know that it is bad design in the common case. But I have to use it because I have data which has following format: time | value_1 | value_2|value_ |---|-| [time_1] [value_1.1] [value_2.1] ... [value_.1] [time_2] [value_1.2] [value_2.2] ... [value_.2] [time_3] [value_1.3] [value_2.3] ... [value_.3] ... And I must filter it by comparing some values, for example, as this: SELECT FROM table_name WHERE time > 1000 AND time < 1500 AND value_1 > value_2 + 3 OR value_456 != value_654 I asked this question because SQLite may use such algorithms which are very slow with very big number of colums and may be in this case it will work rapidly if, for example, I will combine values in such manner: time | value_1_to_value_100 | value_101_to_value_200 | ... |-|| [time_1] [value_1_to_value_100.1] [value_101_to_value_200.1] ... [time_2] [value_1_to_value_100.2] [value_101_to_value_200.2] ... [time_3] [value_1_to_value_100.3] [value_101_to_value_200.3] ... And I will write some functions for extracting values from them and will filter data by following query: SELECT FROM table_name WHERE time > 1000 AND time < 1500 AND get_value(value_1_to_value_100, 1) > get_value(value_1_to_value_100, 2) + 3 OR get_value(value_400_to_value_500, 56) != get_value(value_600_to_value_700, 54) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Performance in a case of big columns number
Hello. Data in my program has such format that there is useful to place it in the database in many (thousands) columns. Please, answer me: does SQLite work more slowly when it has very big number of columns in the table (with the same total amount of data)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
