From: Gary Doades <gpd@gpdnet.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
Date: Tue, 07 Dec 2010 19:58:37 +0000
Message-ID: <4CFE91ED.3010508@gpdnet.co.uk> (raw)
In-Reply-To: <4CFE8E59.2010505@squeakycode.net>
References: <378e24d1e3af88a49fbbb7c4d26f441a@mail.gmail.com>
<4CFE7964.80206@squeakycode.net>
<AANLkTi=HiO8ErFwzqBvgjXXHRUMKeUWqCpL-p9Y+oL_-@mail.gmail.com>
<4CFE8E59.2010505@squeakycode.net>
On 07/12/2010 7:43 PM, Andy Colson wrote:
> On 12/7/2010 1:22 PM, Justin Pitts wrote:
>>>
>>> Also, as a fair warning: mssql doesn't really care about
>>> transactions, but
>>> PG really does. Make sure all your code is properly starting and
>>> commiting
>>> transactions.
>>>
>>> -Andy
>>
>> I do not understand that statement. Can you explain it a bit better?
>
> In mssql you can write code that connects to the db, fire off updates
> and inserts, and then disconnects. I believe mssql will keep all your
> changes, and the transaction stuff is done for you.
>
> In PG the first statement you fire off (like an "insert into" for
> example) will start a transaction. If you dont commit before you
> disconnect that transaction will be rolled back. Even worse, if your
> program does not commit, but keeps the connection to the db open, the
> transaction will stay open too.
As far as I know both MS SQL and and Postgres work just the same as
regards explicit and implicit (autocommit) transactions, only the
underlying storage/logging mechanisms are different.
Transactions shouldn't make ay real difference to the select/join
performance being complained about though. It's already stated that the
insert performance of postgres far exceeds SQL Server, which is my
experience also.
As already suggested, until we see the exact table definitions including
indexes etc. there's no real way to tell what the problem is. How many
rows are in the second table? It really shouldn't take that much time to
read 1000 rows unless you have a bizarrely slow hard disk.
It would be nice to eliminate any programmatic or driver influence too.
How does the SQL select execute in enterprise manager for mssql and psql
or pgadmin for postgres?
Cheers,
Gary.
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: gpd@gpdnet.co.uk
Subject: Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows
In-Reply-To: <4CFE91ED.3010508@gpdnet.co.uk>
* 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