Quick Bindvalue Question

[tpf80]

I have a query formulated as follows:

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

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

To copy to clipboard, switch view to plain text mode 

I use bindvalue as follows

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

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

To copy to clipboard, switch view to plain text mode 

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

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

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

To copy to clipboard, switch view to plain text mode 

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]

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

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

To copy to clipboard, switch view to plain text mode 

Lykurg

[tpf80]

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)

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

To copy to clipboard, switch view to plain text mode 

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]

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 (Too many d->)

[tpf80]

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

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

To copy to clipboard, switch view to plain text mode 

[Lykurg]

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

  // 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)";

  To copy to clipboard, switch view to plain text mode 

Lykurg

[tpf80]

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]

Ha,

have a look at this Task Tracker. There was your Problem, but the suggestion was rejected, why ever. Unfortunately there is no description for the reason...


Lykurg