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]
