Large table performance problem with MYSQL performance with ASP.NET

Large table performance problem with MYSQL performance with ASP.NET

I am doing some benchmarking of an application using MYSQL under
Windows and I confused about the results.

Environment
Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram

mysql 4.1 with odbc 3.51 MYISAM
windows 2003 server std edition
date, account and invoice number are indexed
Database size 18 million rows

I am querying (selecting) columns of a date and an account

our tester program that opens a socket to the Mysql database and does a
select for the above n times
each time the date and the account is randomized to minimize hits on
records closeby.
This program will perform over 1000 queries per second.
At the end, the Mysql socket will be closed

When I enter a similar query manually a web interface, I get about 3
second response time.
This program opens/closes a socket for each query

Does anyone have any suggestions

Your assistance would be greatly appreciated

Also in production, this table will be accesse for both read and write
will I have problems. My testing showed that Innodb is much slower.

Murray
murray@jbmsystems.com
978-535-7676

Re: Large table performance problem with MYSQL performance with ASP.NET

wrote in message
news:1139328706.999981.126520@z14g2000cwz.googlegroups.com.. .
> our tester program that opens a socket to the Mysql database and does a
> select for the above n times
> each time the date and the account is randomized to minimize hits on
> records closeby.
> This program will perform over 1000 queries per second.
> At the end, the Mysql socket will be closed
>
> When I enter a similar query manually a web interface, I get about 3
> second response time.
> This program opens/closes a socket for each query

The work required to open a socket and authenticate a connection to the
MySQL server has some nonzero cost. You should compare the web test against
your testing tool if the testing tool re-establishes its socket and MySQL
login for each query.

Another factor is the additional work to present results in HTML. In one
web application I worked on (mine was in Perl with PageKit MVC framework), I
found that 80% of the processing work was spent in parsing the HTML
templates and formatting the output. The database access code accounted for
only a small portion of the cost of rendering a web page. It's best to use
a profiler tool to get an accurate report of where your code is spending its
time. It might not be in the data access code at all.

I don't know much about developing using ASP.NET, but a search finds this
page with a list of performance measurement tools for .NET, including a
couple of code profilers:
http://www.411asp.net/home/webapps/performa

> Also in production, this table will be accesse for both read and write
> will I have problems. My testing showed that Innodb is much slower.

Yes, InnoDB is a lot slower than MyISAM in many cases. But it offers
additional features (basically, transactional support and referential
integrity constraints are the chief ones). If you don't need those
features, then don't use InnoDB.

Regards,
Bill K.