Hi Martin,
I now need 2 minutes to restore my test scenario. I just checked Oracle and
Ms-Sql Server. It is the same computer. Oracle is running on Linux and MaxDB
and Sql Server are running on w2k pro.
For restoring the same backup date from an identical client to nearly identical
network server I got:
MaxDB: 2 minutes
Oracle: 30 seconds
Sql-Server: 40 seconds
In comparison to oracle this is a ratio of 1:4 (30 seconds : 2 minutes). This
is acceptable for us.
I now still use the jdbc solution with prepared insert statements. Maybe we'll
try the batch update but not sure if this will speed up the restore too much.
The loader is no option for us. We need to have the file that holds the backup
on the client and not on the server. The loader needs the data always on the
server. So this is not practicable for us. We also don't want to implement a
solution only for one dbms. We have to support more then maxdb.
Since 7.6 is not official supported by us I didn't try to find out why the
dbanalyzer crashes always.
Kind regards,
Ronald
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 02, 2005 1:55 PM
To: [email protected]
Cc: Heckendorff. Ronald
Subject: RE: poor insert performance
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]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
