Professional Web Applications Themes

Perfomance Research CF MX7 and SQL - Coldfusion Database Access

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 ...

  1. #1

    Default Perfomance Research CF MX7 and SQL

    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?




    BSW2 Guest

  2. #2

    Default Re: Perfomance Research CF MX7 and SQL

    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.



    Abinidi Guest

Similar Threads

  1. Help with CF_MODULE Recusive Query Perfomance
    By DonO in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: April 22nd, 08:18 PM
  2. List Perfomance question
    By Swd1974 in forum Macromedia ColdFusion
    Replies: 3
    Last Post: March 15th, 04:03 AM
  3. Very low perfomance with numerous objects
    By Armadillo in forum Macromedia Freehand
    Replies: 5
    Last Post: September 26th, 12:03 PM
  4. perl LWP UserAgent resume perfomance
    By Brian Harnish in forum PERL Miscellaneous
    Replies: 0
    Last Post: August 22nd, 09:04 PM
  5. Perfomance access windows Pro x Server
    By Frank Dulk in forum Microsoft Access
    Replies: 0
    Last Post: July 28th, 06:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139