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.