Thread: Slow performance on Windows .NET and OleDb

From:
"Greg Quinn"
Date:

Hello,

I have just installed PostGreSql 8.1 on my Windows XP PC. I created a simple
table called users with 4 varchar fields.

I am using the OleDb connection driver. In my .NET application, I populate
3000 records into the table to test PostGreSql's speed. It takes about 3-4
seconds.

Even worse is displaying the 3000 records in a ListView control. It takes
about 7 seconds. In MySQL, the exact same table and application displays the
same 3000 records in under 1/2 second!!!

Why is PostGreSql so slow compared to MySQL? What do you recommend I do to
speed up? It is such a simple query and small database.



From:
"Merlin Moncure"
Date:

On 3/28/06, Greg Quinn <> wrote:
> I am using the OleDb connection driver. In my .NET application, I populate
> 3000 records into the table to test PostGreSql's speed. It takes about 3-4
> seconds.

have you tried:
1. npgsql .net data provider
2. odbc ado.net bridge

merlin

From:
"Jim C. Nasby"
Date:

On Tue, Mar 28, 2006 at 02:14:00PM +0200, Greg Quinn wrote:
> Hello,
>
> I have just installed PostGreSql 8.1 on my Windows XP PC. I created a
> simple table called users with 4 varchar fields.
>
> I am using the OleDb connection driver. In my .NET application, I populate
> 3000 records into the table to test PostGreSql's speed. It takes about 3-4
> seconds.
>
> Even worse is displaying the 3000 records in a ListView control. It takes
> about 7 seconds. In MySQL, the exact same table and application displays
> the same 3000 records in under 1/2 second!!!

Have you vacuumed recently? This smells like it might be a table bloat
problem.
--
Jim C. Nasby, Sr. Engineering Consultant      
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

From:
"Joshua D. Drake"
Date:

Jim C. Nasby wrote:
> On Tue, Mar 28, 2006 at 02:14:00PM +0200, Greg Quinn wrote:
>> Hello,
>>
>> I have just installed PostGreSql 8.1 on my Windows XP PC. I created a
>> simple table called users with 4 varchar fields.
>>
>> I am using the OleDb connection driver. In my .NET application, I populate
>> 3000 records into the table to test PostGreSql's speed. It takes about 3-4
>> seconds.
>>
>> Even worse is displaying the 3000 records in a ListView control. It takes
>> about 7 seconds. In MySQL, the exact same table and application displays
>> the same 3000 records in under 1/2 second!!!
>
> Have you vacuumed recently? This smells like it might be a table bloat
> problem.


This could be a lot of things...

He is probably running the default postgresql.conf which is going to
perform horribly.

What is your work_mem? shared_buffers?

Are you passing a where clause? If so is there an index on the field
that is subject to the clause?

When you do the population, is it via inserts or copy?

Joshua D. Drake


--

             === The PostgreSQL Company: Command Prompt, Inc. ===
       Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
       Providing the most comprehensive  PostgreSQL solutions since 1997
                      http://www.commandprompt.com/



From:
"Greg Quinn"
Date:

The query is,

select * from users

which returns 4 varchar fields, there is no where clause

Yes, I am running the default postgres config. Basically I have been a MySQL
user and thought I would like to check out PostGreSql. So I did a quick
performance test. The performance was so different that I thought PostGreSQL
was nothing compared to MySQL, but now it seems its just a few configuration
options. Strange how the defult config would be so slow...

I have begun reading the documentation but am not too sure what options I
can quickly tweak to get good performance, could somebody give me some tips?

Thanks


----- Original Message -----
From: "Joshua D. Drake" <>
To: "Jim C. Nasby" <>
Cc: "Greg Quinn" <>; <>
Sent: Tuesday, March 28, 2006 7:52 PM
Subject: Re: [PERFORM] Slow performance on Windows .NET and OleDb


> Jim C. Nasby wrote:
>> On Tue, Mar 28, 2006 at 02:14:00PM +0200, Greg Quinn wrote:
>>> Hello,
>>>
>>> I have just installed PostGreSql 8.1 on my Windows XP PC. I created a
>>> simple table called users with 4 varchar fields.
>>>
>>> I am using the OleDb connection driver. In my .NET application, I
>>> populate 3000 records into the table to test PostGreSql's speed. It
>>> takes about 3-4 seconds.
>>>
>>> Even worse is displaying the 3000 records in a ListView control. It
>>> takes about 7 seconds. In MySQL, the exact same table and application
>>> displays the same 3000 records in under 1/2 second!!!
>>
>> Have you vacuumed recently? This smells like it might be a table bloat
>> problem.
>
>
> This could be a lot of things...
>
> He is probably running the default postgresql.conf which is going to
> perform horribly.
>
> What is your work_mem? shared_buffers?
>
> Are you passing a where clause? If so is there an index on the field that
> is subject to the clause?
>
> When you do the population, is it via inserts or copy?
>
> Joshua D. Drake
>
>
> --
>
>             === The PostgreSQL Company: Command Prompt, Inc. ===
>       Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>       Providing the most comprehensive  PostgreSQL solutions since 1997
>                      http://www.commandprompt.com/
>
>



From:
"Greg Quinn"
Date:

Via insert

>
> When you do the population, is it via inserts or copy?
>
> Joshua D. Drake
>
>
> --
>
>             === The PostgreSQL Company: Command Prompt, Inc. ===
>       Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>       Providing the most comprehensive  PostgreSQL solutions since 1997
>                      http://www.commandprompt.com/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>


From:
Ruben Rubio Rey
Date:

Greg Quinn wrote:

> The query is,
>
> select * from users
>
> which returns 4 varchar fields, there is no where clause
>
> Yes, I am running the default postgres config. Basically I have been a
> MySQL user and thought I would like to check out PostGreSql. So I did
> a quick performance test. The performance was so different that I
> thought PostGreSQL was nothing compared to MySQL, but now it seems its
> just a few configuration options. Strange how the defult config would
> be so slow...

My english is poor but im gonna try to explain it:

Default configuration in postgres its not for good performance, its just
design to make it working in any computer. Thats why u have to try to
custom default config file.

Anyway, people says that mysql is faster (and lighter) than postgres (at
least with mysql 3.x vs postgres 7.4), but postgres is more advanced and
its much harder to get data corrupted.

But there is something that you should known about postgres. Postgres
creates statistics of usage, and when you "vacumm", it optimizes each
table depending of usage.

So:
 - You should custom config file.
 - You should vacumm it, as someone recomended before.
 - Do u have any indexes? Remove it. To get all rows you do not need it

Note that I just have use it under Linux, i have no idea about how
should it work on Windows.



From:
Chris
Date:

Ruben Rubio Rey wrote:
> Greg Quinn wrote:
>
>> The query is,
>>
>> select * from users
>>
>> which returns 4 varchar fields, there is no where clause
>>
>> Yes, I am running the default postgres config. Basically I have been a
>> MySQL user and thought I would like to check out PostGreSql. So I did
>> a quick performance test. The performance was so different that I
>> thought PostGreSQL was nothing compared to MySQL, but now it seems its
>> just a few configuration options. Strange how the defult config would
>> be so slow...

> - Do u have any indexes? Remove it. To get all rows you do not need it

I wouldn't do that. Postgres needs indexing just like any other database.

It might affect this query but it's not going to help other queries.

--
Postgresql & php tutorials
http://www.designmagick.com/

From:
"Greg Quinn"
Date:

> how many rows does it return ? a few, or a lot ?

3000 Rows - 7 seconds - very slow

Which client library may have a problem? I am using OleDb, though haven't
tried the .NET connector yet.

Network configuration?? I am running it off my home PC with no network. It
is P4 2.4 with 1 Gig Ram. Windows XP

----- Original Message -----
From: "PFC" <>
To: "Greg Quinn" <>
Sent: Wednesday, March 29, 2006 11:02 AM
Subject: Re: [PERFORM] Slow performance on Windows .NET and OleDb


>
>> select * from users
>> which returns 4 varchar fields, there is no where clause
>
> how many rows does it return ? a few, or a lot ?
>
>> Yes, I am running the default postgres config. Basically I have been a
>> MySQL user and thought I would like to check out PostGreSql.
>
> Good idea...
>
> From the tests I made, on simple queries like yours, with no joins, speed
> pf pg 8.x is about the same as mysql 5.x ; that is to say very fast. If
> you have a performance problem on something so basic, and moreover on
> windows, it smells like a problem in the client library, or in the TCP
> transport between client and server.
> I remember messages saying postgres on windows was slow some time ago
> here, and it turned out to be a problem in the network configuration of
> the machine.
>



From:
Markus Schaber
Date:

Hi, Greg,

Greg Quinn wrote:
>>> I populate 3000 records into the table to test PostGreSql's speed.
>>> It takes about 3-4 seconds.
>> When you do the population, is it via inserts or copy?
> Via insert

Are those inserts encapsulated into a single transaction? If not, that's
the reason why it's so slow, every transaction sync()s through to the disk.

Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

From:
PFC
Date:

> 3000 Rows - 7 seconds - very slow

    On my PC (athlon 64 3000+ running Linux), selecting 3000 rows with 4
columns out of a 29 column table takes about 105 ms, including time to
transfer the results and convert them to native Python objects. It takes
about 85 ms on a test table with only those 4 columns.

    There is definitely a problem somewhere on your system.

    I'd suggest running this query in an infinite loop. Logically, it should
use 100% processor, with postgres using some percentage (30% here) and
your client using some other percentage (70% here). Is your processor used
to the max ?

From:
"Merlin Moncure"
Date:

On 3/29/06, Greg Quinn <> wrote:
> > how many rows does it return ? a few, or a lot ?
>
> 3000 Rows - 7 seconds - very slow
>
> Which client library may have a problem? I am using OleDb, though haven't
> tried the .NET connector yet.


esilo=# create temp table use_npgsql as select v, 12345 as a, 'abcdef'
as b, 'abcdef' as c, 4 as d from generate_series(1,100000) v;

SELECT
Time: 203.000 ms
esilo=# explain analyze select * from use_npgsql;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on use_npgsql  (cost=0.00..1451.16 rows=61716 width=76)
(actual time=0.007..176.106 rows=100000 loops=1)
 Total runtime: 336.809 ms
(2 rows)

I just pulled out 100k rows in about 1/3 second.    The problem is not
your postgresql configuration.  Your problem is possibly in the oledb
driver.  The last time I looked at it, it was not production ready.

http://pgfoundry.org/frs/?group_id=1000140&release_id=407

Merlin

From:
Christopher Kings-Lynne
Date:

You should run the select query from the psql utility to determine if
it's PostgreSQL, or your OleDb driver that's being slow.  It takes like
185ms on one of my tables to get 7000 rows.

Greg Quinn wrote:
>> how many rows does it return ? a few, or a lot ?
>
> 3000 Rows - 7 seconds - very slow
>
> Which client library may have a problem? I am using OleDb, though
> haven't tried the .NET connector yet.
>
> Network configuration?? I am running it off my home PC with no network.
> It is P4 2.4 with 1 Gig Ram. Windows XP
>
> ----- Original Message ----- From: "PFC" <>
> To: "Greg Quinn" <>
> Sent: Wednesday, March 29, 2006 11:02 AM
> Subject: Re: [PERFORM] Slow performance on Windows .NET and OleDb
>
>
>>
>>> select * from users
>>> which returns 4 varchar fields, there is no where clause
>>
>> how many rows does it return ? a few, or a lot ?
>>
>>> Yes, I am running the default postgres config. Basically I have been
>>> a MySQL user and thought I would like to check out PostGreSql.
>>
>> Good idea...
>>
>> From the tests I made, on simple queries like yours, with no joins,
>> speed pf pg 8.x is about the same as mysql 5.x ; that is to say very
>> fast. If you have a performance problem on something so basic, and
>> moreover on windows, it smells like a problem in the client library,
>> or in the TCP transport between client and server.
>> I remember messages saying postgres on windows was slow some time ago
>> here, and it turned out to be a problem in the network configuration
>> of the machine.
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq

--
Christopher Kings-Lynne

Technical Manager
CalorieKing
Tel: +618.9389.8777
Fax: +618.9389.8444

www.calorieking.com


From:
"Greg Quinn"
Date:

This problem was caused by the OleDb driver. I used a 3rd party .NET
provider and it worked, 8000 rows in just over 100ms!

Can somebody send me a sample connection string for the PostGreSql native
.net driver please? I'm battling to find a valid connection string.

Thanks



From:
"Dave Dutcher"
Date:

I use Npgsql, and the connection string I use is real simple:

Server=192.168.0.36;Database=mydb;User Id=myuserid;Password=123456

Hope that helps,

Dave

> -----Original Message-----
> From: 
[mailto:pgsql-performance-
> ] On Behalf Of Greg Quinn
> Sent: Wednesday, March 29, 2006 11:57 PM
> To: 
> Subject: [PERFORM] [Solved] Slow performance on Windows .NET and OleDb
>
> This problem was caused by the OleDb driver. I used a 3rd party .NET
> provider and it worked, 8000 rows in just over 100ms!
>
> Can somebody send me a sample connection string for the PostGreSql
native
> .net driver please? I'm battling to find a valid connection string.
>
> Thanks
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to  so that
your
>        message can get through to the mailing list cleanly


From:
Antoine
Date:

This is a blatant thread steal... but here we go...
Do people have any opinions on the pgsql driver? How does it compare
with the odbc in terms of performance? Is it fully production ready?
The boss wants to go .net (instead of Java, which is my preference...)
- will I have to spend my time defending postgres against
mysql/postgres/sqlserver?
Cheers
Antoine
ps. I try my best not to steal threads but sometimes... :-)

On 30/03/06, Dave Dutcher <> wrote:
> I use Npgsql, and the connection string I use is real simple:
>
> Server=192.168.0.36;Database=mydb;User Id=myuserid;Password=123456
>
> Hope that helps,
>
> Dave
>
> > -----Original Message-----
> > From: 
> [mailto:pgsql-performance-
> > ] On Behalf Of Greg Quinn
> > Sent: Wednesday, March 29, 2006 11:57 PM
> > To: 
> > Subject: [PERFORM] [Solved] Slow performance on Windows .NET and OleDb
> >
> > This problem was caused by the OleDb driver. I used a 3rd party .NET
> > provider and it worked, 8000 rows in just over 100ms!
> >
> > Can somebody send me a sample connection string for the PostGreSql
> native
> > .net driver please? I'm battling to find a valid connection string.
> >
> > Thanks
> >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to  so that
> your
> >        message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


--
This is where I should put some witty comment.