From: Peter Carter
Date: November 19 1999 9:44pm
Subject: Re: Performance is good doing a INSERT, but is bad doing 
  SELECTs ( Queries )  !!!
List-Archive: http://lists.mysql.com/win32/976
Message-Id: <3.0.6.32.19991119164435.007efe20@sun.fw.pbc.ottawa.on.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

The BIG reason is you are comparing transactional database servers to
mysql, which is not. This reduces complexity, and thus cpu cost.

So.... simple selects and other simple queries will always run faster.
Complex queries tend to be faster on the bigger boys, because this is how
they have been optimized. Memory footprint is much smaller on mysql, so
odds are with the same machine / same memory MySQL should do a better job.

some other points to consider.....

MySQL does not seem to have a table (within reason) limit when run on an OS
that does not have a bad file size limit (Solaris 6/7 and somewhat NT).
MSSql has a limit of about 500meg. Oracle does not, but it chunks the
database into 2 gig files which adds to background processing.

If your connects are short term, SQLServer and Oracle are real pigs. MySQL
is much tighter on connection latency.

The client overhead on Oracle is just plain stupid, I never understood
where their brains were headed on this one. Add an extra 5 meg of disk
space per client, on top of the normal ODBC managers.

In my shop, we run Oracle, MS SqlServer and MySQL. Overall, MySQL is the
real performer and that is where all of our new databases go.

Cost of ownership..... hmm..... do the math on that one. MySQL is not going
to cost your organization $10,000+ just for clients............ more like
$200-$300 per server and $0 for clients.

In short, if you do not need transactional processing, pay Monty not Bill.
You will be much happier. If you need scale ability, throw more hardware at
it.

At 08:51 AM 11/19/99 -0000, you wrote:
>Hi ,
>I am making some test for decide wich Data Base we will include in our
next proyect. I wrote a program that INSERT/SELECT data into a data base,
in a  "similar" way the final users will be doing that.
>
>Here is the scenario:
> 
>    (A) PC Client : Windows 98 , 64 MBytes RAM
>    (B) PC Server : Windows NT server, 64MBytes  in RAM
>
>Here are the results
>
>CASE 1 : In a Client/Server Mode ( PC "A" vs PC "B" )
>
>INSERT 10,000 records in all tables of the data base
> 
>MySQL                57.67 minutes aprox
>InterBase 5.5        4 hours and 43 minutes aprox 
>MS SQL 7.0         13 hours and 5 minutes aprox
>
>SELECT all records in the tables , with a  relation Master -> Detail ( for
example Customers -> Orders )
> 
>MySQL            38 hours aprox
>InterBase          14 hours aprox  
>MS SQL 7.0     10.19 hours aprox
>
>CASE 2:  In Local mode : Runing the Client /Server in the same machine ,
using the Windows 98 PC ( A )
>
>INSERT 10,000 records in all tables of the data base
> 
>MySQL                31.1 minutes
>InterBase 5.5        45.58 minutes 
>MS SQL 7.0         23.31 minutes 
>
>SELECT all records in the tables , with a relation Master -> Detail ( for
example Customers  -> Orders )
> 
>MySQL               361.02 minutes aprox
>InterBase            129.21 minutes
>MS SQL 7.0        33.95 minutes
>
>NOTE: The databases are defined in the same way and they have the same
index files.
>
>Does any body know why MySQL seems to have better performance on INSERT
operations than SELECT ? exist any special parameters for configure MySQL
and improve the performance on SELECT ?
>
>I am using MySQL trial version, and just selecting "Allow Big results" on
the Data Source definition for my database. Also I am developing with
Delphi 4.0 and accesing  the Data bases with the Borland Database Engine (
BDE ).
>
>Thanks again for your help on this matter.
>
>
>Aquiles.
>
>
>Leonidas  Aquiles Mejia Garcia
>Desarrollo de Software
>Computacion en Accion
>e-mail: aquiles.mejia@stripped
>Telefono: (3) 818-09-00  Ext 3321
>URL: www.compac.com.mx
>
---
Peter B. Carter (peterc@stripped)
Pager: 613-751-4660
http://www.pbc.ottawa.on.ca