Thread: 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 Mon, 25 Aug 2003, Bupp Phillips wrote: > > 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. > You probably want a cursor. Typically what happens is postgres sends _all_ the data to the client - which can be rather substantial. A cursor allows you to say "get me the first 1000 records. now the next 1000" - it should get you the speed you want. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
Bupp Phillips <hello@noname.com> 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? > Probably you need to fetch more than one row at a time. I made that misstake once myself :) Magnus
Is this something that can be done thru a SQL statement, or are you saying that I need to develop logic to handle this because the database won't hold the resultset on the server, but instead sends it all to the client? It there a way to get server side cursors with Postgresql like SQLServer has or is this a limitation that it has? Thanks "Jeff" <threshar@torgo.978.org> wrote in message news:Pine.BSF.4.44.0308251645360.15800-100000@torgo.978.org... > On Mon, 25 Aug 2003, Bupp Phillips wrote: > > > > > 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. > > > You probably want a cursor. > Typically what happens is postgres sends _all_ the data to the client - > which can be rather substantial. A cursor allows you to say "get me the > first 1000 records. now the next 1000" - it should get you the speed you > want. > > > -- > Jeff Trout <jeff@jefftrout.com> > http://www.jefftrout.com/ > http://www.stuarthamm.net/ > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Tue, Aug 26, 2003 at 02:18:23AM -0700, Bupp Phillips wrote: > It there a way to get server side cursors with Postgresql like SQLServer has > or is this a limitation that it has? http://www.postgresql.org/docs/7.3/static/sql-declare.html http://www.postgresql.org/docs/7.3/static/sql-fetch.html -Neil
So there are no settings for PG to can give me this same (fast) capability just by issuing a SQL statement thru the ODBC driver? The reason I can't go the route of a DECLARE CURSOR is because my application runs on multiple databases, so I have stay clear of certain routines that may are may not be supported on another database. "Neil Conway" <neilc@samurai.com> wrote in message news:20030826182624.GF64198@home.samurai.com... > On Tue, Aug 26, 2003 at 02:18:23AM -0700, Bupp Phillips wrote: > > It there a way to get server side cursors with Postgresql like SQLServer has > > or is this a limitation that it has? > > http://www.postgresql.org/docs/7.3/static/sql-declare.html > http://www.postgresql.org/docs/7.3/static/sql-fetch.html > > -Neil > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >