PDA

View Full Version : Quick Bindvalue Question



tpf80
5th February 2009, 09:47
I have a query formulated as follows:


insert into table (col1, col2, col3) values ("uniquething", :item1, :item2),("uniquething2", :item1, :item2),("uniquething3", :item1, :item2)


I use bindvalue as follows



query.bindValue(":item1", "something");
query.bindValue(":item2", "something2");


When the code executes the query, the result in the database is:



col1 | col2 | col3
-----------------------------------------------------
uniquething | null | null
uniquething2 | null | null
uniquething3 | something | something2


so it seems that if there are multiple of the same placeholder, bindValue only replaces 1 of them, and leaves the others as null. Is this a bug or is it by design? and is there a way to bind a value to multiple placeholders, other than the obvious of naming all of them uniquely?

Lykurg
5th February 2009, 19:29
Hi, I don't know if it is a bug or a design thing but I would do it this way:

QSqlQuery query;
query.prepare("insert into table (col1, col2, col3) values (:uniquething, :item1, :item2)");
query.bindValue(":item1", "something");
query.bindValue(":item2", "something2");

query.bindValue(":uniquething", "uniquething");
query.exec();

query.bindValue(":uniquething", "uniquething2");
query.exec();

query.bindValue(":uniquething", "uniquething3");
query.exec();


Lykurg

tpf80
5th February 2009, 19:36
Yeah, I could do it that way, but each exec() call causes another query to happen, which means less performance. For example, if I perform it this way:


insert into table (col1, col2, col3) values ("uniquething", :item1, :item2),("uniquething2", :item1, :item2),("uniquething3", :item1, :item2)

then it is sent to the database one time, and executed all in one shot.

If I do it the other way, then it will be sent as 3 seperate queries, which on a high latency connection or high volume server will cause more overhead.

Lykurg
5th February 2009, 19:42
If I do it the other way, then it will be sent as 3 seperate queries, which on a high latency connection or high volume server will cause more overhead.

That's right. If your database can handle transactions use this and the performance should be almost equal.

A look to the sources about the original problem leaves me unwise :crying: (Too many d->)

tpf80
5th February 2009, 21:37
Well, here is how I ended up working around it, although it still is strange that bindValue doesn't replace all of the same placeholders with the same thing if you reference them by name.




querystring = "insert into table (col1, col2, col3) values ";
for (int i = 0; i < uniquevaluelist.size(); ++i) {

querystring += "(:item1, :item2, :item3)";

if (i == uniquevaluelist.size() - 1) {

} else {
querystring += ", ";
}
}
query.prepare(querystring);

int j = 0;
for (int i = 0; i < uniquevaluelist.size(); ++i) {
j = i * 3;
query.bindValue((j + 0), uniquevaluelist.at(i));
query.bindValue((j + 1), "some not unique value");
query.bindValue((j + 2), "some other not unique value");

}

query.exec();

Lykurg
6th February 2009, 19:51
Hi,

some thougths on your code:

:item2 and :item3 are constant, so is it possible to add the values to default values on your Sql database? Then you could leave them blank.
as an optimization of your code (first loop):
// not checked: uniquevaluelist.size() != 0
querystring = "insert into table (col1, col2, col3) values (:item1, :item2, :item3)";
for (int i = 1; i < uniquevaluelist.size(); ++i)
querystring += ", (:item1, :item2, :item3)";


Lykurg

tpf80
6th February 2009, 20:25
They are constant on a "per batch" basis, but not on a "per query" basis. ( and in the real code it does check the size of the list to make sure its not 0 ).

The reason for the performance concern is that I may want to enable remote access to the database through SSH. My testing so far has shown this to not be a good method to access the database.

For example, each time a query happens, due to latency in the connection, it could take around 1-2 seconds for the result to come back, regardless of the size of the result. For example, 7 queries might take 14 seconds for a result to come back even with a minimal amount of data in them, but a single query, even with a result 100 times bigger than the 7 would come back in only 1 second.

So, in essence, I need to do 2 things:

First, I need to make sure that I get the most bang for each query possible, to reduce the effect of latency.

Secondly, I need to figure out if there is a better way to connect, to where the latency issue wouldn't be as much of a problem, perhaps caching data and using a worker thread to do the database work, or making some kind of "middleman" server between the database and the client app.

Lykurg
6th February 2009, 22:09
Ha,

have a look at this Task Tracker (http://www.qtsoftware.com/developer/task-tracker/index_html?method=entry&id=77898). There was your Problem, but the suggestion was rejected, why ever. Unfortunately there is no description for the reason...


Lykurg