performance MySql versus ms access 1

[matrixindicator]

I run often large queries in ms access. In short this is a selection on a large sourcefile (1.500.000 records). On this selection he does a lot of enrichtment manipulations with functions (eg. make the legal form of an enterprise to a standard = ltd or limited company should become LTD).
This is a time consuming action and I wonder If I should use mysql database (online or local) can this speed up the operations. In other words, in something known, can somthing be said about the MySQL performance ?

 

[Kozusnik]

If indexed properly, MySQL is one of the fastest database engines. I have databases with several million records which are queries and pull results in fractions of a second. It should definitely outperform Access.

Also, if you run it on Linux, you can use mysqlhotcopy to backup the databases while they're in use. mysqldump works on Windows or Linux (during uptime), but takes much longer and I've had trouble with blob fields.

Mark

 

[DonQuichote]

If indexed and used properly, MS-Access (better said, the Jet engine) is one of the fastest database engines.

The main problem with Access is that it is very rarely used properly. If you use the Jet engine with table-type recordsets and seek with the seek method (and not with findfirst), you will get an unbeatable speed. Especially if the database is on your own machine. Why? The object model used is a very thin layer above the actual file system. So every slow action is just not present:
- SQL parsing & optimizing,
- Network traffic.

Off course, MS Access has other limitations. Mainly in size and concurrent users. But if you talk SQL to an MS-Access database, you are actually emulating a server. Now THAT is slow.

Off course, the comparison is not quite fair. MS-Access is a filesystem database, while MySQL is a server database. These types are totally different and require totally different programming to make effective use of them. You can make both crawl with sloppy programming, and make both rock with good programming.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[matrixindicator]

DonQuichote,

If this is true why are almost all the people, customers complaining about the performance of acces. I have seen a large number of ms access databases developed by really experienced, skilled and skooled programmers and after a while, (when database grow) all of these complained about low performance, e.g. when using large queries. MS Access is developed as a desktop database, it has incredible possibilities but also his performance limits.

 

[matrixindicator]

Mark,

Thanks for your valuable answer. I have studied a book about MySQL, but not experienced. One question more. In MySQL can I build functions on my own to use them in my query (sql statement) ?

Function EmployeesGroup(employee As Variant) As String
        Select Case employee
            Case 0
                EmployeesGroup = "00-00"
            Case 1 To 4
                EmployeesGroup = "01-04"
            Case Is > 49
               EmployeesGroup = "50+"
        End Select
End Function

 

[johnwm]

Try

http://dev.mysql.com/doc/refman/5.1/en/stored-procedures.html

which gives details on stored functions and stored procedures

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints