Database Performance: Large execution time.

[Former Member]

Hi,

I have TPC-h database of size 1GB. I am running a nested query having multiple joins between 5 tables and a group by and order by on three attributes. It took around 1 hour for this query to get executed (also it was fired for the point which can be considered as the center of selectivity range.).

Following is the query:

select
	supp_nation,
	cust_nation,
	l_year,
	sum(volume)
from
	(
		select
			n1.n_name as supp_nation,
			n2.n_name as cust_nation,
			YEAR (l_shipdate) as l_year,
			l_extendedprice * (1 - l_discount) as volume
		from
			supplier,
			lineitem,
			orders,
			customer,
			nation n1,
			nation n2
		where
			s_suppkey = l_suppkey
			and o_orderkey = l_orderkey
			and c_custkey = o_custkey
			and s_nationkey = n1.n_nationkey
			and c_nationkey = n2.n_nationkey
			and (
				(n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')
				or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')
			)
			and l_shipdate between '1995-01-01' and '1996-12-31'
			and o_totalprice <= 246835
			and c_acctbal <= -422.16
	)as shipping
group by
	supp_nation,
	cust_nation,
	l_year
order by
	supp_nation,
	cust_nation,
	l_year

Moreover it has been observed that such types of queries viz., nested, sub queries, aggregation are taking very high amount of time for execution as compared to other databases. The above mentioned query took only 18 seconds to execute in ORACLE server.

The machine configuration and the database configuration are as follows:

Machine:

64-bit Windows Vista operating System.

RAM: 8GB.

CPU: 3.0 GHZ

Database:

Data Area: No. of Volumes: 1, Size of Volume: 4GB (as mentioned on wiki, for 10 GB database 4 volumes must be assigned.)

Log Area: Volume: 1, Size: 1GB

Data and Log are on same disk.

Caches:

I/O Buffer Cache: 1 GB

Data Cache: 1 GB

Catalog Cache: 30 MB

Parameters:

CacheMemorySize - 131072

ReadAheadLobThreshold- 3000

Also, we have set other optimizer parameters as required and recommended by SAPDB. Even then I am not able get better performance.

How to increase or better the performance? Is there any other parameter that remains to be set?

[lbreddemann · Accepted solution]

> I have TPC-h database of size 1GB. I am running a nested query having multiple joins between 5 tables and a group by and order by on three attributes. It took around 1 hour for this query to get executed (also it was fired for the point which can be considered as the center of selectivity range.).

> Moreover it has been observed that such types of queries viz., nested, sub queries, aggregation are taking very high amount of time for execution as compared to other databases. The above mentioned query took only 18 seconds to execute in ORACLE server.

Such general statements are usually total crap.

MaxDB is running for many SAP customer and SAP internally in many installations - even for BI systems.

We don't know your Oracle server, we don't know the execution plans - so there's nothing to tell why it may be the case here.

> Data Area: No. of Volumes: 1, Size of Volume: 4GB (as mentioned on wiki, for 10 GB database 4 volumes must be assigned.)

It's a rule of thumb - having just one volume is a rather bad idea since you don't get parallel I/O with that.

> Log Area: Volume: 1, Size: 1GB

> Data and Log are on same disk.

Although this is irrelevant for the query performance it's nonsense in productive environments and a performance killer as well.

> I/O Buffer Cache: 1 GB

> Data Cache: 1 GB

Why don't you allow more Cache ?

> Catalog Cache: 30 MB

What for? Do you understand the catalog cache in MaxDB?

It's a per session setting...

> Also, we have set other optimizer parameters as required and recommended by SAPDB. Even then I am not able get better performance.

Can you be more specific here?

What MaxDB version are you using? What parameter settings do you use?

> How to increase or better the performance? Is there any other parameter that remains to be set?

How about showing us the execution plan for the statement and the index structure?

How should we know what MaxDB does here that takes so much time?

Did you have the DBanalyzer running while the query ran?

TPC-H is a benchmark for ad-hoc, decision making support: did you enable any of the BI feature pack features of MaxDB? What about prefetching? What about table clustering, column compression, star join optimization ...?

All in all - you left us here with "MaxDB is slower than Oracle" and nothing to work on.

That's not useful in any way.

Want some answers - provide some information!

regards,

Lars