Re: CFMX ODBC/JDBC Drivers
Real world users should be reading from the database significantly more than they should be doing anything else. depends on your world. in our municipal info system world they beat the crap out of the db during tax season. pretty much constant reading/updating/deleting/reporting for an intense couple of weeks. the load is high constant, hence our form of testing--100 reads is kind of meaningless to us. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:192063 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
If you saw my code you saw that the only thing I was doing was a select statement to the SQL server. The reason the second test was so fast was because I left caching on, so that I would get a more real world result. Ok, I see. As I said earlier, I have no reason not to use JTDS, as a matter of fact if they were faster I would be more than willing to use them instead of Microsoft's drivers. As it stands now though, I have not been able to justify their use . Even though the tests on your website show the drivers being faster, it was done in a very specific manner, I need the drivers to work in a ColdFusion enviroment and if they aren't faster in ColdFusion then they are no use to me. The tests on the jTDS site (published by a commercial JDBC driver vendor) do just the same: they run a query 100 or 1000 times. I ask anyone else to run the same test I did. A loop that runs 100 times is by no means breaking a site, and in my opinion there is no real reason to bog a server down by running it for hours at a time. Real world users should be reading from the database significantly more than they should be doing anything else. I agree with you. However, what I was trying to say was that you were just executing the query and not processing the results. That is not a real world usage, is it? Your testing demonstrates that the Microsoft and DataDirect drivers are faster at dropping data than jTDS. Do some basic processing of the results (like concatenating them in a string or something, without displaying) and you should have something closer to real world usage. Alin. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191783 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Give me an example of exactly what I should do and I will do it. The reason I didn't do anything other than a select was that my view was that ColdFusion then has the result set so there would be no more use from the SQL server. I do everything in stored procedures, how are the results when using the driver for that? Bob Everland ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191792 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Give me an example of exactly what I should do and I will do it.
I don't know how this should look in CFML but I will give you the equivalent
Java code; I think it won't be hard for you to figure out what I mean. Here
goes:
Statement stmt = conn.createStatement();
for (int i = 0; i 100; i++) {
ResultSet rs = stmt.executeQuery(Select * From Users);
// This is where you stopped processing. In any real world application
// some processing on the selected data should follow. Also, selecting
// a single INT field might not be totally relevant.
// Now do the processing
String res = rs.getString(userId) + rs.getString(userName)
+ rs.getTimestamp(lastLogin) + ...;
}
The reason I didn't do anything other than a select was that my view
was that ColdFusion then has the result set so there would be no more
use from the SQL server. I do everything in stored procedures, how are
the results when using the driver for that?
The idea is that ColdFusion has indeed the ResultSet but it doesn't do anything
with it. It simply calls ResultSet.close(). Now different drivers might choose
to drop the remaining data in different ways; jTDS processes it completely
(which is not necessarily the best idea, but as I said it should not be a
problem in real world applications, which don't just select a huge amount of
data to ignore it).
Alin.
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191831
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Quick question, do the non-datadirect drivers support the return of Ref Cursors as a coldFusion query? -Adam On Wed, 26 Jan 2005 01:13:56 +0700, Paul Hastings [EMAIL PROTECTED] wrote: Robert Everland III wrote: I would like to see the test you ran to see why are test vary so significantly. Were you using the latest Microsoft JDBC drivers? I am using the latest of every driver in this test. yes the very latest drivers were used (though again, mm has an even newer driver as a fix for the problem that started this whole thing for me). test table had 50k rows (if i recall, i'm at home right now). select, update delete queries were run (as well as some SP stuff but weren't timed). cf was set to not save class files, both cf sql servers were stopped restarted between runs. if i recall correctly, 1k loops (or it might have been 10k) were run 5 times the times averaged. as i said it took hours. i've also been told by MM staff that their own tests showed datadirect to be faster than ms. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191717 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Robert, I am a jTDS developer and I can tell you (and you may choose to believe me or not) that jTDS is definitely faster than the MS driver. I'm not familiar with CF but from what I can tell you are doing no processing of the results returned by the query, so the test is a bit irrelevant. You should go through all the results and read each column. Also, the difference between the first and the second run times for the MS driver (it's about a third faster in the second run) makes me think there's something wrong with the test (again, since I'm a Java, not CF programmer I can't say exactly what). Alin. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191731 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CFMX ODBC/JDBC Drivers
I'd say the first time you use things, the classloader is loading the classes, so I wouldn't compare trial 1 and anything Try comparing trial 2 and trial 3. Do you get the same difference in results? What JVM's was jtds tested on? Perhaps jtds benchmarks across jvms should be published somewhere? -Dov -Original Message- From: Alin Sinpalean [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 3:30 PM To: CF-Talk Subject: Re: CFMX ODBC/JDBC Drivers Robert, I am a jTDS developer and I can tell you (and you may choose to believe me or not) that jTDS is definitely faster than the MS driver. I'm not familiar with CF but from what I can tell you are doing no processing of the results returned by the query, so the test is a bit irrelevant. You should go through all the results and read each column. Also, the difference between the first and the second run times for the MS driver (it's about a third faster in the second run) makes me think there's something wrong with the test (again, since I'm a Java, not CF programmer I can't say exactly what). Alin. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191733 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
If you saw my code you saw that the only thing I was doing was a select statement to the SQL server. The reason the second test was so fast was because I left caching on, so that I would get a more real world result. As I said earlier, I have no reason not to use JTDS, as a matter of fact if they were faster I would be more than willing to use them instead of Microsoft's drivers. As it stands now though, I have not been able to justify their use . Even though the tests on your website show the drivers being faster, it was done in a very specific manner, I need the drivers to work in a ColdFusion enviroment and if they aren't faster in ColdFusion then they are no use to me. I ask anyone else to run the same test I did. A loop that runs 100 times is by no means breaking a site, and in my opinion there is no real reason to bog a server down by running it for hours at a time. Real world users should be reading from the database significantly more than they should be doing anything else. Bob Everland ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191736 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Nick de Voil wrote: As well as performance, other aspects to a DB driver are stability functionality (like support for SQL syntax the JDBC spec). Did you do any functionality is pretty much what you'd get from the other two drivers (except the datadirect driver that ships w/6.1 has a bug with unicode data in stored procedure OUTPUT vars, you can contact MM for updated drivers if this is an issue, neither ms or jTDS showed that issue which was why we tested jTDS actually). we didn't do any specific stability test but the speed tests were high load ran for a couple of hours. nothing bad happened. we did inadvertently put the driver through some very minor sustained loading (forgot to swap it out it was left used on a development box for about 5 days or so). it is used in a couple of other products they've listed on their site. beyond that, no idea about its suitability for production. comparative testing of these aspects as well as performance, or do you know of any such testing that's been done? nope. are you volunteering? i recall that pete freitag blogged about this before: http://www.petefreitag.com/item/178.cfm maybe he's done some real load testing? ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191660 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
comparative testing of these aspects as well as performance, or do you know of any such testing that's been done? nope. are you volunteering? Well... we'll start by trying it out in a couple of apps if we find out anything interesting will post it. Thanks Nick ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191662 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Ok here is some quick testing I ran on the different datasources to compare speeds, I wanted to do it as quick as possible, before the thread dies off. I didn't really care which one won, I just want the fastest possible drivers. No settings have been changed. Classes were deleted after each test and server was restarted. Here is what the code I used looks like. cfloop index=i from=1 to=100 step=1 cfquery name=test#i# datasource=JTDSTest Select userid From Users /cfquery /cfloop There are 606 records in this database 1. JTDS : 29891 ms 2. Datadirect : 20969 ms 3. Microsoft JDBC : 24000 ms Now I ran the exact same thing again, in reverse to see if restarting the server made a difference. 1. Microsoft JDBC : 16890 ms 2. Datadirect : 18672 ms 3. JTDS : 28922 ms (I refreshed the page 3 times and couldn't get it to go below this number, I have caching turned on) I would like to see the test you ran to see why are test vary so significantly. Were you using the latest Microsoft JDBC drivers? I am using the latest of every driver in this test. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191693 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Robert Everland III wrote: I would like to see the test you ran to see why are test vary so significantly. Were you using the latest Microsoft JDBC drivers? I am using the latest of every driver in this test. yes the very latest drivers were used (though again, mm has an even newer driver as a fix for the problem that started this whole thing for me). test table had 50k rows (if i recall, i'm at home right now). select, update delete queries were run (as well as some SP stuff but weren't timed). cf was set to not save class files, both cf sql servers were stopped restarted between runs. if i recall correctly, 1k loops (or it might have been 10k) were run 5 times the times averaged. as i said it took hours. i've also been told by MM staff that their own tests showed datadirect to be faster than ms. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191708 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
I did my tests on a new coldfusion install, with 6.1 updater and all the hotfixes. Bob ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191737 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Robert Everland III wrote: If you are using SQL Server 2000 you can always use SQL Server JDBC drivers. Who better than MS to know how to handle their own server. I did some testing by looping through different types of queries such as inserts, updates and selects and with just 1000 queries Microsoft JDBC drivers were a couple seconds faster than the built in JDBC drivers. pretty much everybody knows better than ms when it comes to JDBC sql server. ms's JDBC driver originally came from datadirect hasn't really progressed that far. and my own testing is exactly the opposite of your findings, ms's JDBC driver was the 3rd slowest i tested: #1 jTDS http://jtds.sourceforge.net/ #2 datadirect #3 ms JDBC ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191638 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
Paul pretty much everybody knows better than ms when it comes to JDBC sql server. ms's JDBC driver originally came from datadirect hasn't really progressed that far. and my own testing is exactly the opposite of your findings, ms's JDBC driver was the 3rd slowest i tested: #1 jTDS http://jtds.sourceforge.net/ #2 datadirect #3 ms JDBC As well as performance, other aspects to a DB driver are stability functionality (like support for SQL syntax the JDBC spec). Did you do any comparative testing of these aspects as well as performance, or do you know of any such testing that's been done? We're thinking of trying out jTDS but I'm wondering whether the speed comes at a price if so, what the tradeoff is. I've read what they say about jTDS on sourceforge and of course it sounds great, but any kind of independent corroboration would be nice, particularly from people who've used it in a CF context. Nick ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191657 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
CFMX ODBC/JDBC Drivers
I was looking through the archives for solutions to a couple of database problems that we're having on one of our development servers. One solution that I want to try is rolling back the ODBC/JDBC drivers that come with MX 6.1 to the ones from MX 6.0. I've seen this suggested in several places, unfortunately I can't seem to find a source for the appropriate files and a list of instructions. If anyone could point me in the right direction, I'd greatly appreciate it. Thank you. Scott Wolf | Network Administrator | GravityFree 1960 Stickney Point Road | Second Floor Sarasota | FL | 34231 | 800.207.4431 941.927.7674 | f 941.923.5429 www.GravityFree.com ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191579 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: CFMX ODBC/JDBC Drivers
Scott, The CFMX 6.1 Updater 1 comes with Macromedia drivers version 3.3. These drivers have been demonstrated by CF QA to be superior to earlier versions, including those that were in CFMX 6.1 and especially those that came with 6.0x. Macromedia CF QA recommends using the 3.3 driver version. Steve -Original Message- From: Scott Wolf [mailto:[EMAIL PROTECTED] Sent: Monday, January 24, 2005 4:16 PM To: CF-Talk Subject: CFMX ODBC/JDBC Drivers I was looking through the archives for solutions to a couple of database problems that we're having on one of our development servers. One solution that I want to try is rolling back the ODBC/JDBC drivers that come with MX 6.1 to the ones from MX 6.0. I've seen this suggested in several places, unfortunately I can't seem to find a source for the appropriate files and a list of instructions. If anyone could point me in the right direction, I'd greatly appreciate it. Thank you. Scott Wolf | Network Administrator | GravityFree 1960 Stickney Point Road | Second Floor Sarasota | FL | 34231 | 800.207.4431 941.927.7674 | f 941.923.5429 www.GravityFree.com ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191585 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: CFMX ODBC/JDBC Drivers
If you are using SQL Server 2000 you can always use SQL Server JDBC drivers. Who better than MS to know how to handle their own server. I did some testing by looping through different types of queries such as inserts, updates and selects and with just 1000 queries Microsoft JDBC drivers were a couple seconds faster than the built in JDBC drivers. http://www.microsoft.com/downloads/details.aspx?FamilyID=07287B11-0502-461A-B138-2AA54BFDC03Adisplaylang=en Bob Everland ~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191588 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
