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();
 
 
       }
 
 
  }

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();


       }


  }

To copy to clipboard, switch view to plain text mode 

thanks

[spirit]

Move it in other thread. This is nice how to.

[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;

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;

To copy to clipboard, switch view to plain text mode 

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

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

To copy to clipboard, switch view to plain text mode