Re: Serious performance problem

I have been thinking about this query, I downloaded all your info and I read
your reply to a previous post.

At issue, you say MSSQL outperforms PGSQL, this may be true for a finite set of
query types, it may even be true for your entire application, but for how long?
What will be the nature of your data next year?

Your query is a prime example of where application optimization needs to
happen. Regardless if MSSQL can currently execute that query quickly, at some
point there will be a volume of data which is too large to process quickly.
This table, you say, is created periodically with a cron job. How hard would it
be to append a couple SQL statements to create a summary table for the high
speed queries?

Personally, I think your approach needs to be modified a bit. The fact that
your query runs well on one SQL database and poorly on another indicates to me
that you will be tied to one database forever. If you use standard database
optimization techniques in your design, you can choose any database by the
whole of important criteria, such as reliability, speed, support,
administration, and price, rather than just speed.

Also, if you use MSSQL you will need to have some version of MS-Windows on
which to run it, that alone indicates to me you will have reliability problems.

"Tille, Andreas" wrote:
>
> Hello,
>
> I discussed a problem concerning the speed of PostgreSQL compared to
> MS SQL server heavily on postgres-general list. The thread starts with
> message
>
> http://fts.postgresql.org/db/mw/msg.html?mid=1035557
>
> Now I tried a snapshot of version 7.2 and got an increase of speed of
> about factor 2. But sorry this is really not enough. The very simple
> test I pointed to in my mail is even much to slow and the issue would
> probably spoil down the whole project which should be a complete open
> source solution and would perhaps and in any M$ stuff. I´ve got under
> heavy preasur from my employer who was talking about the nice world
> of MS .net (while he is using MS-SQL exclusively). To make the thing
> clear the issue is the gal database of infectious diseases in Germany
> runned by the Robert Koch-Institute. So the beast could be of some
> importance for increasing the acceptance of PostgreSQL and Open Source
> in the field of medicine which is generally known for the money which
> is involved in. So I really hope that some skilled programmers would
> be able to find a good way to solve the performance issue perhaps by
> just profiling the simple query
>
> SELECT Hauptdaten_Fall.MeldeKategorie, Count(Hauptdaten_Fall.ID) AS Anz FROM Hauptdaten_Fall WHERE (((Hauptdaten_Fall.IstAktuell)=20))
> GROUP BY Hauptdaten_Fall.MeldeKategorie ORDER BY Hauptdaten_Fall.MeldeKategorie;
>
> to the data set I put on
>
> http://www.foodborne-net.de/~tillea/ifsg/ifsgtest.dump.bz2
>
> If this should take less than half a second on a modern PC I could
> continue to try mo realistic queries.
>
> I really hope that I could readjust the image of PostgreSQL in the
> eyes of my M$-centered colleagues.
>
> Kind regards
>
> Andreas.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
5-4-3-2-1 Thunderbirds are GO!
------------------------
http://www.mohawksoft.com