Access vs. SQL
-
Sorry I'm not against SQL Server, but I would like to defend Access a bit... Jeremy Falcon wrote: Tables cannot have more than 65K records In my MDB: . I have tables with more than 200k records... . I have more than 250 tables in a database file... . The database size is over 400Mbyte... . I have 40 current clients connected to the MDB file at the same time (in terminal server environment). I'm crazy? :~ Some fiends already answer YES to the question... and they hate me because the develop of the application is around 1 big C++/MFC project that makes the thing easy and fast as butter (the application is over 200k rows code written... with wizards, ok... and it builds 1 EXE file that is over 5000k bytes - I'm not proud of it). Jeremy Falcon wrote: and a MDB file cannot be larger than 2GB don't know if I would like to try in this life :suss: but with the current running... we will go to that size in 4 years! If I have to discuss Access, it's to have a SERVER based database, reduce the net traffic and increase data access security. For the rest, a lot of things are really easier with Access, especially when I ask the users to make their own queries. Consider even to make an application and solve the needing to distribute it as a demo... Access MDB will be easier for sure! And I safe myself from a lot of headache during development and setup... Lately I'm making performance comparison between the 2 databases (SQL/Access); there are not really big differences. For small databases size Access is much better, in big databases the balance gives reasons to SQL. Another point that gives advantage to SQL is "multithreading"; normally SERVER based database drivers (OLE DB) are thread safe. Jet engine was not (in the past, don’t know now... anyway I have to make some simple "tricks" to make code work as well). The real problem for me is a FILE compared to a SERVER database. My applications are still "Windows Framework" based, and there are more advantages to use a database by SERVER. FILE databases (consider even XDB solutions) have the disadvantage that network system administrators have to open a dangerous file sharing to the specific resource, and this increase the network traffic too much. Now I'll really love to try MySQL ($$ reasons). What is attracting me now in the SQL server direction is the "Analysis Server" (data mining, OLAP, ...)... too cool! :cool: Anyway, I like SQL Server, but
John A. Johnson wrote: I have tables with more than 200k records... I'd have to see it to believe it. I'm not talking collectively here, I'm talking about the actual row count per table. If you have more than 65K rows, then it could only be a result of MS increasing the limit for Access 2002. I know from first-hand experience that Access and Excel have had this limitation since the dawn of ages. John A. Johnson wrote: The database size is over 400Mbyte... Which is small compared to an Enterprise-wide database. Some version of SQL Server have a 6GB filesize limit with others don't. Here's another number for ya... FAT32's have a 4GB filesize limit. John A. Johnson wrote: Here I'll love to have your (and others) opinion because my actual experience is only based on Access and SQL Server. I really enjoy MySQL, but sometimes I end up working at "Microsoft shops" and have little choice as to what I use. But, out of Access or SQL Server, I chose SQL Server. If you want to make life a little easier for your users with MySQL, they have a couple of GUI front-ends available. Jeremy Falcon Imputek
-
A couple of questions Why is setting the hardware to full duplex a 'bad, bad idea'? Queries result in a full table copy, but how do inserts and updates get handled? Does the copied table remain resident for a period or does it expire/refresh for each subsequent query?
Richard Melton wrote: Why is setting the hardware to full duplex a 'bad, bad idea'? Because 99% of the time it results in painfully slow network performance. It doesn't speed it up. The standard for 802.3u in ambiguous duplex situations is *half* duplex. This is supposed to ensure that in the event of a problem the lowest common denominator will prevail. In most cases ethernet hardware will fall back to the standard if it has any problems with auto-sensing or negotiating packet rate. (most notably hubs, switches routers etc) At low data rates this will work fine, even when there is a duplex mix, because despite the fact there there may be many error packets, there is so little data flowing that it's not noticeable. At higher utilization the full duplex devices will start to see abnormally high crc or packet alignment errors in a mixed duplex environment. End result to the user will be that the whole thing is running at more like 1mbps and in extreme cases the workstation will drop it's connection to the server intermittently which (among other things of course) causes very bad things to happen to a JET database that is in the midst of being updated. The flipside of this same problem is that it's not at all unusual for even the highest quality network cards to fail to recognize that a hub or router or switch is running at half duplex (in many cases is not capable of running at full duplex or may have just spontaneously decided to fall back to the standard - half duplex) and so the NIC will run at full duplex and problems will ensue. Unless everything is perfect (and it seldom is) forcing every device to half duplex will ensure stability and optimal performance. It's very easy to test for the above condition, find a file large enough that it can be timed as it's copied over the network. Copy and time it with *ALL* hardware set to the same duplex setting and compare the results, never use the automatic setting for duplex, always force it for the reasons above. After many years of being an "in the trenches" network tech I can't even count the number of networks that we took over and just by making this setting change made a huge improvement that all the users noticed right away.
-
My company has been developing a significant amount of web apps lately that are going on our company Portal. All of these are outsourced and cost us about $25,000 on average. They are usually coded in JSP or ASP.NET and connect to an Oracle DB. Recently, I've started to hack together some Intranet pages using ASP connecting to an Access DB to throw together some apps with similar functionality quickly/easily/cheaply. What do people think about using Access vs. SQL/Oracle for small to mid sized applications? What is the cutoff point for # of users? What is the max # of records or DB size before deciding that a "real" database should be used. I am talking specifically about web-enabled applications and using Access/SQL as the back-end and am curious how other people make this decision. Chris LaQuerre Internet Technologies Consultant
For those who recommended MSDE and used the word "free", you may want to read up: http://www.microsoft.com/sql/howtobuy/msdeuse.asp[^] It's not "free" in most circumstances - you had to have obtained it by purchasing one of a handful of products. If you just download MSDE without owning a license for one of these other packages, you may not redistribute MSDE. There are exceptions, though, so read up at the link above. Ryan LaNeve www.laneve.com[^]
-
Ok, allright. If you have many Access based DBs, now we are not talking only about choosing a DB software for a Web Application, but the hole company. I agree in this case, it may be convenient to use Access to directly read data, than running programms to export Access data into MsSql every day. There are many limmitations and disadvantages which should be considered, but it may be a convenient solution in this case.
>> Ok, allright. quitter :mad::laugh: seriously though that really sucks! Despite the earlier comments about the wonderful GUI of access for developers it pales in comparsion to SQL Server for develpment let alone the performance/limitation problems. I use Access for Desktop and i hate having to use it. When im developing in SQL Server all is right with the world! :laugh:
"No matter where you go, there your are..." - Buckaoo Banzi
-pete
-
Hi Chris, I can answer this one and with some authority too! :) I've worked with both for years for business apps. Ideally if you are making an application that will have "legs" you would make a data access layer in your application that sits between the program and the database itself. This layer would contain all the SQL commands and anything else related to the specific database and the program above that layer would not care what type of database there was. This would ensure that in future if you moved from SQL to Oracle for example you would only need to change that one layer. If you find yourself putting a query within your business logic part of your program code stop because it's "madness" in the long term. :) My answer now would always be to go SQL server route because of scaleability. Because you can use and distribute the MSDE SQL server engine for free it doesn't make sense to start with anything else 99% of the time for any app that is more than just a little in-house utility. Access (JET 4.0) databases are file based so performance really depends upon the actual size of the tables being queried and the number of simultaneous users on a network accessing it. People will often make generalizations that Access is always slower, this is not true at all, in some circumstances Access is faster than MSDE for example. There are really three options if your considering Access versus SQL server because there is also the MSDE which is a "light" version of SQL server that has the advantage of complete compatibility with SQL server for future scaleability and is free to use and distribute if you have a product that comes with it. (i.e. Visual studio professional, Office professional etc). SQL server - Fast server based. It is inherently fast for many reasons, but when comparing against JET (Access) it is faster in cases where a client has to query a large table because the server processes the query and only returns the results of the query. - Saves on network utilization because of the above - Can be scaled up simply by adding better hardware. - Will allow for terrabytes of data - Nearly unlimited number of connections supported - Pricey - comes with many management and administrative tools MSDE - Fast server based - Maximum of 2GB of total database size - Has built in throttle back mechanism that starts to deliberately slow performance when the 11th or more connection is made to it. - Has no managment tools so you need to programatically build databases and administrate
-
I recently looked at migrating to MSDE from Access. I got hung up on the installation/distribution issue. can you address that?
"No matter where you go, there your are..." - Buckaoo Banzi
-pete
-
Richard Melton wrote: Why is setting the hardware to full duplex a 'bad, bad idea'? Because 99% of the time it results in painfully slow network performance. It doesn't speed it up. The standard for 802.3u in ambiguous duplex situations is *half* duplex. This is supposed to ensure that in the event of a problem the lowest common denominator will prevail. In most cases ethernet hardware will fall back to the standard if it has any problems with auto-sensing or negotiating packet rate. (most notably hubs, switches routers etc) At low data rates this will work fine, even when there is a duplex mix, because despite the fact there there may be many error packets, there is so little data flowing that it's not noticeable. At higher utilization the full duplex devices will start to see abnormally high crc or packet alignment errors in a mixed duplex environment. End result to the user will be that the whole thing is running at more like 1mbps and in extreme cases the workstation will drop it's connection to the server intermittently which (among other things of course) causes very bad things to happen to a JET database that is in the midst of being updated. The flipside of this same problem is that it's not at all unusual for even the highest quality network cards to fail to recognize that a hub or router or switch is running at half duplex (in many cases is not capable of running at full duplex or may have just spontaneously decided to fall back to the standard - half duplex) and so the NIC will run at full duplex and problems will ensue. Unless everything is perfect (and it seldom is) forcing every device to half duplex will ensure stability and optimal performance. It's very easy to test for the above condition, find a file large enough that it can be timed as it's copied over the network. Copy and time it with *ALL* hardware set to the same duplex setting and compare the results, never use the automatic setting for duplex, always force it for the reasons above. After many years of being an "in the trenches" network tech I can't even count the number of networks that we took over and just by making this setting change made a huge improvement that all the users noticed right away.
Hi, I'm quite interested in this issue, as I'm part of a network admin group for the dorm where I live. ATM, most links are running at half-duplex, but we're thinking about upgrading the whole thing to full duplex. You don't happen to have an url or two describing this issue in detail?
Cheers,
Marc:beer: Click to see my *real* signature :beer:
-
Hi, I'm quite interested in this issue, as I'm part of a network admin group for the dorm where I live. ATM, most links are running at half-duplex, but we're thinking about upgrading the whole thing to full duplex. You don't happen to have an url or two describing this issue in detail?
Cheers,
Marc:beer: Click to see my *real* signature :beer:
-
My company has been developing a significant amount of web apps lately that are going on our company Portal. All of these are outsourced and cost us about $25,000 on average. They are usually coded in JSP or ASP.NET and connect to an Oracle DB. Recently, I've started to hack together some Intranet pages using ASP connecting to an Access DB to throw together some apps with similar functionality quickly/easily/cheaply. What do people think about using Access vs. SQL/Oracle for small to mid sized applications? What is the cutoff point for # of users? What is the max # of records or DB size before deciding that a "real" database should be used. I am talking specifically about web-enabled applications and using Access/SQL as the back-end and am curious how other people make this decision. Chris LaQuerre Internet Technologies Consultant
We have a small intranet (customer information and support tracking), used regulary by 6 people, backed by MS Access. Some pain with choosing the right way to connect (and the resulting fin with DATE/TIME formats), but after that, it runs perfectly well.
If you go to war, you will destroy a great country a stoned greek chick to the richest man of the world
[sighist] | [Agile Programming] [doxygen] -
>> Ok, allright. quitter :mad::laugh: seriously though that really sucks! Despite the earlier comments about the wonderful GUI of access for developers it pales in comparsion to SQL Server for develpment let alone the performance/limitation problems. I use Access for Desktop and i hate having to use it. When im developing in SQL Server all is right with the world! :laugh:
"No matter where you go, there your are..." - Buckaoo Banzi
-pete
Music tip of the day: Depeche Mode - I just can't get enough. :-D -- Uh huh. Yeah.
-
Hi Chris, I can answer this one and with some authority too! :) I've worked with both for years for business apps. Ideally if you are making an application that will have "legs" you would make a data access layer in your application that sits between the program and the database itself. This layer would contain all the SQL commands and anything else related to the specific database and the program above that layer would not care what type of database there was. This would ensure that in future if you moved from SQL to Oracle for example you would only need to change that one layer. If you find yourself putting a query within your business logic part of your program code stop because it's "madness" in the long term. :) My answer now would always be to go SQL server route because of scaleability. Because you can use and distribute the MSDE SQL server engine for free it doesn't make sense to start with anything else 99% of the time for any app that is more than just a little in-house utility. Access (JET 4.0) databases are file based so performance really depends upon the actual size of the tables being queried and the number of simultaneous users on a network accessing it. People will often make generalizations that Access is always slower, this is not true at all, in some circumstances Access is faster than MSDE for example. There are really three options if your considering Access versus SQL server because there is also the MSDE which is a "light" version of SQL server that has the advantage of complete compatibility with SQL server for future scaleability and is free to use and distribute if you have a product that comes with it. (i.e. Visual studio professional, Office professional etc). SQL server - Fast server based. It is inherently fast for many reasons, but when comparing against JET (Access) it is faster in cases where a client has to query a large table because the server processes the query and only returns the results of the query. - Saves on network utilization because of the above - Can be scaled up simply by adding better hardware. - Will allow for terrabytes of data - Nearly unlimited number of connections supported - Pricey - comes with many management and administrative tools MSDE - Fast server based - Maximum of 2GB of total database size - Has built in throttle back mechanism that starts to deliberately slow performance when the 11th or more connection is made to it. - Has no managment tools so you need to programatically build databases and administrate
J Cardinal wrote: - Has no managment tools so you need to programatically build databases and administrate it, however there are many excellent freeware administrative packages that work well on the net so this isn't much of an issue at all. Hold your horses. What about
osql.exe? :) -- Uh huh. Yeah.