psycopg2 faster way to retrieve last x records

L

Luis P. Mendes

Hi,

I would like to know if there is a better way to do what I'm already doing
as stated in the following example, when using psycopg2 with PostgresQL.
........................
nr_bars_before = 200
tabela = 'seconds'
sqlString = "SELECT * FROM " + tabela + " ORDER BY tempounix;"
curs = self.conn.cursor()
curs.execute(sqlString)
try:
while 1:
curs.scroll(1,mode='relative')
except: pass
curs.scroll(-int(math.fabs(nr_bars_before)),mode='relative')
row = curs.fetchone()
curs.close()
........................

What I need is to get the last 200
records from the table, each couple minutes. As stated,
what I do is to go all the way through the table records until the end,
then going back 200 in order to select all of them (those 200) forward
down to the last one.

But it takes a lot of time to do it. I mean some seconds. And it brings
some 'heavy' work on disk. The table 'seconds' has 540000+ lines right
now.

Can I do something different in order to have a lighter load on the system
and a quicker response?

Luis P. Mendes
 
S

Stuart Bishop

Luis said:
Hi,

I would like to know if there is a better way to do what I'm already doing
as stated in the following example, when using psycopg2 with PostgresQL.
.......................
nr_bars_before = 200
tabela = 'seconds'
sqlString = "SELECT * FROM " + tabela + " ORDER BY tempounix;"
curs = self.conn.cursor()
curs.execute(sqlString)
try:
while 1:
curs.scroll(1,mode='relative')
except: pass
curs.scroll(-int(math.fabs(nr_bars_before)),mode='relative')
row = curs.fetchone()
curs.close()
.......................

What I need is to get the last 200
records from the table, each couple minutes. As stated,
what I do is to go all the way through the table records until the end,
then going back 200 in order to select all of them (those 200) forward
down to the last one.

But it takes a lot of time to do it. I mean some seconds. And it brings
some 'heavy' work on disk. The table 'seconds' has 540000+ lines right
now.

The following SQL statement will return the last 200 rows in reverse order:

SELECT * FROM seconds ORDER BY tempounix DESC LIMIT 200

This will only send 200 rows from the server to the client (your existing
approach will send all of the rows). Also, if you have an index on tempounix
it will be really fast.


If you really need the results in tempounix order, then:

SELECT * FROM (
SELECT * FROM seconds ORDER BY tempounix DESC LIMIT 200
) AS whatever
ORDER BY tempounix;


--
Stuart Bishop <[email protected]>
http://www.stuartbishop.net/


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

iD8DBQFFUSzVAfqZj7rGN0oRAvmYAJ9EU3oMMF/jwAMzUmicPi6evzxF9wCdFUuq
ZrKOZGch0ps6cIJTqth8dBQ=
=67WR
-----END PGP SIGNATURE-----
 
L

Luis P. Mendes

Em Tue, 07 Nov 2006 17:03:17 -0800, Stuart Bishop escreveu:
The following SQL statement will return the last 200 rows in reverse order:

SELECT * FROM seconds ORDER BY tempounix DESC LIMIT 200

This will only send 200 rows from the server to the client (your existing
approach will send all of the rows). Also, if you have an index on tempounix
it will be really fast.


If you really need the results in tempounix order, then:

SELECT * FROM (
SELECT * FROM seconds ORDER BY tempounix DESC LIMIT 200
) AS whatever
ORDER BY tempounix;

Thank you Stuart, I'll try it.

Luis P. Mendes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top