From: Tom Polak <tom@rockfordarearealtors.org>
To: pgsql-performance@postgresql.org
Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
Date: Tue, 7 Dec 2010 15:29:54 -0600
Message-ID: <fd4bd31b523753145cf67ae6e6cb4aec@mail.gmail.com> (raw)
In-Reply-To: <4CFE97B4.8090708@squeakycode.net>
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tom@rockfordarearealtors.org
Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
In-Reply-To: <fd4bd31b523753145cf67ae6e6cb4aec@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox