Re: [PERFORM] Slow performance on Windows .NET and OleDb
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" <[EMAIL PROTECTED]> To: "Greg Quinn" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] www.calorieking.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow performance on Windows .NET and OleDb
On 3/29/06, Greg Quinn <[EMAIL PROTECTED]> 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,10) 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=10 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Slow performance on Windows .NET and OleDb
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 ? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Slow performance on Windows .NET and OleDb
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow performance on Windows .NET and OleDb
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" <[EMAIL PROTECTED]> To: "Greg Quinn" <[EMAIL PROTECTED]> 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
Re: [PERFORM] Slow performance on Windows .NET and OleDb
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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Slow performance on Windows .NET and OleDb
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Slow performance on Windows .NET and OleDb
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 ---(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
Re: [PERFORM] Slow performance on Windows .NET and OleDb
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" <[EMAIL PROTECTED]> To: "Jim C. Nasby" <[EMAIL PROTECTED]> Cc: "Greg Quinn" <[EMAIL PROTECTED]>; 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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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/ ---(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
Re: [PERFORM] Slow performance on Windows .NET and OleDb
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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
Re: [PERFORM] Slow performance on Windows .NET and OleDb
On 3/28/06, Greg Quinn <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
