Thread: Re: [HACKERS] Serious performance problem

At 11:49 30/10/01 +0100, you wrote:
>On Tue, 30 Oct 2001, Jean-Michel POURE wrote:
>
> > Other examples? Maybe I can help you.
>The only thing I could send you would be the whole database structure and
>the queries I performed.  I´m not allowed to publish confident medical data.
>If you think that you could make serious performance tests without living
>data I would be happy to send you this stuff.
>
>Kind regards

Hello Andreas,

Sorry, I just read your email.
The database structure is fine for me.
I don't need data. Send me a couple of queries.

Do you have a Windows desktop?
If so, please install pgAdmin II from http://www.pgadmin.org.

Kind regards,
Jean-Michel POURE

>thanks for your kind offer to help!
You are welcome.

>Just to make clear the situation once more:  I´ve got hints on the
>general list about how to change my database application to perform
>better by using extra tables and indices.  I´m also able to do some
>database programming myself, even if I´m not very experienced.  But
>the problem is different:  There is an existing database application
>which has absolutely no performance problems on MS SQL server.  If
>I just test the simplest query and it takes orders of magnitude more
>than something with the server is wrong.
>Moreover chances are low that for this reason changes in the database
>existing application are accepted.  What if the MS-SQL server would
>have performance problems and it comes to tuning of the code there
>and we are at the end of speed optimations at our PostgreSQL server.
>There is a binary administrative decision:  Yes or no. If the same
>database application does perform this badly we can *NOT* use PostgreSQL.
>The cost of development would be much higher than a second M$-SQL
>server license.
Have you done tests on MS SQL with xx million rows. How much data are you
going to have in a single database?

>There is a binary technical decission:  Yes or no.  If there is a
>need for at least one additional table for each query of our application
>to perform in a comparable manner than we are lost because we are
>unable to do that for each new query.
Yes.This is what multi-dimensional databases do.$
This time, we will do it "by hand" in PostgreSQL.
With good naming conventions, this will be clean.

>The reason for the performance lack is also clear:  It is the different
>use of indexes in PostgreSQL.  This would have the consequence of a
>linear scaling of performance compared to the log(n) scaling when
>using indexes strictly.  So chances are high that we will run into
>performance trouble again even if we now could cope with the problems.

Beware PHP native MS SQL driver do not work well.

> > Do you have a Windows desktop?
>No, I havn´t?
> > If so, please install pgAdmin II from http://www.pgadmin.org.
>Sorry, I see no relation to the question.

This is for programming in PL/pgSQL and administrating PostgreSQL .
Without pgAdmin, you will quickly mess-up with objects (tables, views,
triggers).

Best regards,
Jean-Michel POURE

On Tue, 30 Oct 2001, Jean-Michel POURE wrote:

> Have you done tests on MS SQL with xx million rows. How much data are you
> going to have in a single database?
No because I will not have such a database in the next couple of years.
Why should I?  But there are people who claim to do this successfully (not
verified ba myself for sure).

> Yes.This is what multi-dimensional databases do.$
> This time, we will do it "by hand" in PostgreSQL.
> With good naming conventions, this will be clean.
Am I ignorant or why do I fail to see the relation to my original problem
that PostgreSQL performs more than 10 times slower than SQL server for a
*simple* task.  I consider it inacceptable for the simple task but you
are talking about hard tasks.  I´m afraid we are talking about different
things.

> >using indexes strictly.  So chances are high that we will run into
> >performance trouble again even if we now could cope with the problems.
>
> Beware PHP native MS SQL driver do not work well.
Oh please do not remind me on the consequences if my PostgreSQL efforts
would fail.  It would not be PHP.  It would be worse.  It would be the
technique of the same people who wrote MS-SQL server *shrug*.  And not
only this it would mean that *each* web stuff would be done with ASP
stuff because people mean that we should stick to just one web technology
(which is not wrong in principle).  My choice was PostgreSQL + Zope ...

May be I would give MySQL a trial before ...

> This is for programming in PL/pgSQL and administrating PostgreSQL .
> Without pgAdmin, you will quickly mess-up with objects (tables, views,
> triggers).
Huh, than I hope we would get a port of it soon ...
But in the moment I had no problems without it.

Kind regards

        Andreas.