Skip site navigation (1) Skip section navigation (2)

Compared MS SQL 2000 to Postgresql 9.0 on Windows

From: Tom Polak <tom(at)rockfordarearealtors(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Compared MS SQL 2000 to Postgresql 9.0 on Windows
Date: 2010-12-07 17:34:25
Message-ID: 378e24d1e3af88a49fbbb7c4d26f441a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
We are in the process of deciding on how to proceed on a database upgrade.
We currently have MS SQL 2000 running on Windows 2003 (on my test server).
I was shocked at the cost for MS SQL 2008 R2 for a new server (2 CPU
license).  I started comparing DB’s and came across postgresql.  It seemed
to be exactly what I was after.  All of our programming is in ASP.net.
Since I am running MSSQL 2000 I have no benefit for .Net integration, so it
is not a concern.



I ran a head to head test of MS SQL 2000 and Postgresql 9.0.  Both are
running on Windows 2003.  What I found was quite surprising and I am
wondering if anyone can point out what is going on here.
Here is the test I ran.
I created 2 tables, the main table had 5 fields with a serial ID field.  The
second table linked to table 1 for a state field.

I had ASP.net via MSSQL create 1,000 records in the main table. Took 9.85
seconds to complete.
Next I had ASP.net via Postgresql create 1,000 records.  Took .65625
seconds.
Postgresql smoked MS SQL server on that test.



Next test is to use ASP.net and join all 1,000 rows with table 2 and then
display the text out.

MS SQL took 0.76 seconds to display
select name,address,city,state,statename,stateid,other from pgtemp1 left
join pgtemp2 on state=stateid



Then I did the same test via Postgresql and it took 8.85 seconds!  I tried
it again as I thought I did something wrong.  I did a few tweaks such as
increasing the shared buffers.  Still the best I could get it to was 7.5
seconds.  This is insanely slow compared to MSSQL 2000.  What am I missing.
Here is my SQL statement for postgresql:
select name,address,city,state,statename,stateid,other from pgtemp1 left
join pgtemp2 on state=stateid



Any ideas on why the Postgres server is soooo much slower on the joins?   I
am trying to understand what is going on here so please don’t flame me.  Any
advice is appreciated.





*Thanks,
Tom Polak
Rockford Area Association of Realtors
**
The information contained in this email message is intended only for the use
of the individual or entity named.  If the reader of this email is not the
intended recipient or the employee or agent responsible for delivering it to
the intended recipient, you are hereby notified that any dissemination,
distribution or copying of this email is strictly prohibited.  If you have
received this email in error, please immediately notify us by telephone and
reply email.  Thank you.*

*Although this email and any attachments are believed to be free of any
viruses or other defects that might affect any computer system into which it
is received and opened, it is the responsibility of the recipient to ensure
that it is free of viruses, and the Rockford Area Association of Realtors
hereby disclaims any liability for any loss or damage that results.*

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2010-12-07 17:37:31
Subject: Re: Performance under contention
Previous:From: Vlad ArkhipovDate: 2010-12-07 08:36:59
Subject: Slow BLOBs restoring

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group