1

Is there a difference between BindValue and QString.arg() database wise?

QString().arg():

QSqlQuery qry;
qry.prepare(QString("INSERT INTO employee (id, name, salary) VALUES (%1, 'Thad Beaumont', %2)").arg(1001).arg(65000));
qry.exec();

QSqlQuery::bindValue:

QSqlQuery query;
query.prepare("INSERT INTO employee (id, name, salary) VALUES (:id, :name,:salary)");
query.bindValue(":id", 1001);
query.bindValue(":name", "Thad Beaumont");
query.bindValue(":salary", 65000);
query.exec();

Is there any performance difference between them? Is BindValues preferred over the other method?

user4157124
  • 2,809
  • 13
  • 27
  • 42
Petar Petrov
  • 702
  • 5
  • 14
  • 1
    Why couldn't you answer on this question yourself? The best way is just measure the times of execution for two approaches and compare them. – vahancho Jan 29 '18 at 11:14
  • But im asking database wise. Also the OS can interfere with the measurements. – Petar Petrov Jan 29 '18 at 11:17

3 Answers3

2

I think you and your colleagues are getting the prepared statements wrong. For example, one of the best known aims of using them, is (trying) to avoid SQL injection, which is why you should always use parameters and bind them, no matter the performance issue. There is no point in using arg, in this context: it is not a replacement of the argument binding, you're just building a SQL string. You can use it if you are 100% sure the parameters are well filtered/escaped/checked, and execute it with exec, no reason to use a prepared statement.

p-a-o-l-o
  • 9,807
  • 2
  • 22
  • 35
2

According to Wikipedia, there are two advantages of using prepared statements, namely performance and resilience against SQL injection attacks.

There are many posts that explain things in detail about specific SQL engines (MySQL, PostgreSQL) and since your question is tagged SQLite, there is a detailed SO question about prepared statements' performance in SQLite. All of these posts seem to agree that prepared statements are more efficient than executing statements manually (in addition to their security advantage).

In addition to the database being more efficient, your code is more effective. That is, when using prepared statements, you don't have to pay for string operations that substitute the value in your query string, instead, all strings are transferred to the database as they are without joining, and the database takes care of executing them.

Mike
  • 8,055
  • 1
  • 30
  • 44
0

QSqlQuery::bindValue() is better.

Since QString doesn't record the positions of %x,

QString("INSERT INTO employee (id, name, salary) VALUES (%1, 'Thad Beaumont', %2)").arg(1001).arg(65000)

, it has to calculate where to put the args every time when you call this.

On the other hand, it records the positions when calls QSqlQuery::prepare()

Furthermore, QSqlQuery::bindValue() is great for me that I'm not worried about ' anymore.

query.bindValue(":name", "Thad's Beaumont")

Now, you can safely store a name with '. (Before that, I sometime replace it with '', sometime for performance, I replace it with some rarely used character like 0x000c).

Zhang
  • 3,030
  • 2
  • 14
  • 31