Access vs. SQL
-
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.