Extremely slow QSqlQuery SELECT on a small database
-
Hi!
I'm completely new to C++ and Qt but please don't hold that against me :)
I'm building a database front end in Qt as part of a university research project I'm involved in. The database was created in MS Access 2013 initially, converted to SQL Azure using the SSMA application and then hosted online. The access database is adequate for some of our needs but we also really need a custom front end that will operate on multiple OS's.
Now - to the problem.
I've got a table on my Azure SQL server which contains 1015 rows with 11 columns, most of them with no data inside. When I run a QSqlQuery to iterate through the table and store the values so they can be added to the GUI it takes upwards of 55 seconds (55610 ms to be exact).
The code is here:
@ QSqlQuery shipQuery(db);
shipQuery.prepare("SELECT ShipID, ShipName, ShipFlag, ShipRig, ShipTonnage, ShipStandardisedTonnage, ShipYearConstructed, ShipPlaceRegistered, ShipYearRegistered, ShipFrom, ShipTo FROM mydatabase.tblShips");
shipQuery.setForwardOnly(true);
shipQuery.exec();
while (shipQuery.next()) {
int shId = shipQuery.value(0).toInt();
ShipData* shData = new ShipData(shId);
shData->ShipName = shipQuery.value(1).toString();
shData->ShipFlag = shipQuery.value(2).toString();
shData->ShipRig = shipQuery.value(3).toString();
shData->ShipTonnage = shipQuery.value(4).toString();
shData->ShipStandardTonnage = shipQuery.value(5).toString();
shData->ShipYearConstructed = shipQuery.value(6).toString();
shData->ShipPlaceRegistered = shipQuery.value(7).toString();
shData->ShipYearRegistered = shipQuery.value(8).toString();
shData->ShipFrom = shipQuery.value(9).toString();
shData->ShipTo = shipQuery.value(10).toString();
shipData.insert(shId, shData);
}@Pulling the data into another application using a similar SELECT statement takes about 1 second so I'm wondering where I'm going wrong. A very helpful friend has been working with me on the build and guiding me through C++ and Qt but I'm feeling bad about monopolising their time, so if anyone can make any suggestions to speed this up I'd really appreciate it.
Thanks,
Mike
-
Hi, and welcome to DevNet,
without knowing how your shData is declared, I think your problem lies here:
@
ShipData* shData = new ShipData(shId);
@Is it really what you want: Create a new shData with every iteration?
I suggest to generate ShipData once before while {}. -
You can have a problem with the network latency if, each time you call shipQuery.next(), the driver execute a round trip to the server get next row.
The Qt documentation said that setForwardOnly must be called before preparing the query. Give it a try ...