RE: Performance problems ? witch parameter to change ?

> -----Message d'origine-----
> De : Florian Schmitz [mailto:fschmitz@xxxxxxxxxx]
> Envoyé : vendredi 28 janvier 2005 10:11
> À : 'Fabrice Bourdel'; maxdb@xxxxxxxxxxxxxxx
> Objet : AW: Performance problems ? witch parameter to change ?
>
>
> Hello Fabrice,
>
> for the Query-Problem, try doing a Statistics Update in DBMGui
> via Tuning ->
> Optimizer Statistics and set the sample value to 50%.
> This should eliminate the table scan.
> If it doesn't help send an explain-output of the query (explain select
> blahblah).
>
> for the insertion-problem : i'm still investigating in a similiar problem
> over here. :-)
>
> Regards,
>
> Flo
>
> > -----Ursprüngliche Nachricht-----
> > Von: Fabrice Bourdel [mailto:fabrice.bourdel@xxxxxxx]
> > Gesendet: Freitag, 28. Januar 2005 06:46
> > An: maxdb@xxxxxxxxxxxxxxx
> > Betreff: 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=fschmitz@xxxxxxxxxx

hello,

yes, it worked ! but before doing an update stat :
i had updated the statistics, but i seems that is has been commit later ;
and yet, i retried (before updating stats) and the query took 0.125s !

I updates stats the first time through the "Database Manager" and to your
response, i quit the app and stoped and restated the database many time.

i tried to init the stats by achieving an [update stat .. sample 1 rows] but
i cannot get the poor results "back" to check. (but that not a problem)

Thanks.
bye.



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