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]
