Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 046432E3070
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 18 Dec 2007 00:31:52 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 46868-06 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 18 Dec 2007 00:31:45 -0400 (AST)
Received: from web51610.mail.re2.yahoo.com (web51610.mail.re2.yahoo.com
	[206.190.38.215])
	by postgresql.org (Postfix) with SMTP id AA64D2E3042
	for <pgsql-performance@postgresql.org>;
	Tue, 18 Dec 2007 00:31:45 -0400 (AST)
Received: (qmail 35693 invoked by uid 60001); 18 Dec 2007 04:31:44 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=QoLZZjHFfCQ6iH9q057u4MIKdZUtxeAusH5RcQ4OpDKATm8I7wQzUkzWs2G3fB0YI6ewd5Vqz6vLuorcp7e0Ky7ANAgpclUAbIx/MNOYPjMA1Jxa0AQIztH2nXG1kg+edgNW2reuJfFLe+/dLEY8aCAmgqAna+K7iT55jnqy2XA=;
X-YMail-OSG: 
 kUv_sUwVM1maWPYvW3bqwCGpAO6TMM8DTCrVLlonWisZN5l1P0RiYL1wF70nw3Gy2UNzLOmFEA--
Received: from [210.213.227.215] by web51610.mail.re2.yahoo.com via HTTP;
	Mon, 17 Dec 2007 20:31:44 PST
X-Mailer: YahooMailRC/818.31 YahooMailWebService/0.7.158.1
Date: Mon, 17 Dec 2007 20:31:44 -0800 (PST)
From: Robert Bernabe <robert_bernabe@yahoo.com>
Subject: Re: Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-599376724-1197952304=:35451"
Message-ID: <712109.35451.qm@web51610.mail.re2.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/262
X-Sequence-Number: 28078

--0-599376724-1197952304=:35451
Content-Type: text/plain; charset=us-ascii

Hi All,
    Thanks for all the help here. Sorry for the late update but we've found our problem and fixed it already. Prior to looking at the translated code more  intently, I wanted to make sure that our environmental settings were acceptable and the various emails from members have confirmed that...

In a nutshell it seems that MS SQL allows bad T-SQL code by optimizing and ignoring redundant/useless from and where clauses in an update statement whereas plpgsql will execute exactly what the code is asking it to do...

We had several update instances in the T-SQL code that looked like this :

update "_tbl_tmp2"
set "LongBackPeriod" = (select count ("EPeriod") from "_tbl_tmp1" where "_tbl_tmp1"."Row" = "_tbl_tmp2"."Row");
--------------------------------------------------
from "_tbl_tmp2" tmp2, "_tbl_tmp1" tmp1
where tmp2."Row" = tmp1."Row";
---------------------------------------------------

In T-SQL, the performance is the same whether the last two lines are there or not...

In plpgsql, this is not the case the from and where clauses are not necessary and probably creates an internal (rather useless and time consuming) inner join in plpgsql which accounts for the original performance issue.

I'm happy (actually ecstatic) to report that Win2kPro + PG performance is slightly faster than  Win2kPro + MSSQL/MSDE. 

Linux(FC7) + PG 8.x performance seems to be 3x faster than Win2KPro + MSSQL/MSDE for our stored functions. 

Thanks for all the help! Am a believer now. :) 




--0-599376724-1197952304=:35451
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div style="font-family: times new roman,new york,times,serif; font-size: 12pt;">Hi All,<br>&nbsp;&nbsp;&nbsp; Thanks for all the help here. Sorry for the late update but we've found our problem and fixed it already. Prior to looking at the translated code more&nbsp; intently, I wanted to make sure that our environmental settings were acceptable and the various emails from members have confirmed that...<br><br>In a nutshell it seems that MS SQL allows bad T-SQL code by optimizing and ignoring redundant/useless from and where clauses in an update statement whereas plpgsql will execute exactly what the code is asking it to do...<br><br>We had several update instances in the T-SQL code that looked like this :<br><br>update "_tbl_tmp2"<br>set "LongBackPeriod" = (select count ("EPeriod") from
 "_tbl_tmp1" where "_tbl_tmp1"."Row" = "_tbl_tmp2"."Row");<br>--------------------------------------------------<br>from "_tbl_tmp2" tmp2, "_tbl_tmp1" tmp1<br>where tmp2."Row" = tmp1."Row";<br>---------------------------------------------------<br><br>In T-SQL, the performance is the same whether the last two lines are there or not...<br><br>In plpgsql, this is not the case the from and where clauses are not necessary and probably creates an internal (rather useless and time consuming) inner join in plpgsql which accounts for the original performance issue.<br><br>I'm happy (actually ecstatic) to report that Win2kPro + PG performance is slightly faster than&nbsp; Win2kPro + MSSQL/MSDE. <br><br>Linux(FC7) + PG 8.x performance seems to be 3x faster than Win2KPro + MSSQL/MSDE for our stored functions. <br><br>Thanks for all the help! Am a believer now. :) <br><br><br></div></div></body></html>
--0-599376724-1197952304=:35451--