Does transferring Access to MySql improve performance?

Is it worth to do?

[iloveu]

I have a middle size site (around 5000 pages), 95% of pages are database driven. I use ASP and MS Access. The site gets down frequently due to database "System Resource Exceeded". I think the search engine crawls my site and grabs too many pages, so the database connection crashes. I check all my pages to avoid any memory leak, but it does not help. It is said ASP has bug to open its connection to Access and it is better to use MySql. What do you think of it? Is it worth to that? I am not familiar with MySql, how should I do that? Go to MySql website to download a program, create and populate the database and upload it to my site? How about query? Is it same as the query to Access? Need your suggestions, thank.

[Easy_Coder]

access is limited to 255 connections so yeah you'd be better off upgrading.

[iloveu]

Thanks for reply, can you give me some details (detailed steps or online instructions) to implement a MySql database.

[dataguy]

My experience is that moving from SQL Server to MySQL can improve performance, so I would say that yes, definately moving from Access to MySQL would improve performance.

There's not enough room here to cover the conversion, you'll have to find that elsewhere, though there's plenty of tutorials available if you look.

You may want to start by searching for "MyODBC" and "MySQL windows server examples".

[Easy_Coder]

My experience is that moving from SQL Server to MySQL

Do you have any benchmarks?

The Transaction Processing Performance Council has Oralce, IBM, Microsoft and Sybase listed as the top performing databases in speed and performance for price. MySQL is not on the list because they're not submitting to independent testing.

The TPC council benchmarks database performance every day.

[dataguy]

Do you have any benchmarks?

Nothing official, but in my specific application the difference is obvious, the execute time is faster by several orders of magnitude.

I'm in the middle of transitioning from MS SQL Server to MySQL on several of my sites. For some sites I'm running parallel databases and the MySQL statements in some instances execute in one tenth of the time as the exact same SQL statements on MS SQL. Obviously this won't occur in all circumstances, but at least for me it's been worth months of rewriting code and maintaining duplicate data on 2 database servers, for the performance benefits.

[mrMister]

dataguy, what platform are you using (ASP or ASP.Net)?

Are you using ODBC or OLEDB?

Are you just inserting plain text Ad-hoc SQL statements or are you using paramatized queries?

Do you notice the same performance differences when using stored procedures?

[carguy84]

I was going to ask the same question mrMister asked last. Are you using stored procedures with MSSQL? It doesn't sound like it based off your results.

MySQL still doesn't support SP's does it? I thought I remembered there being a Beta that does, but haven't heard much about it or it's performance.

Chip-

[mrMister]

One thing I have heard a few times is that SQL Server is particularly slow at full text searches.

I don't believe the TPC-C tests account for this, and a lot of websites (rightly or wrongly) use full text searching.

However I think it's fair to say that SQL Server 2000 is generally regarded as the the faster database.

In fact up until now, the only argument I've heard for using mySQL is that it's cheaper to buy or it's what the user's cheapo web host has installed.

I've never heard on anyone running it in a live environment on a Windows platform though.

[Easy_Coder]

eesh, I dunno. I can't find any independant studies that show a mysql box blowing the doors of a mssql box.

[coopster]

Here are a couple of independent studies from ZD and SPEC (Note: not defending MySQL here, merely offering up some independent studies as requested):

[mysql.com...]

MySQL stored procedures are in version 5.0 which is still a development release at this point in time.

[Easy_Coder]

Thanks coopster, me neither I just want to see some testing. All the stuff that I found was done by either MS or MySql so that's not really independent. I wonder why there hasn't been much?

Too, I'd really be interested in seeing same size db's, same load but rather on the clustered environment and not the single box set up. I noticed on the mySql website they they can cluster their solutions.

But to the orginal question. For performance reasons you would be much better off using MySQL over Access. Access while it can be used for a web site isn't really designed for that. It's more of a desktop application and it's not going to scale for you.

[coopster]

I agree. I remember another member asking about it once, too. Almost the same question, actually, MySQL/PHP or MS Access [webmasterworld.com] -- except we are in the MS Forum here. I'm surprised iloveu hasn't considered MSSQL as an option ...? Or is the *MySQL* simply a typographical error here?

[txbakers]

MSSQL and mySQL will both work for you.

Access is not for web development.

MSSQL costs more, but does more.

mySQL is open source but does less.

I use mySQL for now. As I grow I will most likely switch to MSSQL.

[Easy_Coder]

iloveu, so whats up? No mssql?

[iloveu]

Thanks for you guys' suggestions. I decide to use MS SQL to replace Access. Do you have any online recources related with MS SQL database data types, database design and MS SQL queries (same as ones for Access or not)? I searched with google, but could not find good one, I think you have to pay money for the proprietary information.

[carguy84]

mrMister, did you mean full text indexing?

[txbakers]

queries in MSSQL are different than Access. The wildcard is % instead of *. Date handling is different also.

[Easy_Coder]

iloveu, get familier with Books Online. It will be installed with SQL Server. It's a great sql server resource.

You can download it from microsoft:
[microsoft.com...]

Also, look up SQLServerCentral.