Dawson, "I don't have any problems with the new datasource, I just don't see the performance gains"
I am surprised you are not seeing any performance gains, guess it all depends on your specific environment. What JVM are you Using? What version of CFMX? What OS are you running? My test environment settings are CFMX ENT, JVM 1.4.1_01, Win2k SP3, SQL-Server 2k SP3 You can do some testing with in-line/CFC inserts. Call an INSERT thats in a CFC 10 times or so, using both drivers, that would probably give you some good timing results. Joe Eugene -------Original Message------- From: "Dawson, Michael" <[EMAIL PROTECTED]> Sent: 03/19/03 10:04 AM To: [EMAIL PROTECTED] Subject: SQL Server JDBC Drivers (was: RE: [CFCDev] CFC Performance Problem (OLD-Resolved)) > > Joe, I double-checked the link and I did download the files from the exact URL you posted from MS's site. I also got the drivers working with CFMX thanks to Steven Erat. My config: URL = jdbc:microsoft:sqlserver://uesqlbox:1433;databaseName=dbName Class = com.microsoft.jdbc.sqlserver.SQLServerDriver I don't have any problems with the new datasource, I just don't see the performance gains. I wish I did. The reality is I don't have that many concurrent users on this site. About the most in any 15-minute period has been about 60. Thanks for all your help. M!chael A. Dawson Group Manager, Programming and Software Development Office of Technology Services 812-479-2581 -----Original Message----- From: Joe Eugene [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 10:54 AM To: [EMAIL PROTECTED] Subject: RE: [CFCDev] CFC Performance Problem (OLD-Resolved) Dawson, "I did find out that the CFMX drivers were faster than the MS JDBC drivers, on average." You must have the wrong drivers or wrong configuration. Check previous posts for right driver and config. We are really happy with (MS-SQL JDBC TYPE IV SP1 Driver released Jan 16 2003) driver performance. As i posted earlier.. Mutiple queries per page through CFC's CFMX JDBC/Data-Direct averaged 300-950ms (very inconsistent timing from driver) Insert took about 350ms/insert MS-SQL JDBC SP1 for SQL2k averaged 70-150ms (Consistent timing) Insert's averaged around 0-20ms/insert There is really no comparison. Joe Eugene -------Original Message------- From: "Dawson, Michael" <[EMAIL PROTECTED]> Sent: 03/18/03 11:49 AM To: [EMAIL PROTECTED] Subject: RE: RE: [CFCDev] CFC Performance Problem (OLD-Resolved) > > Thanks to your message, I was able to get the MS JDBC drivers working. I did find out that the CFMX drivers were faster than the MS JDBC drivers, on average. After taking 12 tests, dropping the high and low values and averaging the rest, I ended up with JDBC Query = 234.4 ms and CFMX driver Query = 167.2 ms. I didn't see the huge gain that I hoped to see. I will mention that this query only returns ~12,000 records. Thanks M!chael A. Dawson Group Manager, Programming and Software Development Office of Technology Services University of Evansville 1800 Lincoln Avenue Evansville, IN 47722 812-479-2581 -----Original Message----- From: Steven Erat [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 9:37 AM To: '[EMAIL PROTECTED]' Subject: RE: RE: [CFCDev] CFC Performance Problem (OLD-Resolved) Although, Joe Eugene will likely be able to provide a vendor specific example for the settings of a Microsoft JDBC driver, if you wanted to configure the sql server driver that already comes with CFMX just as an exercise, you could use the following configuration: Driver Class: macromedia.jdbc.sqlserver.SQLServerDriver JDBC Url: jdbc:macromedia:sqlserver://<servername>:1433;databaseName=<dbname> Driver Name: <chooseAnyNameForYorConvience> Where <servername> is the FQHN of the Sql Server database, and <dbname> is for example "northwind". The Microsoft JDBC driver should come with instructions for Driver Class and the JDBC Url -Steven Erat -----Original Message----- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 18, 2003 10:20 AM To: [EMAIL PROTECTED] Subject: RE: RE: [CFCDev] CFC Performance Problem (OLD-Resolved) Joe, can you give an example of what goes in the "Other" datasource settings for SQL Server 2000 and CFMX? Thanks -----Original Message----- From: Joe Eugene [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2003 10:34 AM To: [EMAIL PROTECTED] Subject: Re: RE: [CFCDev] CFC Performance Problem (OLD-Resolved) Chris, Download the JDBC Drivers (Any Vendor), in this case MS-SQL Server from Microsoft. <a target=_blank href="http://www.microsoft.com/downloads/details.aspx?FamilyID=4f8f2f01- 1ed7-4">http://www.microsoft.com/downloads/details.aspx?FamilyID=4f8f2f0 1-1ed7-4</a> c4d-8f7b-3d47969e66ae&DisplayLang=en This only works with SQL-Server 2000, 7 you are out of luck. 1.Install the driver, copy the three *.jar files in the /lib directory to /CFusionMX/wwwroot/WEB-INF/lib. 2.In cf-admin, go to JVM/classes setting, enter the classpath to the directory (wwwroot/WEB-INF/lib), you will need to re-start cfmx after this. 3.Create an "Other" datasource in Cf-Admin, give it the "Url connection String" and Driver Name. Different drivers vary for the connection string params. check docs. This is probably the best configuration for optimal performance. Joe Eugene -------Original Message------- From: Chris Kief <[EMAIL PROTECTED]> Sent: 03/17/03 01:51 PM To: [EMAIL PROTECTED] Subject: RE: [CFCDev] CFC Performance Problem (OLD-Resolved) > > Joe, This is interesting. Could you possibly give a quick rundown on how you went about switching the drivers used by CFMX...I haven't done this before. Thanks! chris >-----Original Message----- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf >Of Joe Eugene >Sent: Monday, March 17, 2003 7:36 AM >To: [EMAIL PROTECTED] >Subject: [CFCDev] CFC Performance Problem (OLD-Resolved) > >Spend quite alot of time testing CFC's. Finally gave up and switched >SQL- Server Data-Direct Driver to Microsoft SQL Server JDBC Driver SP1. > >Data-Direct (CFMX default) >Execution time 300-900ms (very inconsistent) > >Microsoft JDBC Driver SP1 >Execution time 0-20ms (consistent) > >Problem was the Very Poor Data-Direct drivers. >Lesson.. Always go with DB vendor drivers. > >Joe Eugene >---------------------------------------------------------- >You are subscribed to cfcdev. To unsubscribe, send an email >to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' >in the message of the email. > >CFCDev is run by CFCZone (www.cfczone.org) and supported >by Mindtool, Corporation (www.mindtool.com). ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). > ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). > ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). > ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the word 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com).
