CODEWITHSUNDEEP
sqlsqlite
heczaco
heczaco

Reputation: 318

SQLite Update Query Optimization

So I have tables with the following structure:

TimeStamp,
var_1,
var_2,
var_3,
var_4,
var_5,...

This contains about 600 columns named var_##, the user parses some data stored by a machine and I have to update all null values inside that table to the last valid value. At the moment I use the following query:

update tableName 
    set var_##  = 
            (select   b.var_##  from   tableName  as 
                      where b.timeStamp <=   tableName.timeStamp and   b.var_##   is not null  
                      order by timeStamp desc limit 1)  
    where   tableName.var_##   is null;

Problem right now is the tame it takes to run this query for all columns, is there any way to optimize this query?

UPDATE: this is the output query plan when executin te query for one column:

update wme_test2 
set var_6  = 
        (select   b.var_6  from   wme_test2  as b
                        where b.timeStamp <=   wme_test2.timeStamp and   b.var_6   is not null  
                        order by timeStamp desc limit 1)  
   where   wme_test2.var_6   is null;

enter image description here

Upvotes: 0

Views: 234

Answers (1)

CL.
CL.

Reputation: 180070

Having 600 indexes on the data columns would be silly. (But not necessarily more silly than having 600 columns.)

All queries can be sped up with an index on the timeStamp column.

Upvotes: 1

Related Questions