Professional Web Applications Themes

Our test of MySQL vs. MS SQL Server - MySQL

Someone here tested MS SQL Server vs. MySQL. Both stock install versions, no tweaking. The result of tests, roughly, is that MySQL is about 2-3x faster than MS SQL Server, when doing selects or inserts or updates. So, we'll be going with MySQL. It is also to not pay money to anyone, and to get a full version and not some ized "evaluation" server. i...

  1. #1

    Default Our test of MySQL vs. MS SQL Server

    Someone here tested MS SQL Server vs. MySQL. Both stock install
    versions, no tweaking.

    The result of tests, roughly, is that MySQL is about 2-3x faster than
    MS SQL Server, when doing selects or inserts or updates.

    So, we'll be going with MySQL. It is also to not pay money to anyone,
    and to get a full version and not some ized "evaluation"
    server.

    i
    Ignoramus7272 Guest

  2. #2

    Default Re: Our test of MySQL vs. MS SQL Server

    Out of interest , what amount of data did you use? also, how did you measure
    the speed on the mySQL?
    I'm interested in this process, as I'm just about to start evaluating .


    ----
    Jack Vamvas

    "Ignoramus7272" <7272.invalid> wrote in message
    news:alt.net... 


    Jack Guest

  3. #3

    Default Re: Our test of MySQL vs. MS SQL Server

    > Someone here tested MS SQL Server vs. MySQL. Both stock install 

    Brilliant. What table type?

    What version of MySQL?

    What version of MS SQL Server?

    How much data?

    What kind of indices, if at all, did you use?

    How many concurrent inserts did you try?

    How many concurrent selects did you try?

    In short: what kind of test did you actually run in order to make
    such a statement?
     


    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
    MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  4. #4

    Default Re: Our test of MySQL vs. MS SQL Server

    On Fri, 06 Oct 2006 16:17:27 +0000, Ignoramus7272 wrote:
     

    Version 4 or 5 of MySQL? You are sure you don't need transactions?

    I've seen way to many programmers not thinking about database concurrency
    resulting in corrupted databases. If MySQL cuts it (it does for most of my
    applications), fine. However, if you need a true database, the tables turn
    (pun intended).

    HTH,
    M4
    --
    Redundancy is a great way to introduce more single points of failure.

    Martijn Guest

  5. #5

    Default Re: Our test of MySQL vs. MS SQL Server

    Martijn Lievaart wrote: 
    >
    >
    > Version 4 or 5 of MySQL? You are sure you don't need transactions?
    >
    > I've seen way to many programmers not thinking about database concurrency
    > resulting in corrupted databases. If MySQL cuts it (it does for most of my
    > applications), fine. However, if you need a true database, the tables turn
    > (pun intended).
    >
    > HTH,
    > M4[/ref]

    MySQL can do transactions just fine if you use innodb tables. No
    problem at all.

    Of course MSSQL has other features, but MySQL is rapidly catching up.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  6. #6

    Default Re: Our test of MySQL vs. MS SQL Server

    On Fri, 6 Oct 2006 17:27:01 +0100, Jack Vamvas <com> wrote: 

    a few tens/hundreds of thousands of records, a few thousands of
    selects with all diferent keys, that's all I know. I am aware that
    people can argue forever about what constitutes the most refined test,
    and that perhaps ours was not that.

    i
    Ignoramus24560 Guest

  7. #7

    Default Re: Our test of MySQL vs. MS SQL Server

    On Fri, 6 Oct 2006 19:20:54 +0200, Martijn Tonies <removethis.com> wrote: 
    >
    > Brilliant. What table type?[/ref]

    MyISAM
     

    MySQL 5
     

    MS SQL Express '05, if I have that right.
     

    About 100k records in various tables (about wehat we need)
     

    Whatever was appropriate for the selects
     

     

    no idea, we do not do concurrent statements in practice.
     

    A bunch of selects and inserts, similar to what we need.

    i
     
    >
    >[/ref]
    Ignoramus24560 Guest

  8. #8

    Default Re: Our test of MySQL vs. MS SQL Server

    On Sat, 07 Oct 2006 21:02:30 +0200, Martijn Lievaart <this.part.rtij.nl> wrote: 
    >
    > Version 4 or 5 of MySQL? You are sure you don't need transactions?[/ref]

    5.
     

    Well, though we do not do any concurrent statements due to our system
    design, I thought tat MySQL 5 covers concurrency just fine, no?

    i
    Ignoramus24560 Guest

  9. #9

    Default Re: Our test of MySQL vs. MS SQL Server

    > >> Someone here tested MS SQL Server vs. MySQL. Both stock install 
    > >
    > > Brilliant. What table type?[/ref]
    >
    > MyISAM[/ref]

    So you're not using transactions then?

    I hope it's not a so-called mission-critical application.
     
    >
    > MySQL 5

    >
    > MS SQL Express '05, if I have that right.

    >
    > About 100k records in various tables (about wehat we need)

    >
    > Whatever was appropriate for the selects

    >

    >
    > no idea, we do not do concurrent statements in practice.

    >
    > A bunch of selects and inserts, similar to what we need.[/ref]

    All sounds very thorough.


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  10. #10

    Default Re: Our test of MySQL vs. MS SQL Server

    On Wed, 11 Oct 2006 08:38:39 +0200, Martijn Tonies <removethis.com> wrote: 
    >>
    >> MyISAM[/ref]
    >
    > So you're not using transactions then?[/ref]

    Exactly!
     

    Depends on how you look at it...

    i
     
    >>
    >> MySQL 5
    >> 
    >>
    >> MS SQL Express '05, if I have that right.
    >> 
    >>
    >> About 100k records in various tables (about wehat we need)
    >> 
    >>
    >> Whatever was appropriate for the selects
    >> 
    >>
    >> 
    >>
    >> no idea, we do not do concurrent statements in practice.
    >> 
    >>
    >> A bunch of selects and inserts, similar to what we need.[/ref]
    >
    > All sounds very thorough.
    >
    >[/ref]
    Ignoramus30966 Guest

  11. #11

    Default Re: Our test of MySQL vs. MS SQL Server

    On Wed, 11 Oct 2006 08:38:39 +0200, Martijn Tonies wrote: 
    >>
    >> MyISAM[/ref]
    >
    > So you're not using transactions then?
    >
    > I hope it's not a so-called mission-critical application.[/ref]

    Not all mission-critical applications require transactions. Some
    criticality is for lookups against essentially static data, for example,
    that can be updated during maintenance windows.

    --
    17. When I employ people as advisors, I will occasionally listen to their
    advice.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  12. #12

    Default Re: Our test of MySQL vs. MS SQL Server

     
    > >
    > > So you're not using transactions then?
    > >
    > > I hope it's not a so-called mission-critical application.[/ref]
    >
    > Not all mission-critical applications require transactions. Some
    > criticality is for lookups against essentially static data, for example,
    > that can be updated during maintenance windows.[/ref]

    Right, applications that only "read" data, wouldn't need transactions.
    That is, if the data isn't being updated at the same time.

    However, any data update requires transactions. Period.


    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
    MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  13. #13

    Default Re: Our test of MySQL vs. MS SQL Server

    Martijn Tonies wrote: 
    >>
    >>Not all mission-critical applications require transactions. Some
    >>criticality is for lookups against essentially static data, for example,
    >>that can be updated during maintenance windows.[/ref]
    >
    >
    > Right, applications that only "read" data, wouldn't need transactions.
    > That is, if the data isn't being updated at the same time.
    >
    > However, any data update requires transactions. Period.
    >
    >[/ref]

    Incorrect. A single insert or update to a table does not require a
    transaction.

    Multiple updates, inserts/updates to multiple tables and
    read/wait/update almost always require transactions for safety, though.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  14. #14

    Default Re: Our test of MySQL vs. MS SQL Server

     
    > >
    > >
    > > Right, applications that only "read" data, wouldn't need transactions.
    > > That is, if the data isn't being updated at the same time.
    > >
    > > However, any data update requires transactions. Period.
    > >
    > >[/ref]
    >
    > Incorrect. A single insert or update to a table does not require a
    > transaction.[/ref]

    I figured someone would say that.
     



    I'll take transactions and - perhaps - a slight performance decrease
    every day above no transactions.


    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
    MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  15. #15

    Default Re: Our test of MySQL vs. MS SQL Server

    Martijn Tonies wrote: 
    >>
    >>Incorrect. A single insert or update to a table does not require a
    >>transaction.[/ref]
    >
    >
    > I figured someone would say that.
    >

    >
    >
    >
    >
    > I'll take transactions and - perhaps - a slight performance decrease
    > every day above no transactions.
    >
    >[/ref]

    I'd rather use transactions when necessary, and not use them when they
    are not required.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  16. #16

    Default Re: Our test of MySQL vs. MS SQL Server

    On Thu, 12 Oct 2006 09:15:38 -0400, Jerry Stuckle <net> wrote: 
    >>
    >>
    >> I figured someone would say that.
    >>
    >> 
    >>
    >>
    >>
    >>
    >> I'll take transactions and - perhaps - a slight performance decrease
    >> every day above no transactions.
    >>
    >>[/ref]
    >
    > I'd rather use transactions when necessary, and not use them when they
    > are not required.
    >[/ref]

    That's my attitude as well.

    i
    Ignoramus534 Guest

  17. #17

    Default Re: Our test of MySQL vs. MS SQL Server

     [/ref][/ref]
    example, 
    > >
    > > I'd rather use transactions when necessary, and not use them when they
    > > are not required.
    > >[/ref]
    >
    > That's my attitude as well.[/ref]

    Ah, so you know you code so well, that you can actually say, " use a
    transaction
    now, but not now " even after years and after someone else has taken it
    over?

    Sounds hairy. If you use em everywhere, you - and others after you - will
    never
    make these mistakes.



    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
    MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

  18. #18

    Default Re: Our test of MySQL vs. MS SQL Server

    Martijn Tonies wrote: [/ref]
    >
    > example,

    >>
    >>That's my attitude as well.[/ref]
    >
    >
    > Ah, so you know you code so well, that you can actually say, " use a
    > transaction
    > now, but not now " even after years and after someone else has taken it
    > over?
    >
    > Sounds hairy. If you use em everywhere, you - and others after you - will
    > never
    > make these mistakes.
    >
    >
    >[/ref]

    Yes, I've been doing SQL for over 20 years. I do know when a
    transaction is required and when one is not. So does every competent
    SQL programmer I know. And it's part of the SQL classes I teach, so
    even beginning SQL programmers are aware of when they need transactions
    and when they don't (although, like all new programmers, they do make
    mistakes). And the rules are not complex.

    And commenting the code properly means others will know what and why, also.

    Using transactions unnecessarily places an additional load on both the
    application computer and the db server (whether they are the same or
    different machines). And as database workload gets heavier, so does the
    unnecessary load.

    The result is a higher COS, because you need faster computers for the DB
    servers, larger disks for the transaction logs, faster connections for
    the server, etc.

    Now, this might not matter to you much if you're a casual web programmer
    who does systems which average 1-2 requests a minute. But it does
    matter a lot when you're working on systems which get 100K requests a
    second.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  19. #19

    Default Re: Our test of MySQL vs. MS SQL Server

    On Fri, 13 Oct 2006 08:04:50 +0200, Martijn Tonies <removethis.com> wrote: [/ref]
    > example, 
    >>
    >> That's my attitude as well.[/ref]
    >
    > Ah, so you know you code so well, that you can actually say, " use a
    > transaction
    > now, but not now " even after years and after someone else has taken it
    > over?
    >
    > Sounds hairy. If you use em everywhere, you - and others after you -
    > will never make these mistakes.[/ref]

    We have one app that is the database controller, there is no
    concurrent access.

    i
    Ignoramus3520 Guest

  20. #20

    Default Re: Our test of MySQL vs. MS SQL Server

     
    > >
    > >
    > > Ah, so you know you code so well, that you can actually say, " use a
    > > transaction
    > > now, but not now " even after years and after someone else has taken it
    > > over?
    > >
    > > Sounds hairy. If you use em everywhere, you - and others after you -[/ref][/ref]
    will 
    >
    > Yes, I've been doing SQL for over 20 years. I do know when a
    > transaction is required and when one is not. So does every competent
    > SQL programmer I know. And it's part of the SQL classes I teach, so
    > even beginning SQL programmers are aware of when they need transactions
    > and when they don't (although, like all new programmers, they do make
    > mistakes). And the rules are not complex.[/ref]

    I haven't tried it yet on MySQL and MyISAM tables, but what would happen
    if I create an "after insert/update/delete" trigger, and the trigger fails?

    Might not sound complex, but in environments where, for example, 3rd
    party apps create additional triggers (although MySQL allows for 1 trigger
    per action-type, I believe), this could become complex easily.
     
    also.

    Good luck.
     

    Starting transactions for single actions wouldn't make the load "a lot"
    heavier, as far as I can tell.
     

    Non issue, IMO. I bet your time - and testing your app - takes more $$
    than what transactions costs in performance.
     


    --
    Martijn Tonies
    Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
    MS SQL Server
    Upscene Productions
    http://www.upscene.com
    My thoughts:
    http://blog.upscene.com/martijn/
    Database development questions? Check the forum!
    http://www.databasedevelopmentforum.com


    Martijn Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 0
    Last Post: January 29th, 02:03 PM
  2. DBD::mysql test errors on Win32
    By Brian in forum PERL Modules
    Replies: 2
    Last Post: September 5th, 05:45 PM
  3. Replies: 1
    Last Post: January 27th, 02:49 AM
  4. The server response was: 550 5.7.1 <testtest.com>... we do not relay
    By S. Justin Gengo in forum ASP.NET General
    Replies: 0
    Last Post: August 6th, 03:15 PM
  5. unable to connect/test SQL or MySQL Connections
    By Robert Blackwell in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 27th, 06:41 AM

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