code it is slow.

[valgaba]

I have a code but it is slow.

Is there any way to do this differently faster or efficient?



QSqlQuery query(db);


int row;
QDateTime FechaHoy;

query.exec("SELECT rowid,* FROM HORAS");
query.first();


QSqlQuery queryFecha(db);
queryFecha.prepare("UPDATE HORAS SET"
" HORA = ?"
" WHERE rowid = ?");


while (query.isValid()){
QSqlRecord rec = query.record();

row=rec.value("rowid").toInt();


if(rec.value("HORA").toDateTime().time()<= QTime::currentTime()){
FechaHoy.setDate(QDate::currentDate().addDays(1));

}else{
FechaHoy.setDate(QDate::currentDate());
}



FechaHoy.setTime(rec.value("HORA").toDateTime().time());

queryFecha.addBindValue(FechaHoy);
queryFecha.addBindValue(row) ;
queryFecha.exec();
query.next();


}


}


thanks

[spirit]

Move it in other thread. This is nice how to (http://www.linuxjournal.com/article/9602).

[valgaba]

is not possible in a single update statement

[spirit]

is not possible in a single update statement
Please, explain.

[ChrisW67]

is not possible in a single update statement

Sure it is. Here's the Sqlite example:


create table HORAS ( HORA varchar(19) );
insert into HORAS values('2012-08-14 04:03:00');
insert into HORAS values('2012-08-14 05:00:00');
insert into HORAS values('2012-08-13 04:03:00');
insert into HORAS values('2012-08-13 05:00:00');

select 'Run at: ' || datetime();
select rowid, date(HORA), time(HORA) from HORAS;
select '---';

update HORAS
set HORA =
CASE
WHEN time(HORA) <= time() THEN datetime(date(), time(HORA), '+1 day')
ELSE datetime(date(), time(HORA))
END;

select rowid, date(HORA), time(HORA) from HORAS;

I am fairly certain a workable Oracle equivalent would be possible.

If you persist with the iterative approach then here are some tips:

As described in the QSqlQuery docs, "For the sake of efficiency, there are no functions to access a field by name." It goes on to describe using record().indexOf(). Do this name to column number conversion only once as in the example.
Better still, explicitly name the columns you need, and only those columns, in your query at line 7 and use the column index numbers. No need to fetch 10 columns when you only need one.
Convert the column value to a QDateTime only once.
The result will always be either today's date or tomorrow's date with the column's existing time so you can rearrange the code a bit.
At least for Sqlite rowid is not an int, it is a 64-bit number.
If you are running this on a million row table then you are executing a million queries: this will take some time no matter what.
If you are doing this in Sqlite do it inside a transaction: see http://www.sqlite.org/faq.html#q19 which would equally apply to Delete I expect.


Something like:


db.transaction();
while (query.next()) {
QDateTime FechaHoy = query.value(1).toDateTime();
FechaHoy.setDate(QDate::currentDate());
if (FechaHoy.time() <= QTime::currentTime())
FechaHoy = FechaHoy.addDays(1);

// bind and exec()
}
db.commit(); // I have left out error checking