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
-~----------~----~----~----~------~----~------~--~---
