Hi Ronald, What's the total insert time -after the fix- compared to SQLServer? which method was finally used? programatic or loader? Did you try again with MAXDB 7.6?
Regards, Martin > In a private communication it was found out, that a wrong trigger > updating the entire table was the reason to the bad performance. The > insert worked seamlessly when this was corrected. > > Alexander Schröder > SAP DB, SAP Labs Berlin > > -----Original Message----- > From: Heckendorff. Ronald [mailto:[EMAIL PROTECTED] > Sent: Dienstag, 1. November 2005 15:17 > To: [email protected] > Subject: poor insert performance > > Hi all, > we have a performance problem with our tables during inserts. > > We have one table that has a lot of rows. For testing purposes I used a > backup with approximately 4250 rows. On a productive system we will have > a lot more. All rows are unique. That means we only need inserts and no > updates. > > The table has a primary key, a unique index and three other indices. It > has 32 columns, most of them varchar with a size between 12 and 256. The > values for the primary keys are created by a sequence (under the > responsibility of maxdb). > > Our tool that inserts the rows into the table uses jdbc. It only > performs inserts and no updates (for this kind of table). Now I need 2 > hours to insert those rows. On mssql the tool needs 30 seconds. It > doesn't matter if I use a prepared statement or a batch insert. > > I checked if it is possible to use the loader to get better performance > metrics. In this way I can import those 4250 rows within 30 minutes. It > is better then 2 hours but still not acceptable. > > I use maxdb 7.5.0.30 on a w2k pro with 2 GB RAM. The data area has a > size of 6 GB and the Log area a size of 2 GB. I tried 7.6.0.12 too, but > the dbanalyzer doesn't work on that installation. It always crashes :-(. > > Now I tried several things. > I checked the dbanalyzer output. I got the following messages: > * W3 Log queue overflows: 329, configured log queue pages: 50 > CON: LQ_Overflows > 0 > VAL: 329 > 0 > * W3 Lock list escalations: 58 > CON: LocklEscalations > 0 > VAL: 58 > 0 > * W3 Catalog cache hitrate (SQL Pages) 74.41%, 851 of 3325 accesses > failed > CON: Cat_Hit < 80 && ( SQL_Cmds ) > INTERVAL > VAL: 74.41 < 80 && ( 398 ) > 10 > > I changed the kernel parameters. I use CACHE_SIZE 131072 (2^17) pages. > The page size is 2^13 bytes. > The parameter LOG_IO_QUEUE has now the value 350. > > No changes. > > I changed the isolation level (0, 1, 2, 3). I saw no differences. > > I changed the connection method of the user to be exclusive. The loader > crashes if this is the case :-(. > > I disabled the indices -- no change. > > I reduced the size of the table. Some rows contain only NULL values > within the whole data file. Therefore I removed the rows from the table > definition. Nothing changed, still 30 minutes for loading the data. > > If I check the activity of the database after the import I see that > there are: > -updates > * 18117861 row reads > * 18117861 row changes > -inserts: > * 9063161 rows added > > I don't understand why maxdb updates and inserts so many rows if it has > to insert 4250 unique rows. > > What can we do for getting acceptable metrics for our database restore? > > Kind regards, > Ronald > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
