473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

psycopg2 faster way to retrieve last x records

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
Nov 8 '06 #1
2 1828
Luis P. Mendes wrote:
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 <st****@stuartbishop.net>
http://www.stuartbishop.net/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

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

Nov 8 '06 #2
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
Nov 8 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: mvanier | last post by:
There was a thread a while back dealing with an error message the psycopg2 Postgres interface gives when trying to convert some mxDateTime values: "can't adapt". The answer given was that psycopg2...
2
by: Luis P. Mendes | last post by:
Hi, I've installed psycopg2 under Slacware 11.0 along with PostgreSQL 8.2.4. When I run the python shell I get the following error: lupe@lince ~$ python Python 2.4.3 (#1, Jul 26 2006,...
2
by: Jon Clements | last post by:
Hi All. I'm using psycopg2 to retrieve results from a rather large query (it returns 22m records); unsurprisingly this doesn't fit in memory all at once. What I'd like to achieve is something...
0
by: David Michael Schruth, | last post by:
Hi, I am sort of in a jam here. I am using the PsycoPG2 library to read data out of a windows XP based PostGIS / PostGreSQL database but I am apparently unable to write (update or insert) even...
4
by: Andre' John | last post by:
Hi I am trying to do this for a Postgresql database: conn = psycopg2.connect('host=localhost') cur = conn.cursor() cur.execute("SELECT * FROM names WHERE name=%s", ) , which doesn't work,...
1
by: George Sakkis | last post by:
I have a simple DB table that stores md5 signature pairs: Table "public.duplicate" Column | Type | Modifiers ----------+-------+----------- sig | bytea | not null orig_sig | bytea | not...
0
by: RossGK | last post by:
I've been using pydev for a short while successfully, and Django with postgresql as well. psycopg2 is part of that behind the scenes I would imagine, to make django work. Now I'm trying to use...
2
by: kj | last post by:
Hi. I can't find any documentation for psycopg2. I'm a noob, so I'm sure I'm just not looking in the right place... Anybody know where it is? TIA! kynn
5
by: Thomas Guettler | last post by:
Hi, I discovered this: import psycopg2 connection=psycopg2.connect("dbname='...' user='...'") cursor=connection.cursor() cursor.execute('''SELECT '%' ''') # Does not fail...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.