We were using the jTDS driver for awhile to, with decent results -- except that it is not JDBC 2.0 compliant. It states that it is, but it is missing quite a few features -- mostly notably scrollable resultsets. So, if you do any paging work at all, I have found the jTDS driver to be orders of magnitude slower because it has to iterate though the entire result set.
We are now using the Merila driver from i-net, to much success. -Andrew -----Original Message----- From: Robert S. Sfeir [mailto:[EMAIL PROTECTED] Sent: Thursday, February 26, 2004 8:47 AM To: OJB Users List Subject: Re: OJB + MSSQL sp_executesql problem Yeah, and further, is seems that the MSSQL MS Driver can't deal with sets in an unordered order of fields, meaning if the DB has, id name age height, and you do sets in the order of id age name height, the driver will choke and complain. How lame is that? You might want to take a look at this driver, granted it's still beta, but we've had good results with it: http://sourceforge.net/projects/jtds/ R Charles Anthony wrote: >Hi Alex, > >Very simply, OJB does not issue the sp_executesql statement; the >Microsoft JDBC driver does ! OJB just issues the "SELECT" statement. > >I would strongly suggest that you look to using a different MSSQl JDBC >Driver[1]; about a year ago I did a comparative benchmark of JDBC >Drivers for MS SQL, looking at Microsoft, DataDirect, JSQLConnect and >Opta2000. For the area of code in our app that I benchmarked, the >Microsoft driver was by far the slowest, and Opta2000 was 50% faster. >[2] I posted my results to the list, so they should be in the archive somewhere. > >The "indexed" attribute in the XML repository has no significance to >the OJB runtime; it is there so that table schemas (or DDL) can be >generated from the repository. > >In short, if you have to use the Microsoft driver, it's probably worth >asking around on their forums to see if anyone there has encountered >this issue. > > >Cheers, > >Charles > > >[1] It's advice my employer doesn't actually follow ! >[2] As with all benchmarks, your mileage WILL vary in your app; don't >rely on my comparisons, do your own benchmarks. > > > >>-----Original Message----- >>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >>Sent: 26 February 2004 02:13 >>To: [EMAIL PROTECTED] >>Subject: OJB + MSSQL sp_executesql problem >> >> >>Hello everyone ! >> >>I have been using OJB for the last several months in several projects >>and have had no problems at all - great product ! However, in the last >>project, I am having problems with the sp_executesql statement that is >>generated by OJB in the queries. It apparently is a problem with my >>mssql >> >>installation, but I am looking for a workaround without having to do >>anything major with the database. >> >>A couple of lines about my db setup: >>1. OJB rc5, jdk 1.4.2 >>2. mssql database - the table that I am having a problem with is quite >>large - 60 million rows. >> >>The problem is that it takes about 20 seconds to run a query to >>retrieve a >> >>record from the database. When I look at the generated code, the query >>is of the form 'sp_executesql SELECT ... from ... WHERE DCN=......'. >>If I run >> >>the query directly in the MSSQL query analyzer, it takes just as long >>(so >> >>apparently the problem is not with anything in OJB). However, if I >>take the query out of the 'sp_executesql..' statement, and run it as a >>regular select query (e.g. only "SELECT ... from ... WHERE >>DCN=......), it takes less than a second to run. I investigated my >>set up and it appears that for some reason mssql messes up the indexes >>on the table - instead of using the clustered index that is specified >>on the field on which I specify the WHERE condition (e.g. 'DCN' in the >>sample query snippet above), it uses the index on the primary key >>(e.g. the Id field). When the >> >>query is run as a literal (second example below) - everything works >>like a >> >>charm and mssql selects the correct index. >> >>As you can see below, I thought that if I indicated in the repository >>that >> >>the DCN column was indexed, it would resolve the issue; however, the >>indexed="true" property does not seem to change the generated >>sp_executesql statement in any way. >> >>So, my question is, is there a way to make mssql use the right index >>with some property in the configuration (e.g. that would possibly pass >>an index >> >>hint to the query) ? Has anyone else encountered similar behaviour ? >> >>Sample code: >> >>The repository-user.xml >>... >> <class-descriptor >>class="com.divintech.cigna.printrejects.valueobjects.ScanClaimVO" >>table="Claim_Export_Summary"> >> <field-descriptor id="1" name="id" column="ID" >>jdbc-type="INTEGER" >> >>access="readonly" autoincrement="true" primarykey="true"/> >> <field-descriptor id="2" name="dcn" column="DCN" >>jdbc-type="CHAR" >>access="readonly" indexed="true" /> >> <field-descriptor id="3" name="batchName" >>column="Batch_Name_IA" >>jdbc-type="VARCHAR" access="readonly" /> >> <field-descriptor id="4" name="exportDate" >>column="CreateDate" >>jdbc-type="DATE" access="readonly"/> >> <field-descriptor id="5" name="boxNumber" column="Box_No" >>jdbc-type="VARCHAR" access="readonly"/> >> </class-descriptor> >> >>The generated query (runs for about 15 - 20 seconds): >> >>exec sp_executesql N'SELECT >>A0.DCN,A0.Batch_Name_IA,A0.CreateDate,A0.Box_No,A0.ID FROM >>Claim_Export_Summary A0 WHERE A0.DCN = @P1 ', N'@P1 >>nvarchar(4000) ', >>N'01211104500002' >> >>Literal query (runs for less than a second): >>SELECT A0.DCN,A0.Batch_Name_IA,A0.CreateDate,A0.Box_No,A0.ID FROM >>Claim_Export_Summary A0 WHERE A0.DCN = '01211104500002' >> >>Regards, >> >>Alex Kotchnev >>Developer / Systems Analyst >>Diversified Information Technologies >> >>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >>CONFIDENTIALITY NOTICE: If you have received this e-mail in error, >>please immediately notify the sender by e-mail at the address shown. >> This e-mail >>transmission may contain confidential information. This information >>is intended only for the use of the individual(s) or entity to whom it >>is intended even if addressed incorrectly. Please delete it from your >>files if you are not the intended recipient. Thank you for your >>compliance. >>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >> >> >> > > >___________________________________________________________ >HPD Software Ltd. - Helping Business Finance Business Email terms and >conditions: www.hpdsoftware.com/disclaimer > > > >--------------------------------------------------------------------- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
