Hi Charles, thanks for your quick answer. Primary key and indices setted correctly. It's correct, that we are using the Microsoft SQL 2000 JDBC driver. We have made experiences with jtds and the performance of the Microsoft JDBC driver was in many cases better than jdts. We have a look for your link.
Axel > -----Urspr�ngliche Nachricht----- > Von: Charles Anthony [mailto:[EMAIL PROTECTED] > Gesendet: Dienstag, 21. Oktober 2003 15:56 > An: 'OJB Users List' > Betreff: RE: Very bad performance with big Tables > > > Hi Axel, > > This may be a completely stupid question, and irrelevant, but > do you have an index created on column hisOID ? > If not, Is hisOID actually marked as being a primary key > (i.e. does it have a primary key constraint) ? > > The syntax you specify in the first example is *not* > generated by OJB, actually, but by the JDBC driver. OJB > creates a PreparedStatement - which in most cases is more > perfomant that executing a SQL string directly. All the @P1 > @P1 etc bits are generated by the JDBC driver (which I am > assuming is the Microsoft JDBC driver). > > Finally, have you tried using a different JDBC driver ? My tests > http://www.mail-archive.com/[EMAIL PROTECTED]/msg03163.html > show that the MS driver is not very good... I'd test using a > different driver to see if that makes a difference. > > Cheers, > Charles. > > > > -----Original Message----- > > From: Axel Hohaus [mailto:[EMAIL PROTECTED] > > Sent: 21 October 2003 14:51 > > To: [EMAIL PROTECTED] > > Subject: Very bad performance with big Tables > > > > > > I have a problem with big tables and MS SQL Server. The > table has more > > than 5 million records. Select and Update statements are > very slow. I > > think the problem is the statement with parameters > generated by ojb. > > The statement with the parameter syntax runs almost 6 > seconds, without > > it just takes mills. > > > > Has someone also recognized is problem. > > Does someone knows why this syntax is so slow and has some > suggestions > > how to fix it. Is there an easy way to change the statement > > generation, > > if there are no other advices. > > > > Example > > > > First takes 6 sec > > > > exec sp_executesql N'UPDATE History SET > > [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],hisLa > > stModifier > > [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] > > 7,hisAttac > > [EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],hisO > > [EMAIL PROTECTED] > > 11 WHERE hisOID = @P12 ', N'@P1 datetime ,@P2 nvarchar(4000) ,@P3 > > datetime ,@P4 nvarchar(4000) ,@P5 nvarchar(4000) ,@P6 > nvarchar(4000) > > ,@P7 datetime ,@P8 nvarchar(4000) ,@P9 nvarchar(4000) ,@P10 > > nvarchar(4000) ,@P11 nvarchar(4000) ,@P12 nvarchar(4000) ', > > 'Jul 28 2003 > > 5:17:32:000PM', N'ibrahimk', 'Okt 20 2003 5:29:29:777PM', > N'SYSTEM', > > N'SV: xxx', N'', 'Jul 28 2003 5:17:31:000PM', NULL, N'90', N'1080', > > N'5CCFCCE8-B144-11D7-A860-0007E90F2D57', > > N'FC7C1593-9D39-4D21-9E02-B86F91DCCDD7' > > > > Second takes just mills > > > > exec sp_executesql N'UPDATE History SET > hisLockTimestamp=''Jul 28 2003 > > > 5:17:31:000PM'',hisLockUser=''ibrahimk'',hisLastModified=''Jul 28 2003 > > 5:17:31:000PM'',hisLastModifier=''SYSTEM'',hisShortDescription=''SV: > > xxx'',hisDescription='''',hisCreation=''Jul 28 2003 > > 5:17:31:000PM'',hisAttachmentOID='''',hisOrderStatusOID=''90'' > > ,hisHistor > > > yTypeOID=''1080'',hisOrderOID=''5CCFCCE8-B144-11D7-A860-0007E90F2D57'' > > WHERE hisOID = ''FC7C1593-9D39-4D21-9E02-B86F91DCCDD7''' > > > > with kind regards > > > > Markus Denkinger > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > This email and any attachments are strictly confidential and > are intended solely for the addressee. If you are not the > intended recipient you must not disclose, forward, copy or > take any action in reliance on this message or its > attachments. If you have received this email in error please > notify the sender as soon as possible and delete it from your > computer systems. Any views or opinions presented are solely > those of the author and do not necessarily reflect those of > HPD Software Limited or its affiliates. > > At present the integrity of email across the internet cannot > be guaranteed and messages sent via this medium are > potentially at risk. All liability is excluded to the extent > permitted by law for any claims arising as a re- sult of the > use of this medium to transmit information by or to > HPD Software Limited or its affiliates. > > > > --------------------------------------------------------------------- > 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]
