Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 491E21337B55
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Dec 2010 17:32:06 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 10679-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  7 Dec 2010 21:31:58 +0000 (UTC)
X-Greylist: delayed 03:53:14.347118 by SQLgrey-1.7.6
Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com
	[74.125.82.174])
	by mail.postgresql.org (Postfix) with ESMTP id CD3C31337B4F
	for <pgsql-performance@postgresql.org>;
	Tue,  7 Dec 2010 17:31:56 -0400 (AST)
Received: by wyb28 with SMTP id 28so369721wyb.19
	for <pgsql-performance@postgresql.org>;
	Tue, 07 Dec 2010 13:31:56 -0800 (PST)
Received: by 10.227.72.139 with SMTP id m11mr7995170wbj.104.1291757515842;
	Tue, 07 Dec 2010 13:31:55 -0800 (PST)
From: Tom Polak <tom@rockfordarearealtors.org>
References: <378e24d1e3af88a49fbbb7c4d26f441a@mail.gmail.com>
	<4CFE7964.80206@squeakycode.net>
	<AANLkTi=HiO8ErFwzqBvgjXXHRUMKeUWqCpL-p9Y+oL_-@mail.gmail.com>
	<4CFE8E59.2010505@squeakycode.net>
	<AANLkTin4K86j2-GOUfmdXQ8UKv_j9V8ZRiRBEbbXj+jc@mail.gmail.com>
	<20101207201028.GE4028@aart.is.rice.edu>
	<4CFE97B4.8090708@squeakycode.net>
In-Reply-To: <4CFE97B4.8090708@squeakycode.net>
MIME-Version: 1.0
X-Mailer: Microsoft Office Outlook 11
Thread-Index: AcuWTJ5vYaRjA+WpScyvgxcH8lk85QACPZIQ
X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7600.16543
Date: Tue, 7 Dec 2010 15:29:54 -0600
Message-ID: <fd4bd31b523753145cf67ae6e6cb4aec@mail.gmail.com>
Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.589 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, LOTS_OF_MONEY=0.001, RCVD_IN_DNSWL_LOW=-0.7,
	T_FILL_THIS_FORM_SHORT=0.01
X-Spam-Level: 
X-Archive-Number: 201012/94
X-Sequence-Number: 41546

What I was really after was a quick comparison between the two.  I did not
create anything special, just the two tables.  One table SQL generated the
records for me.  I did not tweak anything after installing either system.
There was a primary key on the ID field of both tables, no indexes though
in either system.  The second table had 1 record in it.  The hardware it
is running on is fairly good, dual Xeon CPUs, 4 GB of RAM, Raid 5.  Btw,
the cost for MS SQL 2008 R2 is ~$14,000 for 2 cpus,
http://www.cdw.com/shop/products/default.aspx?EDC=2167810 .  That is why I
am pursuing this.  :)

Here is the ASP.net code that I was running
Dim starttime As Date = Date.Now
        Dim endtime As Date
        Dim reader As NpgsqlDataReader
        Dim output2 As String = ""


        Dim oConn As New
NpgsqlConnection("Server=192.168.1.5;Port=5432;Userid=postgres;Password=12
345;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encodin
g=UNICODE;Timeout=15;SslMode=Disable;Database=tomtemp")
        oConn.Open()
        Dim x As Integer = 0
        'For x = 0 To 1000 'uncomment to insert records.
        'Dim command As New NpgsqlCommand("insert into pgtemp1(name,
address, city, state) values ('Tom" & x & "','123" & x & " main
st','rockford',1) ", oConn) 'meant for loop to put in 1,000 records in
pgtemp1 table
        'Dim command As New NpgsqlCommand("insert into pgtemp2(statename,
stateid, other) values ('Illinois',1,'This is a lot of fun') ", oConn)
'only sends 1 record into the table pgtemp2
        'command.ExecuteNonQuery()
        'Next

        'join table and read 1000 rows.
        Dim command As New NpgsqlCommand("select
name,address,city,state,statename,stateid,other from pgtemp1 left join
pgtemp2 on state=stateid", oConn)
        reader = command.ExecuteReader()
        While reader.read()
            output2 += "<tr><td>" & reader("name") & "</td><td>" &
reader("address") & "</td><td>" & reader("city") & "</td><td>" &
reader("statename") & "</td><td>" & reader("other") & "</td></tr>"
        End While
        oConn.Close()
        readeroutput.text =
"<table><tr><td>Name:</td><td>Address:</td><td>City:</td><td>State</td><td
>Other</td></tr>" & output2 & "</table>"

        endtime = Date.Now
        Dim runtime As String
        runtime = endtime.Subtract(starttime).TotalSeconds
        output.text = starttime.ToString & " " & runtime

The SQL is a straight convert from MS SQL code.  I did not tweak either
system.