QSqlQuery bindValue slow
-
I have a query like this
@select Count(1) as Count, pt.Name as TypeName, pt.ID as TypeID, pc.ID as CatID,
o.Name as OffName, o.ID as OffID, pc.Color as Color, s.ID, s.ActionType,
s.EndTime, pt.Size, pt.Price, pt.Unit, pt.OffID as ProdOffID
from sess s
inner join off o on o.id = s.offid
inner join act a on a.sessid = s.id
inner join prod p on p.tagid = a.prodid
inner join ProdType pt on pt.id = p.prodtypeid and pt.offid = p.Offid
left join prodcat pc on pc.id = pt.prodcatid and pc.offid = pt.offid
where s.offid = ? and s.acttype in (?, ?)
Group By pt.Name, pt.ID, pc.ID, o.Name,
o.ID, pc.Color, s.ID, s.ActType,
s.EndTime, pt.Size, pt.Price, pt.Unit, pt.OffID@If i use this query in Qt like this, query takes lots of time (about 2 seconds)
@QSqlQuery newQuery(db);
newQuery.prepare(queryString);
for (int parameterIndex=0;parameterIndex<values.count();parameterIndex++) {
newQuery.bindValue(parameterIndex,values[parameterIndex]);
}
newQuery.exec();@But if i replace "?"s with values and if i don't use bindValue query takes about 50ms.
Is this normal?Note that these tables have btree indexes for their FK’s. Using Qt 4.7.4 compiled with VC2008SP1. Database is PostgreSQL.
-
Uhm...there could be a little difference, but should not be such the one you reported. You have to understand that given a completed query allows the optimizer to choose in advance the right/best plan, giving a prepared statement makes the optimizer to guess and to rebuild the plan once the values are known.
You can test the same prepared query from the console too, and see if the problem persist.
Anyway, take into account that testing the same query over and over again is a bad idea: the database will cache data and the execution of the same query will result faster.
Finally, you can enable verbose logging to see which plan the database choose and compare it against the complete query.