I was kind of unimpressed with the performance of TSqlQuery compared to c.

I wrote two programs that built the same identical table. A random set of names with addresses. I found the user time of the program written in c was 20% of that which was written in Free Pascal.

Is there any chance of someone working on this library to improve performance?

Terry A. Haimann wrote

I wrote two programs that built the same identical table. A random set of names with addresses. I found the user time of the program written in c was 20% of that which was written in Free Pascal.

How did you generate the random names? FPC uses the Mersenne twister as random generator, which is quite good but also quite cpu intensive. Most C standard libraries uses a linear congruential generator, which is very fast but less good.

I.o.w., you may have been comparing the performance of the random number generators rather than of the SQL interface.

Jonas

Terry A. Haimann wrote

I was kind of unimpressed with the performance of TSqlQuery compared to c.

To build a table really fast, try Postgres COPY FROM STDIN / TO STDOUT BINARY <http://www.postgresql.org/docs/9.2/static/sql-copy.html> with Postgres libpq <http://www.postgresql.org/docs/9.2/static/libpq.html>. And I have experienced Postgres performning (much) better under heavy loads than MySQL.

Regards,

Adriaan van Os

Terry A. Haimann wrote

I was kind of unimpressed with the performance of TSqlQuery compared to c.

That is logical.

Beside the point raised by Jonas,
if you use a library which is designed to be general and cross-db-engine, it will always be somewhat slower than code which is written specifically for one engine. You will most likely be using wholly different techniques.

I wrote two programs that built the same identical table. A random set of names with addresses. I found the user time of the program written in c was 20% of that which was written in Free Pascal.

Without details of the program, it is difficult to say what causes this. The use of Random may be one cause. Transaction management may be another. Who knows what else ?

Is there any chance of someone working on this library to improve performance?

I doubt there is a lot you can do to improve performance, but you are welcome to try.

Specifically for MySQL, there is only one thing left to do: use parameter support for mysql version>=5.0, which should significantly improve performance. Currently (since the same code is used for versions of mysql <5.0) the parameters are substituted in code in the SQL.

And, as suggested by Adriaan, try to use Postgres or Firebird, which are by far better databases.

Michael.

In our previous episode, Terry A. Haimann said:

Do not measure user time, measure the time within the program. C and Pascal have differing library architectures, so that might obscure results.

The TSqlQuery is also fully runtime, the returned dataset is a runtime typed concept. Did you setup your C code equivalent, e.g. by prompting for the sql text?

Otherwise you are benchmarking runtime vs compiletime performance. Trying to use the mysql api directly with compiletime typed result set parsing might be faster from Pascal too.