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
...
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'0121110452'
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 = '0121110452'
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.
