Perfomance Research CF MX7 and SQL

[BSW2]

I have been doing some testing with CF MX7, IIS and Microsoft SQL Express.
Everything is on my development Win XP PRO computer. 2G RAM installed and
3Ghz Pentium CPU.

The testing was done against a 160,000 record table. I used the following
query for the each SQL Express test. The MySQL query was very similar but a
LIMIT statement was used instead of TOP.

SELECT TOP 25 WELLNESS.Client, Count(Client) AS CountOfClient
FROM WELLNESS
GROUP BY Client
ORDER BY Count(Client) DESC;

My ?cold load time? is the time it took the query to run after the system was
rebooted. No cache of any kind plus all the other overhead, schema load,
server config, ?..

Test configuration 1:
MS ACCESS MDB file
Cold load time 5800ms redisplay from cache 1200ms

Test configuration 2:
MS SQL EXPRESS via ODBC driver
Cold load time 2850ms redisplay from cache 1600ms
The ODBC cache seems to flush after 20 seconds. Running the query within 20
seconds delivered results in 95ms every time.

Test configuration 3:
MS SQL EXPRESS via CF Microsoft SQL driver
Cold load time 2500ms redisplay from cache 150ms

Test configuration 4 (Query in stored procedure):
MS SQL EXPRESS via CF Microsoft SQL driver to a stored procedure
Cold load time 2500ms redisplay from cache 450ms

Test configuration 5:
MySQL V5 via CF MySQL(3.X) driver
Cold load time 1600ms redisplay from cache 250ms

All the timings were averages from the many tests I ran in each test
configuration. This like bit of research may be flawed in some ways but it
does seem to point to MySQL as an excellent database for use with CF. I knew
MDB files were going to be slow. Stored procedures do not seem to improve
performance with SQL EXPRESS. I was unable to get stored procedures to work on
MySQL. Using the CF SQL driver seems to be a better choice than ODBC.

Let me know what you think?

[Abinidi]

SQLExpress is a very limited DB platform. Mysql is a mid level DB platform as
well.

I would try, if you have resources, testing it againg MSSQL2000 or MSSQL2005,
and Oracle. These are real DB platforms and your times will go down
considerablely.