I just ran a little test on this query.
My test, wipe out all the data. I am inserting into 3 tables when
a user takes a quiz. Each had close to or over a million rows. So,
I wiped 'em out. Than ran it again. Works like a charm.
Something in the insert is making it take minutes to commit. And, I
think it is the @@IDENTITY.
I have to take a look at the code for the PlatformMsSQLServerImpl.
But from looking at the sql profiler I can see that when it does an
insert it also selects the "@@IDENTITY AS id" so that it can return
the id to the newly created object.
For some reason, that @@IDENTITY part takes minutes when there are
close to a million rows. This is probably a SQL Server problem and
not an OJB problem. But, does anyone have an idea on how I could
get around this problem?
Thanks for all the previous help on this issue.
Charlie
Armin Waibel said the following on 10/18/2005 8:34 PM:
Hi Charlie,
class QA has a 1:1 reference to Q and QR, the auto-update attributes
are set 'false' for both (correct me if I'm wrong), thus OJB will only
"link" the FK's to QR and Q and method
> ojbRepository.insertObject( questionAnswer );
have to do the rest (update/insert QR and Q). Maybe the problem is
caused by this method. I can't believe that this is an issue caused by
OJB (but maybe I'm wrong).
If you really use ConnectionFactoryPooledImpl then skip this
paragraph, because attribute 'removeAbandoned' is only supported by
ConnectionFactoryDBCPImpl.
You set whenExhaustedAction="2" (grow) and removeAbandoned="true" in
your jdbc-connection-descriptor. Especially the second attribute will
cause significant overhead and it's not recommended to use this
setting in production environment. If "leaking connections" occur,
it's recommended to fix the code instead of using this attribute ;-)
Do have any problems with exhausted connection- or PB-pools (leaking
connections and PB instances) when running your tests?
Which version of OJB do you use?
I'm (nearly) sure that it's not possible to reproduce your issue in
OJB test-suite (there are many tests using 1:1 references and I never
observed such a behavior). So (as Tom suggested in a previous post)
use a profiling tool to find the hot spot.
regards,
Armin
Charles Harvey III wrote:
I use the PB API.
ConnectionFactoryClass = ConnectionFactoryPooledImpl
ObjectCacheClass = ObjectCacheDefaultImpl
Here is the block of code that does the most damage:
while( questionIter.hasNext() )
{
Question question = (Question)questionIter.next();
QuestionAnswer questionAnswer = new QuestionAnswer();
questionAnswer.setQuizReport( quizReport );
questionAnswer.setQuestion( question );
Integer questionNumber = question.getQuestionNumber();
questionAnswer.setQuestionAnswerValue( new Integer(
(String)questionMap.get( "question" + questionNumber ) ) );
ojbRepository.insertObject( questionAnswer );
_log.info( "inserted QA id: " + questionAnswer.getId() );
}
I do not extend any of the pluggable classes. the "ojbRepository"
class is my wrapper class for ojb methods. It does the standard ojb
stuff right from the website, nothing different at all.
And, below is my repository.xml file.
Thanks so much for all the help, it is much appreciated.
Charlie
------------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!-- defining entities for include-files -->
<!DOCTYPE descriptor-repository PUBLIC
"-//Apache Software Foundation//DTD OJB Repository//EN"
"http://db.apache.org/ojb/repository.dtd"; [
]>
<descriptor-repository version="1.0" isolation-level="read-uncommitted">
<!-- Development JDBC Connection. -->
<jdbc-connection-descriptor
jcd-alias="development"
default-connection="false"
platform="MsSQLServer"
jdbc-level="4.0"
driver="net.sourceforge.jtds.jdbc.Driver"
protocol="jdbc"
subprotocol="jtds"
dbalias="sqlserver://10.4.3.10:1433/CCSQuiz"
username="ccsquizuser"
password="ccsquizuser"
eager-release="false">
<connection-pool
maxActive="100"
maxIdle="5"
maxWait="200"
minEvictableIdleTimeMillis="1000000"
timeBetweenEvictionRunsMillis="600000"
validationQuery=""
removeAbandoned="true"
whenExhaustedAction="2"/>
<sequence-manager
className="org.apache.ojb.broker.util.sequence.SequenceManagerNativeImpl">
</sequence-manager>
</jdbc-connection-descriptor> <!-- include user defined
mappings here -->
<!-- TABLE CATEGORY -->
<class-descriptor
class="com.alloyinc.quiz.bean.Category"
table="QUIZ_CATEGORY">
<field-descriptor
name="id"
column="category_id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor
name="categoryDesc"
column="category_desc"
jdbc-type="VARCHAR"
access="readwrite"/>
</class-descriptor>
<!-- TABLE QUIZ -->
<class-descriptor
class="com.alloyinc.quiz.bean.Quiz"
table="QUIZ">
<field-descriptor
name="id"
column="quiz_id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor
name="quizDisplayTitle"
column="quiz_display_title"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="quizDisplayTeaser"
column="quiz_display_teaser"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="quizIntroText"
column="quiz_intro_text"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="imagePath"
column="image_path"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="archiveImage"
column="archive_image"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="numberOfPages"
column="number_of_pages"
jdbc-type="INTEGER"
access="readwrite"/>
<field-descriptor
name="publishStatus"
column="publish_status"
jdbc-type="BIT"
access="readwrite"/>
<field-descriptor
name="dateCreated"
column="date_created"
jdbc-type="DATE"
access="readwrite"/>
<field-descriptor
name="timesTaken"
column="times_taken"
jdbc-type="INTEGER"
access="readwrite"/>
<field-descriptor
name="scoreType"
column="score_type"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="channelName"
column="channel_name"
jdbc-type="VARCHAR"
access="readwrite"/>
<collection-descriptor
name="questions"
element-class-ref="com.alloyinc.quiz.bean.Question"
orderby="questionNumber"
sort="ASC"
proxy="true">
<inverse-foreignkey
field-ref="quizId"/>
</collection-descriptor>
</class-descriptor>
<!-- TABLE QUESTION -->
<class-descriptor
class="com.alloyinc.quiz.bean.Question"
table="QUIZ_QUESTION">
<field-descriptor
name="id"
column="question_id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor
name="questionDesc"
column="question_desc"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="questionType"
column="question_type"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="quizId"
column="quiz_id"
jdbc-type="INTEGER"
access="anonymous"/>
<reference-descriptor
name="quiz"
class-ref="com.alloyinc.quiz.bean.Quiz"
auto-update="false">
<foreignkey
field-ref="quizId"/>
</reference-descriptor>
<field-descriptor
name="questionNumber"
column="question_number"
jdbc-type="INTEGER"
access="readwrite"/>
<field-descriptor
name="pageNumber"
column="page_number"
jdbc-type="INTEGER"
access="readwrite"/>
<collection-descriptor
name="questionChoices"
element-class-ref="com.alloyinc.quiz.bean.QuestionChoice"
orderby="id"
sort="ASC"
proxy="true">
<inverse-foreignkey
field-ref="questionId"/>
</collection-descriptor>
</class-descriptor>
<!-- TABLE QUESTION_CHOICE -->
<class-descriptor
class="com.alloyinc.quiz.bean.QuestionChoice"
table="QUIZ_QUESTION_CHOICE">
<field-descriptor
name="id"
column="question_choice_id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor
name="questionChoiceDesc"
column="question_choice_desc"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="questionChoiceValue"
column="question_choice_value"
jdbc-type="INTEGER"
access="readwrite"/>
<field-descriptor
name="questionId"
column="question_id"
jdbc-type="INTEGER"
access="anonymous"/>
<reference-descriptor
name="question"
class-ref="com.alloyinc.quiz.bean.Question"
auto-update="false">
<foreignkey
field-ref="questionId"/>
</reference-descriptor>
</class-descriptor>
<!-- TABLE QUESTION_ANSWER -->
<class-descriptor
class="com.alloyinc.quiz.bean.QuestionAnswer"
table="QUIZ_QUESTION_ANSWER">
<field-descriptor
name="id"
column="question_answer_id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor
name="questionAnswerValue"
column="question_answer_value"
jdbc-type="INTEGER"
access="readwrite"/>
<field-descriptor
name="questionId"
column="question_id"
jdbc-type="INTEGER"
access="anonymous"/>
<reference-descriptor
name="question"
class-ref="com.alloyinc.quiz.bean.Question"
auto-update="false">
<foreignkey
field-ref="questionId"/>
</reference-descriptor>
<field-descriptor
name="quizReportId"
column="quiz_report_id"
jdbc-type="INTEGER"
access="anonymous"/>
<reference-descriptor
name="quizReport"
class-ref="com.alloyinc.quiz.bean.QuizReport"
auto-update="false">
<foreignkey
field-ref="quizReportId"/>
</reference-descriptor>
</class-descriptor>
<!-- TABLE QUIZ_REPORT -->
<class-descriptor
class="com.alloyinc.quiz.bean.QuizReport"
table="QUIZ_REPORT">
<field-descriptor
name="id"
column="quiz_report_id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor
name="quizId"
column="quiz_id"
jdbc-type="INTEGER"
access="anonymous"/>
<reference-descriptor
name="quiz"
class-ref="com.alloyinc.quiz.bean.Quiz">
<foreignkey
field-ref="quizId"/>
</reference-descriptor>
<field-descriptor
name="dateTimeTaken"
column="date_time_taken"
jdbc-type="TIMESTAMP"
access="readwrite"/>
</class-descriptor>
<!-- TABLE QUIZ_REPORT_SCORE -->
<class-descriptor
class="com.alloyinc.quiz.bean.QuizReportScore"
table="QUIZ_REPORT_SCORE">
<field-descriptor
name="id"
column="quiz_report_score_id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor
name="resultTypeId"
column="result_type_id"
jdbc-type="INTEGER"
access="anonymous"/>
<reference-descriptor
name="resultType"
class-ref="com.alloyinc.quiz.bean.ResultType">
<foreignkey
field-ref="resultTypeId"/>
</reference-descriptor>
<field-descriptor
name="quizReportId"
column="quiz_report_id"
jdbc-type="INTEGER"
access="anonymous"/>
<reference-descriptor
name="quizReport"
class-ref="com.alloyinc.quiz.bean.QuizReport">
<foreignkey
field-ref="quizReportId"/>
</reference-descriptor>
</class-descriptor>
<!-- TABLE RESULT_TYPE -->
<class-descriptor
class="com.alloyinc.quiz.bean.ResultType"
table="QUIZ_RESULT_TYPE">
<field-descriptor
name="id"
column="result_type_id"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
access="readonly"/>
<field-descriptor
name="header"
column="header"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="longDesc"
column="long_desc"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="imagePath"
column="image_path"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="pointRange"
column="point_range"
jdbc-type="VARCHAR"
access="readwrite"/>
<field-descriptor
name="quizId"
column="quiz_id"
jdbc-type="INTEGER"
access="anonymous"/>
<reference-descriptor
name="quiz"
class-ref="com.alloyinc.quiz.bean.Quiz">
<foreignkey
field-ref="quizId"/>
</reference-descriptor>
</class-descriptor>
</descriptor-repository>
------------------------------------------------------------------------------------
Armin Waibel said the following on 10/18/2005 1:50 PM:
Hi Charlie,
> Should I attach my repository.xml? What should I be looking for
that would
> be making this so slow? I've never seen anything like this.
yep, the metadata mapping for TestCalculateLogic.java (and all
referenced objects) would be helpful.
Which API do you use (PB-api, ODMG-api)?
Did you extend one or more of the pluggable classes (e.g. RowReader,
...)?
Which cache, connection-factory implementation do you use?
regards
Armin
Charles Harvey III wrote:
Ok. I did that. I setup a JDBC method and did everything via JDBC.
(Been a while since I've had to do that, no wonder I use ORM
software.)
3710 INFO (TestCalculateLogic.java:37) :: *** testCommitAnswers() ***
31095 INFO (CalculateLogic.java:186) :: inserted QuizReport id:
537991
153408 INFO (CalculateLogic.java:301) :: inserted QuestionAnswer
id: 3168866
327598 INFO (CalculateLogic.java:301) :: inserted QuestionAnswer
id: 3168867
2158 INFO (TestCalculateLogic.java:81) :: ***
testCommitAnswersJDBC() ***
3322 INFO (CalculateLogic.java:274) :: inserted QuizReport id: 537992
4673 INFO (CalculateLogic.java:301) :: inserted QuestionAnswer id:
3168869
6004 INFO (CalculateLogic.java:301) :: inserted QuestionAnswer id:
3168870
HUGE difference between the two in the log time on the left. What is
happening?
Should I attach my repository.xml? What should I be looking for
that would
be making this so slow? I've never seen anything like this.
Charlie
Thomas Franke said the following on 10/13/2005 3:26 AM:
Charles Harvey III wrote:
Any ideas? Could it be the data structure? A setting in OJB?
Something
wrong with the database (MSSQL)?
How about to try your statement without OJB e.g. only with JDBC?
So you could
figure out if it is an OJB or another problem. Maybe you are using
the wrong driver?
Regards,
Thomas
---------------------------------------------------------------------
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]
---------------------------------------------------------------------
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]
