Yeah I will add an index I have another table with the same problem so I
think you might be right on the counting taking the most amount of time. Is
there anyway to index on count?
The other table:
db.cash_journal.distributor_id.readable =
db.cash_journal.transaction_id.readable =
db.cash_journal.operator_id.readable = False
db.cash_journal.transaction_type.represent = lambda
transaction_type,row: UCashChangeType.cash_types()[transaction_type]
db.cash_journal.happen_amount.represent = lambda happen_amount,row:
"$%.2f" % happen_amount
db.cash_journal.ucash_before.represent = lambda ucash_before,row:
"$%.2f" % (ucash_before if ucash_before else 0)
db.cash_journal.ucash_after.represent = lambda ucash_after,row: "$%.2f"
% (ucash_after if ucash_after else 0)
distributor_id = db(db.distributors.account_id ==
auth.user.account_id).select(db.distributors.id)[0]['id']
ucashHistoryGrid = SQLFORM.grid(
(db.cash_journal.distributor_id==distributor_id), orderby=~
db.cash_journal.id,
paginate=10, deletable=False, create=False, editable=False,
details=False, csv=True,
links=[lambda row: A('Transfer Details',
_href=URL("transfer_details", args=[row.transaction_id],
user_signature=True))],
formname='ucash-history',
)
cash_journal current has only 977263 records
On Sun, Apr 22, 2012 at 11:47 AM, simon <[email protected]> wrote:
> Perhaps it is the sorting that is taking the time. Might be worth adding
> an index on ad_id.
>
> On Sunday, 22 April 2012 17:37:14 UTC+1, Bruce Wade wrote:
>>
>> The grid is limiting the amount on the page being displayed. however it
>> is still querying all the records then creating the pages so you can page
>> between them. The query still queries all of the data instead of using
>> limits and or ajax. for 50,000 records it takes almost 3-8 seconds to load
>> the page.
>>
>> db.dailyadviews.id.readable = db.dailyadviews.ad_id.readable =
>> db.dailyadviews.viewer_id.**readable = db.dailyadviews.accepted.**readable
>> = False
>> grid = SQLFORM.grid(db.dailyadviews.**ad_id == ad_id,
>> create=False, editable=False, deletable=False, details=False,
>> args=[ad_id]
>> )
>>
>> On Sun, Apr 22, 2012 at 9:26 AM, Massimo Di Pierro <
>> [email protected]> wrote:
>>
>>> This is what I do not understand. If the query is done by the grid, it
>>> should limit the number of records to the number of records displayed.
>>> If that now happening? Are you calling the query outside of the grid?
>>>
>>>
>>> On Sunday, 22 April 2012 11:22:32 UTC-5, Bruce Wade wrote:
>>>>
>>>> Yes it has built in pagination, however some of my grids will return
>>>> over 500,000+ records which causes query's to be very very very slow. I
>>>> would rather limit the returned data because the average person is not
>>>> going to go through all them records or care about them. For example
>>>> currently there has been around 19 million adviews each of these records
>>>> contains a rating, however there is no way SQLFORM.grid can handle loading
>>>> that much data.
>>>>
>>>> For some data I can use a date range however other tables we are not
>>>> storing a date field so I can not use that process to limit the results.
>>>>
>>>> On Sun, Apr 22, 2012 at 9:13 AM, Massimo Di Pierro <
>>>> [email protected]> wrote:
>>>>
>>>>> grid has built-in pagination. Perhaps I do not understand the
>>>>> question. Can you show us your code?
>>>>>
>>>>>
>>>>> On Sunday, 22 April 2012 10:40:37 UTC-5, Bruce Wade wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Is there a way to limit the returned results for the grid? I am
>>>>>> finding that my site has so much data already that most areas where I am
>>>>>> using grid are failing <class 'psycopg2.InternalError'> current
>>>>>> transaction is aborted, commands ignored until end of transaction block
>>>>>>
>>>>>> If there is no way to limit the results is there a specific reason
>>>>>> why this was never included?
>>>>>>
>>>>>> --
>>>>>> --
>>>>>> Regards,
>>>>>> Bruce Wade
>>>>>> http://ca.linkedin.com/in/**bruc****elwade<http://ca.linkedin.com/in/brucelwade>
>>>>>> http://www.wadecybertech.com
>>>>>> http://www.fittraineronline.**co****m<http://www.fittraineronline.com>-
>>>>>> Fitness Personal Trainers Online
>>>>>> http://www.warplydesigned.com
>>>>>>
>>>>>>
>>>>
>>>>
>>>> --
>>>> --
>>>> Regards,
>>>> Bruce Wade
>>>> http://ca.linkedin.com/in/**bruc**elwade<http://ca.linkedin.com/in/brucelwade>
>>>> http://www.wadecybertech.com
>>>> http://www.fittraineronline.**co**m <http://www.fittraineronline.com>-
>>>> Fitness Personal Trainers Online
>>>> http://www.warplydesigned.com
>>>>
>>>>
>>
>>
>> --
>> --
>> Regards,
>> Bruce Wade
>> http://ca.linkedin.com/in/**brucelwade<http://ca.linkedin.com/in/brucelwade>
>> http://www.wadecybertech.com
>> http://www.fittraineronline.**com <http://www.fittraineronline.com> -
>> Fitness Personal Trainers Online
>> http://www.warplydesigned.com
>>
>>
> On Sunday, 22 April 2012 17:37:14 UTC+1, Bruce Wade wrote:
>>
>> The grid is limiting the amount on the page being displayed. however it
>> is still querying all the records then creating the pages so you can page
>> between them. The query still queries all of the data instead of using
>> limits and or ajax. for 50,000 records it takes almost 3-8 seconds to load
>> the page.
>>
>> db.dailyadviews.id.readable = db.dailyadviews.ad_id.readable =
>> db.dailyadviews.viewer_id.**readable = db.dailyadviews.accepted.**readable
>> = False
>> grid = SQLFORM.grid(db.dailyadviews.**ad_id == ad_id,
>> create=False, editable=False, deletable=False, details=False,
>> args=[ad_id]
>> )
>>
>> On Sun, Apr 22, 2012 at 9:26 AM, Massimo Di Pierro <
>> [email protected]> wrote:
>>
>>> This is what I do not understand. If the query is done by the grid, it
>>> should limit the number of records to the number of records displayed.
>>> If that now happening? Are you calling the query outside of the grid?
>>>
>>>
>>> On Sunday, 22 April 2012 11:22:32 UTC-5, Bruce Wade wrote:
>>>>
>>>> Yes it has built in pagination, however some of my grids will return
>>>> over 500,000+ records which causes query's to be very very very slow. I
>>>> would rather limit the returned data because the average person is not
>>>> going to go through all them records or care about them. For example
>>>> currently there has been around 19 million adviews each of these records
>>>> contains a rating, however there is no way SQLFORM.grid can handle loading
>>>> that much data.
>>>>
>>>> For some data I can use a date range however other tables we are not
>>>> storing a date field so I can not use that process to limit the results.
>>>>
>>>> On Sun, Apr 22, 2012 at 9:13 AM, Massimo Di Pierro <
>>>> [email protected]> wrote:
>>>>
>>>>> grid has built-in pagination. Perhaps I do not understand the
>>>>> question. Can you show us your code?
>>>>>
>>>>>
>>>>> On Sunday, 22 April 2012 10:40:37 UTC-5, Bruce Wade wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> Is there a way to limit the returned results for the grid? I am
>>>>>> finding that my site has so much data already that most areas where I am
>>>>>> using grid are failing <class 'psycopg2.InternalError'> current
>>>>>> transaction is aborted, commands ignored until end of transaction block
>>>>>>
>>>>>> If there is no way to limit the results is there a specific reason
>>>>>> why this was never included?
>>>>>>
>>>>>> --
>>>>>> --
>>>>>> Regards,
>>>>>> Bruce Wade
>>>>>> http://ca.linkedin.com/in/**bruc****elwade<http://ca.linkedin.com/in/brucelwade>
>>>>>> http://www.wadecybertech.com
>>>>>> http://www.fittraineronline.**co****m<http://www.fittraineronline.com>-
>>>>>> Fitness Personal Trainers Online
>>>>>> http://www.warplydesigned.com
>>>>>>
>>>>>>
>>>>
>>>>
>>>> --
>>>> --
>>>> Regards,
>>>> Bruce Wade
>>>> http://ca.linkedin.com/in/**bruc**elwade<http://ca.linkedin.com/in/brucelwade>
>>>> http://www.wadecybertech.com
>>>> http://www.fittraineronline.**co**m <http://www.fittraineronline.com>-
>>>> Fitness Personal Trainers Online
>>>> http://www.warplydesigned.com
>>>>
>>>>
>>
>>
>> --
>> --
>> Regards,
>> Bruce Wade
>> http://ca.linkedin.com/in/**brucelwade<http://ca.linkedin.com/in/brucelwade>
>> http://www.wadecybertech.com
>> http://www.fittraineronline.**com <http://www.fittraineronline.com> -
>> Fitness Personal Trainers Online
>> http://www.warplydesigned.com
>>
>>
--
--
Regards,
Bruce Wade
http://ca.linkedin.com/in/brucelwade
http://www.wadecybertech.com
http://www.fittraineronline.com - Fitness Personal Trainers Online
http://www.warplydesigned.com
