Forum Moderators: phranque
The site will connect buyers and sellers. Buyers would fill out a form detailing what they are looking for. Paying sellers would be about to log in and use this information for sales leads.
The maximum number of buyers filling out the form we would get would be 50 per day.
The maximum number of submissions would be in the hundreds per day.
The maximum number of users that would log in during a day 5000 per day.
These are all orders of magnitude more than we expect but better safe than sorry.
What we would like to know is…
Will access meet our needs? It is easier to work with (I have not used sqlServer) and it is cheaper to get a server with only access support.
I have had people tell me access will be fine into the 100s of thousands of records (far more than we will need). I have also heard that access cannot handle many users at once. Will this be a problem with the numbers mentioned above?
Any insight would be appreciated. As well as links to web resources dealing with this issue.
Thanks so much.
jmirick
If price is a problem (and the way MS does licensing for SQL recently got worse...) you might want to look into open sourced apps, such as MySQL, or PostgreSQL. You might wanna give them a look, even if price isn't a worry. :)
Onya
Woz
If you have many tables (and particularly if you use external data sources also) and successful operation of the site requires repeated query and update operations to push and pull data to/from the dbase, performance can drop off.
If you've just got a few, fairly simple tables, it might do you fine (at least until the table size becomes an issue)
You would need then to look at data retention policy. Do you need to keep everything for ever, or would a periodic purge of now "stale" data be an option? If so, try to estimate input rates vs purge rates to calculate rate of data growth. From that, and an estimate of data complexity, you could come up with a rough figure for the lifespan of Access, subject to validation by live operational experience
If you plan on more than 25 concurrent connections then go SQL instead of Access.
I agree with Woz though if starting small start with Access then migrate to SQL when the time, and budget, is right.
Brian
Couple of situations:
1. 20 concurent connections, one table, ~2000 transactions in 2-3 hours. Slow, but a captive audience and still usable.
2. unmonitored number of concurent connections, one table, less then 300 rows, few transactions, query time was unacceptable when requesting all the rows, with all columns (15). Admittedly, a lot of this was the browser trying to construct the table. However, it sped up a lot after moving it to SQL.
I'll admit, though, that this company often seems to revel in spending whopping loads of cash on MS products and then deciding later to move to something else that costs too much. ;) I'll also admit that both of those were at the beginning of my ASP coding career, and as such, probably not efficiently coded. However, the speed increase in both upon moving to SQL was very noticable. Paticularly in number 2, an unusable app became one of the most used. The code has since been modified to make it even better, of course.
We're trying to convice our dept. head to allow us to move to something such as MySQL or PostgreSQL. The pricing on both MS-SQL and Oracle continues to rise. While they are both excellent DBs, their price has become prohibitive. They have excellent user interfaces that MySQL & PostgreSQL lack, and additional tools, but for our average use/user, they are far beyond what is needed. I still wouldn't recommend Access for anything beyond personal databases or testing (it does make a pretty decent test bed for DB enabled websites where you can control the concurrent connections.) But then again, this is based off of my experiences, which may not be representative. Could also be influenced by my desire to move away from MS products. Why spend the cash on Access or MS-SQL when there are perfectly good free/inexpensive open source alternatives? :)
Access will work, but it is somewhat inefficient when used as part of a web site. It's also more difficult to maintain should you have to do offline processing that would require downloading, changing, then re-uploading the data file. If you're dealing with a relatively small database or a lightly-used web site, Access should be fine.
mySQL, on the other hand, is client-server technology so it's better in that it's much more scalable for busier/larger sites, and you don't have to download/change/upload the data file at all. Instead, you'd connect to the database in your scripts using ODBC just like it were an Access database. You can also connect to it remotely using ODBC and MS Access has an option to link to such tables (see File ¦ Get External Data ¦ Link Tables).
A lot of web hosting providers offer mySQL support so, depending on your situation, you might only need to ask for it and have an administrator create your tables.
Gernerd
If you're looking for a less expensive way to use MS SQL Server, you might try using a webhoster whose services include SQL Server databases and get the cheaper Developer edition of MS SQL Server, which basically includes the client tools. The downside is you can't be the sa for your SQL Server db.
Actually, this isn't an issue for me. On our site, we already work with MS SQL and Oracle. We also host our own servers. We are moving away from MS SQL, however, in favor of Oracle. Both have costly licensing, but that doesn't seem to bother the powers-that-be here. :)
For my own personal stuff I use mySQL.
