[odb-users] performance of updates in table with many rows for sqlite db

[Daniel James]

Hello

I'm not sure if this is a reasonable question but here goes:

Suppose I have the following object:

#pragma db object pointer(std::shared_ptr)
class Intensity
{

public:
    //
    // Relationships
    //
    odb::lazy_weak_ptr<Probe> probe;


    //
    // Data members.
    //
    float signal, backgroundSignal, normalisedSignal, predictedSignal;
    bool flag, extra_flag;
    unsigned long id();
private:
    friend class odb::access;

#pragma db id auto
    unsigned long id_;
};

I store about 6 million of these in a table. Then I try and update a
subset of about 40 000.

What is the quickest way to do this? At the moment it is proceeding at
a rate of about one update per second with the following SQL being
generated at each update:

UPDATE "Intensity" SET
"probe"=?,"signal"=?,"backgroundSignal"=?,"normalisedSignal"=?,"predictedSignal"=?,"flag"=?,"extra_flag"=?
WHERE "id"=?


Do I need to:

rethink the database design?
somehow use an index on the Record table to speed things up?
other strategy?

Many thanks,

Daniel