Thank you for you help! I fetch all 50000 records from database not for displaying in the browser.
I found the bottleneck was using python's 'for .. in ..' loops. I wrote a record to a row of an Excel file(using xlwt) within every loop. And I found it was unavoidable. Now for 50000+ records, generating the Excel file just take 61 seconds... 2009/8/7 Donald Hughes <donald.hug...@gmail.com> > > Are you trying to write 50,000 rows to a single webpage without > paging? If so, you might find the bottleneck is in the browser trying > to render a table that large. > > > > I ran the query below on a similar set of data, albeit in MS SQL > Server 2005 so I'm sure the SQL will vary from that of PostgreSQL. The > query below executed for me in less than second, though hardware and > platform considerations could change that. Nonetheless, 50000 records > is a very small table so long as it is properly indexed and I don't > doubt that PostgreSQL on commodity hardware could also deliver a > subsecond response. > > select > ID, FIRST_NAME, LAST_NAME, > case when datediff(yy,BIRTH_DATE,getdate()) between 30 and 39 then > '5' > when datediff(yy,BIRTH_DATE,getdate()) between 40 and 49 > then '10' > when datediff(yy,BIRTH_DATE,getdate()) between 50 and 59 > then '15' > when datediff(yy,BIRTH_DATE,getdate()) between 60 and 69 > then '20' > when datediff(yy,BIRTH_DATE,getdate()) between 70 and 79 > then '30' > end > from PEOPLE > > > > > You would just need to add the township to the select and group by > clauses to additionally group by township and by age for your summary > report. > > select TOWNSHIP, > case when datediff(yy,BIRTH_DATE,getdate()) between 30 > and 39 then '30-39' > when datediff(yy,BIRTH_DATE,getdate()) between 40 > and 49 then > '40-49' > when datediff(yy,BIRTH_DATE,getdate()) between 50 > and 59 then > '50-59' > when datediff(yy,BIRTH_DATE,getdate()) between 60 > and 69 then > '60-69' > when datediff(yy,BIRTH_DATE,getdate()) between 70 > and 79 then > '70-79' > end, > count(*) > from PEOPLE > group by > TOWNSHIP, > case when datediff(yy,BIRTH_DATE,getdate()) between 30 and 39 then > '30-39' > when datediff(yy,BIRTH_DATE,getdate()) between 40 and 49 > then > '40-49' > when datediff(yy,BIRTH_DATE,getdate()) between 50 and 59 > then > '50-59' > when datediff(yy,BIRTH_DATE,getdate()) between 60 and 69 > then > '60-69' > when datediff(yy,BIRTH_DATE,getdate()) between 70 and 79 > then > '70-79' > end > > > > The above SQL produces results like (though I left the TOWNSHIP > grouping out since I don't have a similar column and for brevity): > > 30-39 9684 > 40-49 15344 > 50-59 15697 > 60-69 8581 > 70-79 2358 > other 3206 > > > > > > > On Aug 6, 1:13 pm, 陶艺夫 <artman...@gmail.com> wrote: > > Hi, > > I'm using PostgreSQL, I need to use stored procedure to calculate on more > > than 50000 records -- according to every people's birthday, figure out > how > > much subsidy (s)he will get. Is this a rational idea? > > I have done calculating by a controller, but it has taken more than 50 > > seconds to get the result. Is a stored procedure helpful for this? And > how > > to do it in DAL? > > > > Thanks. > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
