RE: Access OLE DB vs. ODBC
I've screamed about this OLE DB vs. ODBC for almost a year, and no one ever gives me any concrete answers or benchmarks! There was an article in CF Developer's Journal a year or so back about how to set up OLE DB... The author couldn't disclose the speed differences because they were done without adhering to any strict standards, whatever that means. It was just a bunch of bull, because the article focused more on getting OLE DB to work (what a chore it was on CF 4.x -- don't know about CF 5 server). After the author jumped through numerous hoops, I bet he found no speed gain, probably a speed deficiency, so he downplayed benchmarking. Wonderful! Now I have an OLE DB connection which took a half-hour to set up, and it's slow as molasses anyway. All I know is, OLE DB is SLOWER than ODBC when I ran a bunch of tests, with Access. I have seen no speed gain with OLE DB. Access using ODBC is a lot faster for small to medium-sized queries; when you hit queries that return super-large result sets, the two get closer together in speed. But let's face it, if you need an Enterprise database solution, you won't be using Access, because it WILL crap out sooner or later under load. Andy -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 7:58 PM To: CF-Talk Subject: Re: Access OLE DB vs. ODBC My experience was it either worked very well or not at all. I have one big site that uses still Access 2k and OLEDB and gives me absolutely no trouble. Works so well I've been lazy and left it alone for over a year like that. I wasn't always so lucky. There's one particular weird error that crops up for no apparent reason on some files. If you have the problem with your file spec you'll know immediately. Was never able to track down exactly what it was that caused the trouble. From what I saw in the CF forums at the time I wasn't the only one so afflicted, and no one else had any luck tracking down the root cause, either. Give it a shot. You can always switch back. Heck, you have nowhere to go but up ;D --- Matt Robertson[EMAIL PROTECTED] MSB Designs, Inc., www.mysecretbase.com --- -- Original Message -- from: Jim McAtee [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] date: Wed, 31 Oct 2001 13:13:25 -0700 We've got a couple of customers that we host who insist on using Access 97 as their database. One of them has a fairly busy site and a single large Access database of about 1/2 GB. The CF server that hosts the site becomes occasionally unresponsive, no doubt due to the use of Access. Migrating the site to MS SQL is in the works, but we're not in the loop on that, so we can only wait for the customer to complete the job. In the meantime, we need to get the system as stable as we can. Does anyone have much experience using OLE DB instead of ODBC to connect to Access databases that might be able to say whether it's any more stable? I know that articles in the Allaire knowledgebase recommend OLE DB over ODBC. Are there any differences in SQL statements when using OLE DB - that is, any code changes required when changing from Access ODBC to OLE DB? Thanks, Jim ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Access OLE DB vs. ODBC
I think the whole OLEDB is faster than ODBC argument came out of the ADO Programmer's Reference book. If you do a web search for any articles that offer hard number comparisons (I'm using Google) all you can find (or at least all I've been able to find) is reference to some metrics that were put forth in the ADO 2.0 Programmer's Reference (though a lot of people seem to be referencing this). The numbers were (to SQL Server): Connection Times via OLEDB: 18 (I'm assuming this is supposed to be ms) Vs DSN Connection: 82 I personally have tried some comparisons on my own when I was doing Performance Analysis with the old Allaire Consulting group and have found the difference to be nominal when connecting to SQL Server. Maybe others have some other useful data on this, I'm just speaking from my own experiences. J. -Original Message- From: Andrew Tyrone [mailto:[EMAIL PROTECTED]] Sent: Friday, November 02, 2001 11:46 AM To: CF-Talk Subject: RE: Access OLE DB vs. ODBC I've screamed about this OLE DB vs. ODBC for almost a year, and no one ever gives me any concrete answers or benchmarks! There was an article in CF Developer's Journal a year or so back about how to set up OLE DB... The author couldn't disclose the speed differences because they were done without adhering to any strict standards, whatever that means. It was just a bunch of bull, because the article focused more on getting OLE DB to work (what a chore it was on CF 4.x -- don't know about CF 5 server). After the author jumped through numerous hoops, I bet he found no speed gain, probably a speed deficiency, so he downplayed benchmarking. Wonderful! Now I have an OLE DB connection which took a half-hour to set up, and it's slow as molasses anyway. All I know is, OLE DB is SLOWER than ODBC when I ran a bunch of tests, with Access. I have seen no speed gain with OLE DB. Access using ODBC is a lot faster for small to medium-sized queries; when you hit queries that return super-large result sets, the two get closer together in speed. But let's face it, if you need an Enterprise database solution, you won't be using Access, because it WILL crap out sooner or later under load. Andy -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 7:58 PM To: CF-Talk Subject: Re: Access OLE DB vs. ODBC My experience was it either worked very well or not at all. I have one big site that uses still Access 2k and OLEDB and gives me absolutely no trouble. Works so well I've been lazy and left it alone for over a year like that. I wasn't always so lucky. There's one particular weird error that crops up for no apparent reason on some files. If you have the problem with your file spec you'll know immediately. Was never able to track down exactly what it was that caused the trouble. From what I saw in the CF forums at the time I wasn't the only one so afflicted, and no one else had any luck tracking down the root cause, either. Give it a shot. You can always switch back. Heck, you have nowhere to go but up ;D --- Matt Robertson[EMAIL PROTECTED] MSB Designs, Inc., www.mysecretbase.com --- -- Original Message -- from: Jim McAtee [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] date: Wed, 31 Oct 2001 13:13:25 -0700 We've got a couple of customers that we host who insist on using Access 97 as their database. One of them has a fairly busy site and a single large Access database of about 1/2 GB. The CF server that hosts the site becomes occasionally unresponsive, no doubt due to the use of Access. Migrating the site to MS SQL is in the works, but we're not in the loop on that, so we can only wait for the customer to complete the job. In the meantime, we need to get the system as stable as we can. Does anyone have much experience using OLE DB instead of ODBC to connect to Access databases that might be able to say whether it's any more stable? I know that articles in the Allaire knowledgebase recommend OLE DB over ODBC. Are there any differences in SQL statements when using OLE DB - that is, any code changes required when changing from Access ODBC to OLE DB? Thanks, Jim ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Access OLE DB vs. ODBC
BTW, here's a link to one of the few sites I found that had any numbers associated with the comparison for those who are interested. http://www.4guysfromrolla.com/webtech/063099-1.shtml J. -Original Message- From: John Cummings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 02, 2001 12:14 PM To: '[EMAIL PROTECTED]' Subject: RE: Access OLE DB vs. ODBC I think the whole OLEDB is faster than ODBC argument came out of the ADO Programmer's Reference book. If you do a web search for any articles that offer hard number comparisons (I'm using Google) all you can find (or at least all I've been able to find) is reference to some metrics that were put forth in the ADO 2.0 Programmer's Reference (though a lot of people seem to be referencing this). The numbers were (to SQL Server): Connection Times via OLEDB: 18 (I'm assuming this is supposed to be ms) Vs DSN Connection: 82 I personally have tried some comparisons on my own when I was doing Performance Analysis with the old Allaire Consulting group and have found the difference to be nominal when connecting to SQL Server. Maybe others have some other useful data on this, I'm just speaking from my own experiences. J. -Original Message- From: Andrew Tyrone [mailto:[EMAIL PROTECTED]] Sent: Friday, November 02, 2001 11:46 AM To: CF-Talk Subject: RE: Access OLE DB vs. ODBC I've screamed about this OLE DB vs. ODBC for almost a year, and no one ever gives me any concrete answers or benchmarks! There was an article in CF Developer's Journal a year or so back about how to set up OLE DB... The author couldn't disclose the speed differences because they were done without adhering to any strict standards, whatever that means. It was just a bunch of bull, because the article focused more on getting OLE DB to work (what a chore it was on CF 4.x -- don't know about CF 5 server). After the author jumped through numerous hoops, I bet he found no speed gain, probably a speed deficiency, so he downplayed benchmarking. Wonderful! Now I have an OLE DB connection which took a half-hour to set up, and it's slow as molasses anyway. All I know is, OLE DB is SLOWER than ODBC when I ran a bunch of tests, with Access. I have seen no speed gain with OLE DB. Access using ODBC is a lot faster for small to medium-sized queries; when you hit queries that return super-large result sets, the two get closer together in speed. But let's face it, if you need an Enterprise database solution, you won't be using Access, because it WILL crap out sooner or later under load. Andy -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 7:58 PM To: CF-Talk Subject: Re: Access OLE DB vs. ODBC My experience was it either worked very well or not at all. I have one big site that uses still Access 2k and OLEDB and gives me absolutely no trouble. Works so well I've been lazy and left it alone for over a year like that. I wasn't always so lucky. There's one particular weird error that crops up for no apparent reason on some files. If you have the problem with your file spec you'll know immediately. Was never able to track down exactly what it was that caused the trouble. From what I saw in the CF forums at the time I wasn't the only one so afflicted, and no one else had any luck tracking down the root cause, either. Give it a shot. You can always switch back. Heck, you have nowhere to go but up ;D --- Matt Robertson[EMAIL PROTECTED] MSB Designs, Inc., www.mysecretbase.com --- -- Original Message -- from: Jim McAtee [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] date: Wed, 31 Oct 2001 13:13:25 -0700 We've got a couple of customers that we host who insist on using Access 97 as their database. One of them has a fairly busy site and a single large Access database of about 1/2 GB. The CF server that hosts the site becomes occasionally unresponsive, no doubt due to the use of Access. Migrating the site to MS SQL is in the works, but we're not in the loop on that, so we can only wait for the customer to complete the job. In the meantime, we need to get the system as stable as we can. Does anyone have much experience using OLE DB instead of ODBC to connect to Access databases that might be able to say whether it's any more stable? I know that articles in the Allaire knowledgebase recommend OLE DB over ODBC. Are there any differences in SQL statements when using OLE DB - that is, any code changes required when changing from Access ODBC to OLE DB? Thanks, Jim ~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk
Re: Access OLE DB vs. ODBC
What you're going to gain is *not* going to be speed but increased stability *under load*. There's an Allaire KB in there somewhere about using Access in a production environment, where the only real point discussed, if I recall correctly, was that your server is a lot less likely to bust a gut if you're using OLEDB drivers. Once I got rid of most of the Access db's on my server my memory requirements went way down (leaky odbc drivers) and the thing suddenly became solid as a rock. If you're stuck with Access then try the OLEDB as it will work better, if it works at all. If you have control of your own server mySQL for Windows is probably your next best bet, unless you have an extra $5,000 you need to spend, or a dire need for stored procs or triggers. - Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com - - Original Message - From: Andrew Tyrone [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Friday, November 02, 2001 8:46 AM Subject: RE: Access OLE DB vs. ODBC I've screamed about this OLE DB vs. ODBC for almost a year, and no one ever gives me any concrete answers or benchmarks! There was an article in CF Developer's Journal a year or so back about how to set up OLE DB... The author couldn't disclose the speed differences because they were done without adhering to any strict standards, whatever that means. It was just a bunch of bull, because the article focused more on getting OLE DB to work (what a chore it was on CF 4.x -- don't know about CF 5 server). After the author jumped through numerous hoops, I bet he found no speed gain, probably a speed deficiency, so he downplayed benchmarking. Wonderful! Now I have an OLE DB connection which took a half-hour to set up, and it's slow as molasses anyway. All I know is, OLE DB is SLOWER than ODBC when I ran a bunch of tests, with Access. I have seen no speed gain with OLE DB. Access using ODBC is a lot faster for small to medium-sized queries; when you hit queries that return super-large result sets, the two get closer together in speed. But let's face it, if you need an Enterprise database solution, you won't be using Access, because it WILL crap out sooner or later under load. Andy -Original Message- From: Matt Robertson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 7:58 PM To: CF-Talk Subject: Re: Access OLE DB vs. ODBC My experience was it either worked very well or not at all. I have one big site that uses still Access 2k and OLEDB and gives me absolutely no trouble. Works so well I've been lazy and left it alone for over a year like that. I wasn't always so lucky. There's one particular weird error that crops up for no apparent reason on some files. If you have the problem with your file spec you'll know immediately. Was never able to track down exactly what it was that caused the trouble. From what I saw in the CF forums at the time I wasn't the only one so afflicted, and no one else had any luck tracking down the root cause, either. Give it a shot. You can always switch back. Heck, you have nowhere to go but up ;D --- Matt Robertson[EMAIL PROTECTED] MSB Designs, Inc., www.mysecretbase.com --- -- Original Message -- from: Jim McAtee [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] date: Wed, 31 Oct 2001 13:13:25 -0700 We've got a couple of customers that we host who insist on using Access 97 as their database. One of them has a fairly busy site and a single large Access database of about 1/2 GB. The CF server that hosts the site becomes occasionally unresponsive, no doubt due to the use of Access. Migrating the site to MS SQL is in the works, but we're not in the loop on that, so we can only wait for the customer to complete the job. In the meantime, we need to get the system as stable as we can. Does anyone have much experience using OLE DB instead of ODBC to connect to Access databases that might be able to say whether it's any more stable? I know that articles in the Allaire knowledgebase recommend OLE DB over ODBC. Are there any differences in SQL statements when using OLE DB - that is, any code changes required when changing from Access ODBC to OLE DB? Thanks, Jim ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Access OLE DB vs. ODBC
BTW, here's a link to one of the few sites I found that had any numbers associated with the comparison for those who are interested. http://www.4guysfromrolla.com/webtech/063099-1.shtml interesting. all my tests w/cf *never* showed anything that clear cut. ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Access OLE DB vs. ODBC
Mine either. -Original Message- From: Paul Hastings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 02, 2001 2:25 PM To: CF-Talk Subject: Re: Access OLE DB vs. ODBC BTW, here's a link to one of the few sites I found that had any numbers associated with the comparison for those who are interested. http://www.4guysfromrolla.com/webtech/063099-1.shtml interesting. all my tests w/cf *never* showed anything that clear cut. ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Access OLE DB vs. ODBC
You should be able to switch the connection type to OLEDB without any problem, as long as you don't specify a DBTYPE of ODBC in your cfquery tag. It is definitely more stable, although I've found that the latest (2.5 or 2.6) MDAC drivers are fairly stable. We used to have all sorts of crashes when we had the 2.1 MDAC version. We have a 1 gig Access 97 database which was later updated to a 1.6 gig Access 2000 database, and haven't had any problems with it in 2 years (very low volume) tom www.basic-ultradev.com Does anyone have much experience using OLE DB instead of ODBC to connect to Access databases that might be able to say whether it's any more stable? I know that articles in the Allaire knowledgebase recommend OLE DB over ODBC. Are there any differences in SQL statements when using OLE DB - that is, any code changes required when changing from Access ODBC to OLE DB? Thanks, Jim ~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Access OLE DB vs. ODBC
If memory serves, we tried it here. But, it didn't really help the problem. We had an access database that was over 50 mb, and the server continually hung. We found that the only way to get it to stop killing everyone else's stuff was to move it to a different server. (I know, not what you wanted to hear.) The two things we did before that that seemed to help were: 1. Disable maintain database connections on ALL access datasource. 2. Scoured their code for any instances of using concantenation within sql statements and removed them. (These don't work with access and cf for some reason, though I'm not sure if that's still the case with 5.0.) By the way, a group of developers has been working on migrating this to oracle for over a year now. *sigh* -Deanna Deanna Schneider Interactive Media Developer [EMAIL PROTECTED] ~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
