Performance problems ? witch parameter to change ?

hi,

I must have a problem with the database parameters :

I have tested an identical query againts maxdb 7.5.0.19 and
MSDE2K (= SQL Server2000) : 182ms on MSDE, 11.124s on MaxDb !
more than 61 times microsoft database !

MaxDb : 7.5.0.19, on Linux Debian, machine with a 900Mhz proc
MSDE2k, on Win2k, machine with a 900Mhz proc too (quite similar machine)
(that's develpement machines, not final machines)

Tables are identical, indexes are the same, statistics are
updated, table contains 100 000 rows.

I checked time for MaxDb with SQL Studio, by activating "trace all" and
just executing the query.

The structure :
create table a_b_c_test(
 ch1 int default serial(1),
 ch2 int,
 ch3 char(1),
 primary key(ch1)
)
//
create index t1 on a_b_c_test(ch2)
//
create index t2 on a_b_c_test(ch3)

The query :
select *
from a_b_c_test t1
where ch2 = (select min(ch2) from a_b_c_test t2 where t2.ch3 = t1.ch3)

Is't a correlated query, i know that another form of this query :
select *
from a_b_c_test t1
where (ch2, ch3) = any (select min(ch2), ch3 from a_b_c_test t2 group by
ch3)
takes less time (3.436s instead) but this form isn't accepted on microsoft
database ; that was just for the comparison.

Also, space for data is about 1800Ko, for indexes 2800Ko (checked is the
MSDE
database)

I had a cache size value to 4096 pages (32Mb) i shifted to 8192 pages (64Mb)
but nothing changed in the time taken for the query on MaxDb.

Could you tell me with parameter(s) could help ?

One explication for the time difference :
is saw that Maxdb do first a table scan on table t1, then the subquery for
each line. But on the MSDE, is't totally different : it seem calculate a
hash value on t2.ch3 and calculation min(ch2), do a neested loops/inner join
with table t1, and then filter on t1.ch3 = t2.ch3, seems to be equivalent to
the 2nd query that don't run directly on msde !

***
Also, i think i have also another problem when i am doing lots in insertions
on maxdb : i need to insert monthly 25000 to 30000 ligns of data ; each line
contains mostly text data, but not more then 10 fields. I take a very long
time and the disk while inserting is accessed all the time, maybe a problem
with caching ? i searched and saw that MaxDb is waiting for the log to be
written to disk ; is the log_io_queue param the only param to shift ? the
actual
value is 50 pages ; for the example above, i tryed to shift le value to 100
pages but the insertion is not quicker. Any suggestions ?

Thanks.


-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx