FreePascal and MySQL

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

FreePascal and MySQL

Terry A. Haimann-2
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?

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: FreePascal and MySQL

Jonas Maebe-2
On 01/05/15 18:07, 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
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: FreePascal and MySQL

Adriaan van Os-2
In reply to this post by Terry A. Haimann-2
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
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: FreePascal and MySQL

Michael Van Canneyt
In reply to this post by Terry A. Haimann-2


On Fri, 1 May 2015, 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.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: FreePascal and MySQL

Marco van de Voort
In reply to this post by Terry A. Haimann-2
In our previous episode, Terry A. Haimann said:
> 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.

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.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal