Re: [lazarus] best use of components for mysql query insert performance benchmarking

[Joost van der Sluis]

On Mon, 2007-01-22 at 14:51 -0700, Seth Grover wrote:
> I'm writing a program to do some informal benchmarking of insert and
> query performance using various MySQL storage engines using MySQL
> 5.1.x latest beta. I've got it up and running just fine using Lazarus
> and the MySQL components. It was a piece of cake.

Well. I'm not sure what you want to benchmark. If you're gonna compare
two servers, then it doesn't really matter how your code works. But if
you only want to show some fast numbers, you'd better don't use the
MySQL-components, since they only add an extra overhead-layer. 

If you want fast direct access, and filtering, iterating through
records, editing and such doesn't matter, you'de better use the MySQL-
api directly. (the unit mysqlXX, XX=40,41,50)  

How the api works can you find in the MySQL-documentation. They call it
the c-api.

Joost

_
 To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
   archives at http://www.lazarus.freepascal.org/mailarchives

RE: [lazarus] best use of components for mysql query insert performance benchmarking

[Pieter Valentijn]

I gess parameters would be a bit more OO.

So consturct your sql like this.

MySqlCom.sql.text := 'Insert into test(Name) values (:NAME)' ;

Then uses

MySqlCom.ParamByName('NAME').aswhatever = somewhatever ;

This way you only need to set the SQL 1 time.

Don't forget if you set params your Query does not close and open.
So you need to do this.

MySqlCom.close ;
MySqlCom.ParamByName('NAME').aswhatever = somewhatever ;
MySqlCom.open ;

I think this whay you also just pas params and not the full SQL so it
just needs to be parced 1 time by the SQL optimizer (if anny is used).

Met vriendelijke groet, 
Pieter Valentijn
 
Delphidreams
http://www.delphidreams.nl
 


-Oorspronkelijk bericht-
Van: Seth Grover [mailto:[EMAIL PROTECTED] 
Verzonden: maandag 22 januari 2007 22:51
Aan: lazarus@miraclec.com
Onderwerp: [lazarus] best use of components for mysql query insert
performance benchmarking


I'm writing a program to do some informal benchmarking of insert and
query performance using various MySQL storage engines using MySQL 5.1.x
latest beta. I've got it up and running just fine using Lazarus and the
MySQL components. It was a piece of cake.

My question is on what the best way is to do the inserts to get the
truest picture of insert performance. Right now I'm basically
constructing an insert statement string and using ExecSQL to do the
insert. Is there another way (perhaps one a little more
object-oriented) to do inserts other than just piecing together a huge
"Insert into table t1 (a, b, c, d,) values ..."-style statement? If not,
would I get better performance inserting multiple records with each
statement I execute (insert into t1 values (1), (2), (3), ...
etc.) or inserting one record per statement (insert into t1 values (1);
insert into t1 values (2); etc.?

Any tips you have would be appreciated. I just want to eliminate as much
as possible any performance hits due to the overhead of the tools I'm
using to do the benchmarking, thus getting a better picture of the
performance of the database itself.

Thanks,

Seth Grover

-- 
Seth Grover
sethdgrover[at]gmail[dot]com
http://grovers.us/seth

I'm a driver, I'm a winner. Things are going to change, I can feel it.

_
 To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
   archives at http://www.lazarus.freepascal.org/mailarchives

_
 To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
   archives at http://www.lazarus.freepascal.org/mailarchives