| Author |
 Topic  |
|
D3mon
Senior Member
United Kingdom
1685 Posts |
 Posted - 25 July 2004 : 07:17:13
|
A debate rages between myself and WebWiz regarding the performance characteristics of Access and SQL.
WebWiz maintains that using SQL will always be faster, whatever the server structure, whereas I propose that using an Access database would be faster on structures where the Web server and Access infrastructure are located on the same LAN (or indeed the same CPU)
anyone anything to add? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
aspwiz
Junior Member
250 Posts |
Posted - 25 July 2004 : 07:20:54
|
SQL Server will always be faster on large and complex databases (like snitz)
Access is not even a true RDBMS coz its file based.
If you have 50 users online (anyone there ever have 50 active users?) Access will at times be painfully slow. |
 |
|
|
PeeWee.Inc
Senior Member
United Kingdom
1893 Posts |
Posted - 25 July 2004 : 07:25:40
|
Access can handle more then 50 users if set up right. Huw will back me up there.
|
De Priofundus Calmo Ad Te Damine |
|
|
|
aspwiz
Junior Member
250 Posts |
Posted - 25 July 2004 : 07:29:00
|
In Microsoft's own words....
The following comes from Microsoft article Q300216.
"Microsoft Jet is a file-sharing database system. A file-sharing database is one in which all the processing of the file takes place at the client. When a file-sharing database, such as Microsoft Jet, is used in a multiuser environment, multiple client processes are using file read, write, and locking operations on the same shared file across a network. If, for any reason, a process cannot be completed, the file can be left in an incomplete or a corrupted state. Two examples of when a process may not be completed is when a client is terminated unexpectedly or when a network connection to a server is dropped.
Microsoft Jet is not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as Web, commerce, transactional, and messaging servers. For these type of applications, the best solution is to switch to a true client/server-based database system such as Microsoft Data Engine (MSDE) or Microsoft SQL Server. When you use Microsoft Jet in high-stress applications such as Microsoft Internet Information Server (IIS), customers have reported database corruption, stability issues such as IIS crashing or locking up, and also a sudden and persistent failure of the driver to connect to a valid database that requires re-starting the IIS service."
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 25 July 2004 : 10:22:29
|
quote:
Originally posted by aspwiz
Access is not even a true RDBMS coz its file based.
Better stay put than say something completely wrong. A relational database management system is a database that implements the relational model. It has nothing to do with being filed based or not, ok? In terms of relational model support, Access is not worse than SQL Server.
The issue with Access is handling concurrent access in an environment where there will be many requests made by the database. Access is perfectly good for low traffic sites and for database sizes not too small. Depending on server characteristics, Access can perform quite well. I've seen the exact same Snitz DB in Access and SQL Server and a search taking the double ammount of time in SQL Server (Jeepaholic can confirm that).
Now on the scenario described by D3mon. I'm sorry but I can't agree with you. For that scenario, with a big database and a large number of requests Access will let you down. For low traffic situations and moderately sized DBs, will a server with a enough RAM and a reasonable CPU, Access can give SQL a run for the money...
Microsoft tends to degrade Access reputation as a DB alternative for use with IIS. That sounds to me much more an attempt to have people buy the hugely expensive SQL Server than anything else. I run a low traffic site with 5 databases in a Windows server since 2001 without any meaningful problem.
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 25 July 2004 10:24:02 |
|
|
|
aspwiz
Junior Member
250 Posts |
Posted - 25 July 2004 : 21:17:41
|
quote:
Originally posted by ruirib
quote:
Originally posted by aspwiz
Access is not even a true RDBMS coz its file based.
Better stay put than say something completely wrong. A relational database management system is a database that implements the relational model.
I always tend to think of file based databases as a bit of a bodge job..... but sure on reflection I agree with your comment.
Anyway, I have to say that if someone ever optimised snitz for SQL (i mean using stored procs, etc, etc) then the senario of access occasionally being faster would never occur.
Searching in snitz is no way optimised for SQL, and I believe some mod author addressed that.
Maybe one day we could have a snitz purely optimsed for SQL.... now that really would fly! |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 25 July 2004 : 21:44:43
|
quote:
Originally posted by aspwiz
Searching in snitz is no way optimised for SQL, and I believe some mod author addressed that.
Yeah, I guess I was the one who did it  . It's a simple change of strategy, guess what, motivated by that contrast in behavior between access and SQL Server that I described in my previous post. Anyway, even my change in strategy wasn't enough to beat Access, can you believe that?! |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 25 July 2004 : 23:41:36
|
quote:
Originally posted by aspwiz
I always tend to think of file based databases as a bit of a bodge job.....
Why? On a system architecture level there isn't much difference between a file and a directory of files. To be honest I don't understand OSes enough to know how one file would be treated differently than a directory of files, but I don't expect it would be much different under the hood.
I mean, besides compression is there much difference between a .zip file and a folder? |
 Nathan Bales
CoreBoard | Active Users Download |
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 26 July 2004 : 00:51:51
|
quote:
Originally posted by aspwiz
quote:
Originally posted by ruirib
quote:
Originally posted by aspwiz
Access is not even a true RDBMS coz its file based.
Better stay put than say something completely wrong. A relational database management system is a database that implements the relational model.
I always tend to think of file based databases as a bit of a bodge job..... but sure on reflection I agree with your comment.
Anyway, I have to say that if someone ever optimised snitz for SQL (i mean using stored procs, etc, etc) then the senario of access occasionally being faster would never occur.
Searching in snitz is no way optimised for SQL, and I believe some mod author addressed that.
Maybe one day we could have a snitz purely optimsed for SQL.... now that really would fly!
Not to get off topic but yea, search isn't the greatest.  I'm going to get together with some SQL Expert buddies of mine at work in the following months to help come up with a better solution. I'll gladly share whatever we come up with. As for Snitz and Procs...I believe your right there too. Using the tools of MSSQL is where the speed really comes from, as well as being able to do things like run subqueries, multiple queries at a time, etc..
|
Erick
Snowmobile Fanatics
|
Edited by - sr_erick on 26 July 2004 00:56:03 |
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 26 July 2004 : 07:46:26
|
The file-based aspect of access is this - if you are using an mdb client (not a web-client) Access will transfer the whole table to the client, take the writes etc, and copy back (ie the local copy of Jet does the work). SQL Server is more akin to shouting commands to the remote db engine to do the work, ie blindingly less network traffic. Not really an issue for web apps imho, although SQL Server has some huge selling points above and beyond this.
As for access & multi-user, remember it was concieved as a single-user db, but the marketers declared multi-user was what was needed for the market, hence all the 'bodge job' .ldb file business. |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 01 August 2004 : 20:06:52
|
The debate goes on. For me, testing a single-user snitz with an access db on the web server is noticeably quicker than testing a single-user snitz with a SQL 7 backend on a networked database server (or a local msde server on the web server).
Access works pretty well until you overload it.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 01 August 2004 : 21:14:47
|
quote:
Originally posted by Doug G
Access works pretty well until you overload it.
I second this view . Access is pretty darn good, as long as you use it as it was intended to be. Always loved Access, right from version 1.0! |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Jeepaholic
Average Member
USA
697 Posts |
Posted - 01 August 2004 : 22:45:42
|
quote:
Originally posted by ruirib
The issue with Access is handling concurrent access in an environment where there will be many requests made by the database. Access is perfectly good for low traffic sites and for database sizes not too small. Depending on server characteristics, Access can perform quite well. I've seen the exact same Snitz DB in Access and SQL Server and a search taking the double ammount of time in SQL Server (Jeepaholic can confirm that).
Yup. Access beat the crap out of MS SQL when it came to Snitz searches. I was blown away. To say that SQL will always be faster is just flat wrong... 
I ran a forum with over 100,000 posts and typically 20-30 on-line users (with a 15-minute user "expiration") on an Access database and didn't really see a performance issue until I got near 135-140k posts and 30+ regular users. At that point, moving to MS SQL made a difference (in everything but the searching!) I actually disabled searching to only Subject's until I got faster hardware to handle them. |
Al Bsharah
Aholics.com
Jeepaholics Anonymous
Broncoholics Anonymous
Network Insight
|
|
|
|
Nathan
Help Moderator
USA
7664 Posts |
Posted - 02 August 2004 : 00:07:42
|
I actually think if fulltext indexing were to be used MS SQL and MySQL seaching would be much quicker than Access.
I should keep my mouth shut though because I don't know that snitz doesn't use fulltext indexing, I'm only assuming. |
Nathan Bales
CoreBoard | Active Users Download |
|
|
|
Topic |
|
Topic Locked
