Обсуждение: What is the fastest way to get a resultset
I'm very new to Postgresql, so don't beat me up to bad if you see a problem, just Inform me what I've done wrong. I'm use Postgresql 7.2 (PeerDirect's Windows port) on Win2000 384MB RAM 10GB of Free space 800 Mhz, using the ODBC driver 7.03.01.00. I have a table that has 103,000 records in it (record size is about 953 bytes) and when I do a select all (select * from <table>) it takes a whopping 30 secs for the data to return!! SQLServer on the other hand takes 6 secs, but you can also use what is called a firehose cursor, which will return the data in < 1 sec. I have done everything that I know how to speed this up, does anyone have any advise? Thanks
On Sun, 24 Aug 2003, Bupp Phillips wrote: > I'm very new to Postgresql, so don't beat me up to bad if you see a problem, > just Inform me what I've done wrong. > > I'm use Postgresql 7.2 (PeerDirect's Windows port) on Win2000 384MB RAM 10GB > of Free space 800 Mhz, using the ODBC driver 7.03.01.00. > > I have a table that has 103,000 records in it (record size is about 953 > bytes) and when I do a select all (select * from <table>) it takes a > whopping 30 secs for the data to return!! > > SQLServer on the other hand takes 6 secs, but you can also use what is > called a firehose cursor, which will return the data in < 1 sec. > > I have done everything that I know how to speed this up, does anyone have > any advise? Have you tried declaring a cursor?
On Sun, Aug 24, 2003 at 05:19:52PM -0700, Bupp Phillips wrote: > I'm very new to Postgresql, so don't beat me up to bad if you see a problem, > just Inform me what I've done wrong. > > I'm use Postgresql 7.2 (PeerDirect's Windows port) on Win2000 384MB RAM 10GB > of Free space 800 Mhz, using the ODBC driver 7.03.01.00. > > I have a table that has 103,000 records in it (record size is about 953 > bytes) and when I do a select all (select * from <table>) it takes a > whopping 30 secs for the data to return!! From what I remeber there is an option in the ODBC driver (or was it Access) to use cursors when selecting from tables. The problem was that the boss would attempt to open a 9 million row table and Access would attempt to load the whole lot into memory (and fail). With this option on it simply created a cursor and fetched just enough to display on screen while you scroll around. Much better and faster. When dealing with large resultsets, cursors are the way to go. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato