Thread: Slow performance on Windows .NET and OleDb
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.
On 3/28/06, Greg Quinn <greg@officium.co.za> 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
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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/
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" <jd@commandprompt.com> To: "Jim C. Nasby" <jnasby@pervasive.com> Cc: "Greg Quinn" <greg@officium.co.za>; <pgsql-performance@postgresql.org> 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/ > >
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 >
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.
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/
> 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" <lists@peufeu.com> To: "Greg Quinn" <greg@officium.co.za> 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. >
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
> 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 ?
On 3/29/06, Greg Quinn <greg@officium.co.za> 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
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" <lists@peufeu.com> > To: "Greg Quinn" <greg@officium.co.za> > 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 chris.kings-lynne@calorieking.com www.calorieking.com
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
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: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] On Behalf Of Greg Quinn > Sent: Wednesday, March 29, 2006 11:57 PM > To: pgsql-performance@postgresql.org > 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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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 <dave@tridecap.com> 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: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance- > > owner@postgresql.org] On Behalf Of Greg Quinn > > Sent: Wednesday, March 29, 2006 11:57 PM > > To: pgsql-performance@postgresql.org > > 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 majordomo@postgresql.org 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.