i have a problem with QSqlQuery and GROUP BY. My database is inMemory and is created like this:
query.exec("create table if not exists ProzSchnitte (id integer primary key autoincrement, Kanal uint, ProgNr uint, WkzNr uint, WkzBez blob, BearbNr uint, "
"Offset uint, Datum uint, Uhrzeit uint, count uint, SchnittHeader blob, xVals blob, yVals blob, Strom1 blob, Strom2 blob, Strom3 blob)");
I then writing my data into this table like this:
query.prepare(QString("INSERT INTO ProzSchnitte (Kanal, ProgNr, WkzNr, WkzBez, BearbNr, Offset, Datum, Uhrzeit, Count, SchnittHeader, xVals, yVals, Strom1, Strom2, Strom3) VALUES (%1, %2, %3, ?, %4, %5, %6,%7, %8, ?, ?, ?, ?, ?, ?) ")
.arg(TempSchnitt.Header.Kanal).arg(TempSchnitt.Header.Programmnummer).arg(TempSchnitt.Header.Werkzeugnummer)
.arg(TempSchnitt.Header.Bearbeitungsnummer).arg(TempSchnitt.Header.Offset).arg(TempSchnitt.Header.Datum)
.arg(TempSchnitt.Header.Uhrzeit).arg(impCount));
query.bindValue(1,HeaderArray);
query.bindValue(2,x);
query.bindValue(3,y);
query.bindValue(4,Strom1);
query.bindValue(5,Strom2);
query.bindValue(6,Strom3);
query.exec();
query.finish();
After that i am reading the data like this:
QSqlQuery q(QSqlDatabase::database("ProzAnaDB"));
q.prepare("select * from ProzSchnitte GROUP BY Kanal, ProgNr, Offset, WkzNr, BearbNr, Count order by Count, Kanal, ProgNr, Offset, BearbNr");
q.exec();
The Execution of this is extremly slow and takes about 8 seconds but if i make a select without GROUP BY it is executed in under 10 milliseconds.
I have read that i may have to set so indexes in my table but i have no clue what that means or how i could do that.
I made an EXPLAIN of my query and this was the result.
QSqlRecord( 8 )
" 0:" QSqlField("addr", int, generated: yes, typeID: 1) "0"
" 1:" QSqlField("opcode", QString, generated: yes, typeID: 3) ""
" 2:" QSqlField("p1", int, generated: yes, typeID: 1) "0"
" 3:" QSqlField("p2", int, generated: yes, typeID: 1) "0"
" 4:" QSqlField("p3", int, generated: yes, typeID: 1) "0"
" 5:" QSqlField("p4", QString, generated: yes, typeID: 3) ""
" 6:" QSqlField("p5", QString, generated: yes, typeID: 3) ""
" 7:" QSqlField("comment", , generated: yes, typeID: 5) ""
Can somebody explain me what i could do to boost the performance of this query?
Aucun commentaire:
Enregistrer un commentaire