Queries to the sapdb 7.4.3 database become slow over time

[Former Member]

I have an application that performs some complex queries to a table in a sapdb 7.4.3 database. Initially, these queries execute in a few seconds, but after about a day, they can slow down to 10 or 15 minutes. After restarting the database, the queries become fast again. I have not determined any differences in the database before and after restarting, such as number of sessions or database space used.

Does anyone know what the problem is?

[Former Member]

Here is the query:

declare idMap cursor for select csc.name, cscid id from clusteredserviceconditions csc, serviceconditionrollup scr where csc.name = scr.child and scr.parent in ('Application Systems','Costpoint Application','Database Applications','Database Systems','Enterprise Systems','Files and Print Systems','Messaging Systems','Network Devices','Nextance Application','Web Systems','WebSite SLA') for reuse

declare scMinMaxTimes cursor for select min(sampletime), max(sampletime), name, idMap.id from idMap, d_slastore ss where idMap.id = ss.measuredobjectid and ss.sampletime between '2008-06-13 00:00:00' and '2008-06-13 09:32:28' group by name, idMap.id for reuse

select ss.status, ss.statuschange, ss.sampletime, scMinMaxTimes.name from d_slastore ss, scMinMaxTimes where ss.measuredobjectid = scMinMaxTimes.id and ss.sampletime between '2008-06-13 00:00:00' and '2008-06-13 09:32:28' and (statuschange != 0 or ss.sampletime = scMinMaxTimes.expression1 or ss.sampletime = scMinMaxTimes.expression2) order by name, sampletime

Here's the D_SLA table definition:

CREATE TABLE "FZE_GRP1"."D_SLASTORE"

(

"SAMPLETIME" Timestamp NOT NULL,

"EXPIRATION" Timestamp,

"INTERVAL" Integer,

"MEASUREDOBJECTID" Fixed (10,0) NOT NULL,

"INSERTTIMESECONDS" Integer,

"STATUS" Integer NOT NULL,

"STATUSCHANGE" Integer,

"CONSECUTIVEDOWNMINUTES" Integer,

"RESPONSETIME" Fixed (9,2) NOT NULL,

"CURRENTVALUE" Varchar (64) ASCII,

"DETAIL" Varchar (1000) ASCII,

PRIMARY KEY ("SAMPLETIME", "MEASUREDOBJECTID")

)

[lbreddemann]

Hi Dick,

is it just me or are you really abusing the cursor facility here to do what should be done via views instead?

Besides the fact that you neither provided all table definitions nor any test data (the tables aren't empty are they?), I can say the following:

- the search condition on the D_SLA table

where idMap.id = ss.measuredobjectid and ss.sampletime between '2008-06-13 00:00:00' and '2008-06-13 09:32:28'

is not a very good supported by the primary key of D_SLA (sampletime, measuredobjectid).

Only sampletime will be evaluated to reduce rows from D_SLA since the equijoin condition on measuredobjectid cannot be applied as an access predicate after a KEY RANGE SCAN.

Therefore you're likely doing the join "backwards" here.

- even worse, with your cursor in cursor in cursor approach you force the database to build up temporary result sets.

These are not indexed - so it's always a SCAN on those temporary row sets.

Building up those resul tsets takes time. Removing them again, will increase your I/O.

If this is the SQL code running on your ever crashing server - well, it's time to rethink this 'design'.

Think of views, think of single column indexes, think of replacing 'sampletime' with a shorter sample_id and reference a 'samples' table.

Perhaps also think of db functions for easier lookups...

KR Lars

[Former Member]

I'm not sure if upgrading to a newer version is an option for me. Should I still provide you with the answers to your latest questions?

[lbreddemann]

I don't see why an upgrade wouldn't be an option - unless you're keeping a museum of server hard and software the old version is of no use any longer.

If you have answers to the questions - post them!

KR Lars

[Former Member]

Lars,

Thank you for your input. I did occasionally see more than one active task during the query, but most of the time there was just one. The state was always the same - "IO Wait (R)".

I did notice an increase in the paging on the server while the query was being executed. The volume of data does not change between the time the performance is good and when it is bad. The number of rows in the table is about 2,000,000 rows. There was also an increase in disk reads while the query was being executed. Just prior to executing the query, disk reads were low.

[lbreddemann]

Hi Dick,

well we won't get further here without more information (-> upgrade to MaxDB 7.6.03 for that!)

- What statement takes seconds first and than minutes?

If all statements are slow - take the most important ones (important to your business).

- How do the tables/indexes look like? (DDL statements!)

- What DB Analyzer Warnings do you get?

Without this information we basically cannot say anything.

Kind regards,

Lars

[Former Member]

The task is in an "IO Wait (R)" state. This is Windows Server 2003 SP2 (Standard Edition).

[lbreddemann]

HI Dick,

THE task? Is there just one session active?

What about paging on the DB Server?

Does the datavolume change the queries should handle? E.g. when you start and performance is OK, you only work on 1000 rows but after a while when performance becomes lame you work on 1000000 rows?

Does - perhabs - the read I/O performance change much over the time? From Storage point of view MaxDB does random reads that may lead to problems with storage site caches.

KR Lars

P.S.

Upgrade to 7.6.03, enable time measurement and activate the DB Analyzer - than we should be able to tell more about what happens here.

[lbreddemann]

Hi Dick,

besides the fact, that 7.4.3 is now desupported for a looooong time this sounds interesting.

When the queries slow down so much - what are the task states? (use 'x_cons <DBSID> show active' to find out).

Could it be that the database server is poorly configured and begins to swap memory a lot after some time of usage?

What OS are we talking about anyhow?

KR Lars