Обсуждение: SQL fast in PSQL, very slow using MS.NET driver
<div class="Section1"><p class="MsoNormal">Hi,<p class="MsoNormal">The setup in question includes PostGRESQL v8.2.4, Javabased web servers and MS.NET based web servers. Following is the fuzzy situation:<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0level1 lfo2"><span style="mso-list:Ignore">1.<span style="font:7.0pt "Times New Roman""> </span></span>Our SQL queries run very fast using PSQL (both, from the server as well as the client).<p class="MsoListParagraph"style="text-indent:-.25in;mso-list:l0 level1 lfo2"><span style="mso-list:Ignore">2.<span style="font:7.0pt"Times New Roman""> </span></span>The Java app also retrieves the results very fast (of course, weare using Postgres JDBC driver).<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo2"><span style="mso-list:Ignore">3.<spanstyle="font:7.0pt "Times New Roman""> </span></span>But, the same SQL queries performpathetically slow when called from .NET application. The driver being used is NPGSQL.<p class="MsoNormal"> <p class="MsoNormal">Ihave tried making some alterations to TCP related system variables like TCP_NoDelay and TCPAckFrequencyon Windows, but to no profit. Same setup is already running on Oracle backend, and, there is no differencein SQL response timings from either application.<p class="MsoNormal"> <p class="MsoNormal">Please advice, as thishas become a major hurdle for us to push PostGRESQL to be productionized, in place of currently running Oracle DB.<pclass="MsoNormal"> <p class="MsoNormal">Regards,<p class="MsoNormal"> <p class="MsoNormal">Ashish Sharma<p class="MsoNormal">CoreDatabase Systems<p class="MsoNormal"><a href="http://www.coredbsystems.com">http://www.coredbsystems.com</a></div><br/><p><font size="2">Internal Virus Databaseis out-of-date.<br /> Checked by AVG.<br /> Version: 7.5.519 / Virus Database: 269.22.5/1356 - Release Date: 4/2/20084:14 PM<br /></font>
On Wed, 2008-04-09 at 18:33 +0530, Ashish Sharma wrote: > Hi, > > The setup in question includes PostGRESQL v8.2.4, Java based web > servers and MS.NET based web servers. Following is the fuzzy > situation: > > 1. Our SQL queries run very fast using PSQL (both, from the > server as well as the client). > > 2. The Java app also retrieves the results very fast (of course, > we are using Postgres JDBC driver). > > 3. But, the same SQL queries perform pathetically slow when > called from .NET application. The driver being used is NPGSQL. > > > > I have tried making some alterations to TCP related system variables > like TCP_NoDelay and TCPAckFrequency on Windows, but to no profit. > Same setup is already running on Oracle backend, and, there is no > difference in SQL response timings from either application. > > > > Please advice, as this has become a major hurdle for us to push > PostGRESQL to be productionized, in place of currently running Oracle > DB. You have to give more info than that to get any meaningful advice . Unless you are just looking for some paid-support guy to contact you ;) And as this seems to be a .NET related problem, you may get a better answer from some dedicated list, maybe http://archives.postgresql.org/pgsql-interfaces/ ------------------------- Hannu
On Wed, Apr 9, 2008 at 10:56 AM, Hannu Krosing <hannu@krosing.net> wrote: > > On Wed, 2008-04-09 at 18:33 +0530, Ashish Sharma wrote: > > Hi, > > Hi, all!! > > The setup in question includes PostGRESQL v8.2.4, Java based web > > servers and MS.NET based web servers. Following is the fuzzy > > situation: > > > > 1. Our SQL queries run very fast using PSQL (both, from the > > server as well as the client). > > > > 2. The Java app also retrieves the results very fast (of course, > > we are using Postgres JDBC driver). > > > > 3. But, the same SQL queries perform pathetically slow when > > called from .NET application. The driver being used is NPGSQL. > > > > What queries are you running? What version of Npgsql? Are you using prepared statements? We have performance issues with prepared statements. If it is so, can you try without prepared statements? You can discuss this also in our forums: forums.npgsql.org Thanks in advance. -- Regards, Francisco Figueiredo Jr. fxjr.blogspot.com www.npgsql.org
Thanks for the response. Below are the details: > > On Wed, 2008-04-09 at 18:33 +0530, Ashish Sharma wrote: > > > Hi, > > > > Hi, all!! > > > The setup in question includes PostGRESQL v8.2.4, Java based web > > > servers and MS.NET based web servers. Following is the fuzzy > > > situation: > > > > > > 1. Our SQL queries run very fast using PSQL (both, from the > > > server as well as the client). > > > > > > 2. The Java app also retrieves the results very fast (of course, > > > we are using Postgres JDBC driver). > > > > > > 3. But, the same SQL queries perform pathetically slow when > > > called from .NET application. The driver being used is NPGSQL. > > > > > > > > > What queries are you running? We are running normal SQLs and DMLs. Even simple queries like "select * from..." are showing the described behavior. > What version of Npgsql? NPGSQL ver. 1.97.1.0 > > Are you using prepared statements? We have performance issues with > prepared statements. If it is so, can you try without prepared > statements? We are not you prepared statements. > > You can discuss this also in our forums: > > forums.npgsql.org > The dotNet version we are using is 2.0, PostgreSQL 8.2.4 (on RHEL4) Appreciate the support. Regards, Ashish Sharma No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.22.11 - Release Date: 4/9/2008 12:00 AM
On Thu, Apr 10, 2008 at 5:27 AM, Ashish Sharma <ashish@coredbsystems.com> wrote: > Thanks for the response. Below are the details: > > > > > What queries are you running? > > We are running normal SQLs and DMLs. Even simple queries like "select * from..." are showing the described behavior. > > > What version of Npgsql? > > NPGSQL ver. 1.97.1.0 > No performance problem with it. But it may be good to update to our latest beta3 release. > > > > Are you using prepared statements? We have performance issues with > > prepared statements. If it is so, can you try without prepared > > statements? > > We are not you prepared statements. > Ok. > > > > > You can discuss this also in our forums: > > > > forums.npgsql.org > > > > The dotNet version we are using is 2.0, PostgreSQL 8.2.4 (on RHEL4) > > Appreciate the support. > You are welcome. Please, when using forums, give us what queries you are running so we can make some tests to see what can be happening. Thanks in advance, Ashish. -- Regards, Francisco Figueiredo Jr. http://fxjr.blogspot.com http://www.npgsql.org