Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
igendreau
Starting Member
6 Posts |
 Posted - 2011-11-14 : 11:19:44
|
| Most of my programming experience is all based on VBA for Access applications. Typically, I've split the tables into their own database. Then I install the forms, queries, etc... onto the user's computer, and link to tables on our network. If you're in the office where our server is located, it's fine, performance-wise. If you're at home, or one of our remote offices, pulling the data back and forth over the network/internet is an absolute bear.So I've decided to switch gears and move to developing apps in Visual Studio 10 with SQL Server as my database.My question is this... I feel pretty comfortable developing a Windows Forms App in VS10. Less so developing an ASP.NET Web App. If I develop and app that is installed locally, but is pulling data from a SQL Server instance running on the server in our office, will I be okay, or will pulling and pushing data from my SQL Server database result in similar slowness when I push and pull from Access?I know that if I did the ASP.NET web app, having the app process server side would get rid of my performance issue. Just wondering what kind of performance an experienced SQL Server user would expect to see versus a split Access database. Thanks for the help! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-14 : 13:25:55
|
| Its an interesting question.Many MANY years ago I developed Office Applications. The sort of thing that was installed on each user's PC, it connected to the database, got the data it needed, processed it, and saved the results back to the database.We started with an ISAM database in the 70's, and upgraded to SQL in the 90's. We didn't know much about SQL at that time (a bit like you I guess) and we kinda did things using SQL to mimic the way that we had done them in ISAM. It was "OK". With what I know now about SQL, I shudder at the way we did them in the 90's - but ... it worked ... and our clients had a sensible upgrade path.Now we only build things that are web-based. The web browser interface is rubbish compared to what we did in our APP back in those days (but you could probably avoid that using DotNet - but we now build things that are used by Joe Public with bog standard Browsers and HTML) but other than that it works well.Back then my target client had maybe 100 or 200 concurrent users, and they probably only did one "transaction" a minute (look at a form/screen, think, enter data in form, SAVE).Now we have tens-of-thousands of concurrent users, browsing through pages often at less than 10 seconds per page ...Back then each Client PC used its own CPU to do all the work - so the User could work at the speed of their own PC.Now EVERYTHING is done by a single, central, Web Server plus a single SQL box - so it HAS to be efficient to cope.Given the experience we have now, compared to then, we have highly optimised SQL code. Just typing this, and thinking about it, it is astonishing how much throughput we get out of SQL, and also how much "resilience" we have - we have resilient hardware (in the form of things like RAID), we have resilient transaction processing (so we can ROLLBACK a number of sequential updates when we get to a point where we discover we cannot continue - "ATOMic" transactions), we have LOG backups which means we can restore to a specific moment-in-time (or we can create a temporary database restored to a specific moment-in-time and investigate what the state of the database was at that time, or how a fraud was perpetrated); we have fail-over servers, so if one breaks we carry on with a fallback machine as-if nothing had happened, we have disaster-recovery opportunities, so clients can cope with their whole data-centre building begin destroyed ...If feels really "grown up" compared to the days of ISAM (and I expect, compared top Access too).But getting from the impressive knowledge we had of ISAM, to getting to (what I would now consider to be) an impressive knowledge of SQL took a long time. But we produced a capable replacement to our ISAM application within a year. |
 |
|
|
|
|
|
|
|
SQL Server vs Access Performance