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]
