consider also to change logic...
instead of calculating 50k age range every time that you need to see that
view, you could add an extra field containing the range... you could do add
an extra field telling when it will change range... then you could calculate
new range only on a fraction of the 50k and update those fields...
of course if you have 500k or more people you will feel the difference....
2009/8/7 Yarko Tymciurak <yark...@gmail.com>
> can you calculate this as you process payment per person? Or is this
> something that is calculated once a month?
>
> The idea is that stored procedures work on the database engine, and can
> sometimes be more efficient, but you can do the same as you describe on the
> selected data.
>
> I think perhaps a nice way to do this is with a database view, where you
> create a stored procedure, and return a non-stored value through the view.
>
> What i mean is the payment would look to web2py "like" a data table column,
> but in reality it would be a calculated value by your view, which would use
> a stored procedure to return the proper value.
>
> This means you would set migrate to False for your web2py table definition
> (you do not want to change the view, once the basic table is setup).
>
> You would create the stored procedure in whatever way is conveneint (most
> likely with a management tool for the database);
> You would create a VIEW in the database which would return all the fields
> of the "native" table, PLUS a field which would be defined as the return of
> your stored procedure on the current row-id, and the appropriate age fields.
>
> In web2py you COULD define an administrative function to make the initial
> "native" table, and then define a second one, migrate=False, and this payout
> field as readonly, and name it for the VIEW you created separately in your
> DB.
>
> That should nicely and transparently give you what you want.
>
> Perhaps others will have other ideas. I have done things like this in the
> past, and it can work rather well.
>
> Regards,
> - Yarko
>
> 2009/8/6 陶艺夫 <artman...@gmail.com>
>
> Thank you for reply.
>>
>> It's a charity project. The city goverment here decided to help old people
>> who aged 70 above financially every month. There are aid standards here:
>> age range : 70-79 80-89 90-94 95-99 100+
>> aid money: 30 80 150 300 500
>> You know, every month some people's ages will changed to the next age
>> range. So I need to calculate monthly every person's age to decide how much
>> money (s)he will get.
>> The fields of the people table:
>> name, gender, birth_date, township_id, community_id....
>> When the calculating work is done, I need to generate an Excel file by
>> xlwt, a town a worksheet, the rows will be grouped by every community.
>> The aged 70+ people here is 50,000+.
>> Any solutions more efficient than python's "For Loop" on a huge DAL
>> records set?
>>
>> Thanks
>>
>>
>> 2009/8/7 mdipierro <mdipie...@cs.depaul.edu>
>>
>>
>>> You can do
>>>
>>> db.executesql('....')
>>>
>>> and pass any sql you want. If you show us what you need to do
>>> specifically perhaps we can suggest a better way.
>>>
>>> Massimo
>>>
>>> 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.
>>>
>>>
>>
>>
>>
>
> >
>
--
Sebastian E. Ovide
skype: seezov
+353 87 6340149
Sent from Dublin, Ireland
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
