Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
oracle or access?
- Thread starter avivit
- Start date
- Thread starter
- #2
Nebuchednezzar
MIS
I personally don't know of any set guidelines for when to switch from access to Oracle.
In my opinion, it is the size of the database, the coding differences (I find Oracle much easier to code against), and the power of the client PCs that should be considered.
I worked for a software development company that developed primarily in access, sql server, and oracle.
Access is primarily considered a file server product. Most of your processing of any data (calculations, variable use, etc) is done on the client. Oracle and Sql server are considered Client server setups. You can write your applications to do all of the processing on the client, but you have the ability to store a lot of the code on the server in the way of procedures, triggers, functions, etc. This leads to less strain on the client PCs, and puts most of the processing load on the server.
As far as size goes...the larger any database gets, the slower it runs, typically.
I found that access starts to really bog down once you get over several hundred thousand records in multiple tables (a couple of tables may be OK - your problem will result when you try to do table joins or lookups against several large tables at once).
As far as coding goes...I just hate access. You have to go all around to do anything. The result of a complex if statements in access can be gotten much easier in Oracle using a DECODE function for instance.
In my opinion, it is the size of the database, the coding differences (I find Oracle much easier to code against), and the power of the client PCs that should be considered.
I worked for a software development company that developed primarily in access, sql server, and oracle.
Access is primarily considered a file server product. Most of your processing of any data (calculations, variable use, etc) is done on the client. Oracle and Sql server are considered Client server setups. You can write your applications to do all of the processing on the client, but you have the ability to store a lot of the code on the server in the way of procedures, triggers, functions, etc. This leads to less strain on the client PCs, and puts most of the processing load on the server.
As far as size goes...the larger any database gets, the slower it runs, typically.
I found that access starts to really bog down once you get over several hundred thousand records in multiple tables (a couple of tables may be OK - your problem will result when you try to do table joins or lookups against several large tables at once).
As far as coding goes...I just hate access. You have to go all around to do anything. The result of a complex if statements in access can be gotten much easier in Oracle using a DECODE function for instance.
I'd also add Multiple users and recoverability.
I've not used access so I won't comment on it except to say that from what I've read it can start to have problems when more than 50 users are trying to access the same tables and recovering a corrupt database / table can have you crying or possibly your users crying when they have to type a lot of data back in.
Oracle on the otherhand works quite splendidly on this.
I've worked on a system that would have around 500 users logged in most of the time, it would perform no problems.
The other one is recoverability. Through the major, and I mean major disasters I've been through, where we've had to go back to backups nearly a week old to get a "Good version" not once have we had to get a user to type back in a single peice of data.
I've not used access so I won't comment on it except to say that from what I've read it can start to have problems when more than 50 users are trying to access the same tables and recovering a corrupt database / table can have you crying or possibly your users crying when they have to type a lot of data back in.
Oracle on the otherhand works quite splendidly on this.
I've worked on a system that would have around 500 users logged in most of the time, it would perform no problems.
The other one is recoverability. Through the major, and I mean major disasters I've been through, where we've had to go back to backups nearly a week old to get a "Good version" not once have we had to get a user to type back in a single peice of data.
- Thread starter
- #5
Thanks Nebuchednezzar and MikeJones.
1. MikeJones, What do you mean by: "users crying when they have to type a lot of data back in".
My users only have to type a word or two in order to
search the database (filling in a text box, or choosing an option from select box).
I'd like to understand what u did you mean by that.
2. As for the no. of users that are using the db in the same time:
How will I know if there's a damage in the db?
And if I count the no. of users the same time, and limit the access to, say, 50 users, will that solve that problem?
Thanks in advance
1. MikeJones, What do you mean by: "users crying when they have to type a lot of data back in".
My users only have to type a word or two in order to
search the database (filling in a text box, or choosing an option from select box).
I'd like to understand what u did you mean by that.
2. As for the no. of users that are using the db in the same time:
How will I know if there's a damage in the db?
And if I count the no. of users the same time, and limit the access to, say, 50 users, will that solve that problem?
Thanks in advance
By "users crying when they have to type a lot of data back in" I mean if you get a DB corruption and have to go back to an old save then roll the database forward. Oracle keeps a thing called a redo log, every time a change to the data in the database is made oracle stores this change in the redo log. So say you back your database up at the start of the day and then at lunch time the database corrupts. (This may not be the fault of the DB by the way, another program or the OS system its self may cause this . For example we had the problem with Oracle 8.1.5 running on AIX, 2 very stable bits of software you would think!) You restore the DB back to the save you made at the start of the day but any changes your users have made in the morning are lost!! With Oracle, you "Replay" the redo log against the DB and all the changes the users made in the morning are apoplied to the DB. The DB will be back in exactly the same state as when it was corrupted. This situation can be made worse when you have to go back to a very old copy, sometimes a DB save can fail (Tape corruptions were quite common at my old place) or perhaps you didn't back the DB up over the last few days as you didn't have time to bring it down to back it up. I don't know if Access has these abilities, but as it's part of Office I'd be surprised...
As for your second question I'm afraid I don't know enough about Access to comment, maybe someone else does???
As for your second question I'm afraid I don't know enough about Access to comment, maybe someone else does???
- Thread starter
- #7
Got it. Thanks.
So when you say 50 users (for exp.) accessing the db in the same tme can cause a damage, u mean users that can add data to the db right?
Not ppl who only search the db and can not make changes to it right? (searches here r made through asp pages. Could be perl)
Thanks for the information
So when you say 50 users (for exp.) accessing the db in the same tme can cause a damage, u mean users that can add data to the db right?
Not ppl who only search the db and can not make changes to it right? (searches here r made through asp pages. Could be perl)
Thanks for the information
- Thread starter
- #9
Thank you. That's what I wanted to hear.
We make the changes on our shared directory (one programmer at a time!!!),
and than upload it to our web-site, instead of the current
access file. That's all.
Users can make queries through the web site, and the db is a read only file to the users. So this is not supposed to be a problem(as u already said).
We do upload a file while a user might try to query through the db through the web site, but we can't upload it at night.
Damm, I never thought there could be a problem.
But this probelm will not be solved by using oracle anyway.
I guess it has the same problem.
Thanks much.
Anymore comments r always welcome, from anyone who wants to.
We make the changes on our shared directory (one programmer at a time!!!),
and than upload it to our web-site, instead of the current
access file. That's all.
Users can make queries through the web site, and the db is a read only file to the users. So this is not supposed to be a problem(as u already said).
We do upload a file while a user might try to query through the db through the web site, but we can't upload it at night.
Damm, I never thought there could be a problem.
But this probelm will not be solved by using oracle anyway.
I guess it has the same problem.
Thanks much.
Anymore comments r always welcome, from anyone who wants to.
wushutwist
Programmer
Actually, I see very little comparison between Access and Oracle. Access is great as a desktop database but I would never use it as a background for a client/server app or web app.
As far as performance goes, Access doesn't slow down with 50 concurrent users. This is because Access only allows 30 users to be connected at any one time.
Wushutwist
Sun Certified Java 2 Programmer
As far as performance goes, Access doesn't slow down with 50 concurrent users. This is because Access only allows 30 users to be connected at any one time.
Wushutwist
Sun Certified Java 2 Programmer
- Thread starter
- #11
30 users just to search the db through the web site?
Gee...
But I know of lots of commercial sites that use shopping cart when using access+asp?
How come it all works then? U say it doesn't?
Can I alert a message to the 31 user and on, that the db users quota is full right now?
Where can I read all these limitations?
Gee...
But I know of lots of commercial sites that use shopping cart when using access+asp?
How come it all works then? U say it doesn't?
Can I alert a message to the 31 user and on, that the db users quota is full right now?
Where can I read all these limitations?
DBAwhosaysNIE
MIS
If money is a constraint, which is the only argument I can think of for using M$ products (NT/IIS/ASP/Access), then I'd consider looking at a Linux/Apache/PHP/mySQL solution. You may be facing a learning curve but you'll end up with an architecture that's faster, more secure/reliable and actually cheaper!
just my 2c/never 2Bho...
just my 2c/never 2Bho...
- Thread starter
- #13
as well you might want to also consider the amount of db tools there are that recognize the vb/oracle mix. sql navigator, for instance, allows you to write up a sql script in it's editor and will at the click of a button translate it into ready to use format that you can paste into a vb method(it will also do the reverse - strip the vb markings and run just the sql when you paste code into the sql-navigator editor!).
- Status
Similar threads
