Getting back to this discussion.
I have a table containing 31.000 record. Model below.
db.define_table('tracker_person_product',
db.Field('troop', db.troop, required=True),
db.Field('troop_person', db.troop_person, required=True),
db.Field('campaign', db.campaign, required=True),
db.Field('product', db.product, required=True),
db.Field('count_received', 'integer', required=True,
requires=IS_INT_IN_RANGE(0, 1000000)),
db.Field('count_sold', 'integer', required=True,
requires=IS_INT_IN_RANGE(0, 1000000)),
db.Field('count_returned', 'integer', required=True,
requires=IS_INT_IN_RANGE(0, 1000000)),
db.Field('count_paid', 'integer', required=True,
requires=IS_INT_IN_RANGE(0, 1000000)),
db.Field('edited_by', db.auth_user,default=auth.user_id,
required=True),
db.Field('edited_date', 'datetime', required=True),
format='%(count_received)s',
migrate=settings.migrate)
Queryn all records (record=db(db.tracker_person_product).select())
takes 47 seconds. Just executing one row of python code. Not even
showing all records.
Is there something I can do to reduce query time? 31.000 records just
doesn't sound like that much yet.
Kenneth
---------- Forwarded message ----------
From: Kenneth Lundström <[email protected]>
Date: Dec 27 2010, 9:14 am
Subject: Server slow
To: web2py-users
So it rather fast notslow:=)
But the edited_by column is not showing a name, it shows a number.
But that is not very intresting as I don t need a list like that, it
was
just a test. I m trying to optimize some reporting functions. I ll
have
to start from an other angle.
Kenneth
> Now I understand.
> The problem is here:
> Field('edited_by', db[user_table], required=True),
> When you do {{=orders}} is uses a default representation for
> edited_by. The field contains a user id and the default representation
> is the user name. So for each record it has to do a database lookup
> (980 of them).
> If you want to represent users by name, you should either cache them.
> Something like this:
> db.orders.edited_by.represent = lambda id: cache.ram('user:
> %i'%id,lambda:db.auth_user(id).first_name,3600)
> or turn your query into a join.
> Massimo
> On Dec 26, 11:37 pm, Kenneth Lundstr m<[email protected]>
> wrote:
>> What part of the model would you like to see?
>> This?
>> db.define_table('orders',
>> db.Field('name', 'string', length=40, requires=IS_NOT_EMPTY()),
>> db.Field('district', db.district, required=True),
>> db.Field('edited_by', db[user_table], required=True),
>> db.Field('edited_date', 'datetime', required=True),
>> db.Field('reward_selected', 'integer', default=0),
>> db.Field('members', 'integer', default=0),
>> db.Field('memb_no', 'string', default=0),
>> db.Field('locked', 'integer', default=False, required=True),
>> db.Field('cow_id', 'string', default=''),
>> migrate=settings.migrate)
>> So far there was no view defined for this controller. If I define a
>> empty one it takes like 2-3 seconds to show a empty page, controller
>> changed to dict(orders=orders).
>> If I define the view to be {{=orders}} it takes again 40 seconds to show.
>> The strange thing is if I change the view to
>> {{
>> for order in orders:
>> =XML(order)
>> pass
>> }}
>> it only takes about 2-3 seconds to show all orders. Not as nice as
>> =orders but very strange anyhow.
>> Kenneth
>>> I have never seen this before. Can I see the model?
>>> massimo
>>> On Dec 25, 3:51 pm, Kenneth Lundstr m<[email protected]>
>>> wrote:
>>>>> Is this reproducible?
>>>> This happens all the time, if I do it ten times in a row every time it
>>>> takes 38-39 seconds. Even with lynx on theserveritself it takes about 40
>>>> seconds.
>>>> If I use:
>>>> t0=time.time()
>>>> orders = db(db.orders.id> 0).select()
>>>> logging.info('time to fetch %s' % (time.time()-t0))
>>>> The time is 1.75 - 1.90 seconds to fetch the data from database
>>>> I change the controller to this
>>>> sql = db(db.orders.id> 0)._select()
>>>> t0=time.time()
>>>> db.executesql(sql)
>>>> logging.info('time to fetch %s' % (time.time()-t0))
>>>> orders=[]
>>>> now I get times like 0.38 - 0.39 seconds
>>>> If I instead of db.orders.id< 490) to get half the rows all times
>>>> drops to half, even 40 seconds is now 20 seconds.
>>>> Just testing I tried with:
>>>> t0=time.time()
>>>> orders = db(db.orders.id> 0).select()
>>>> orders1 = db(db.orders.id> 0).select()
>>>> orders2 = db(db.orders.id> 0).select()
>>>> orders3 = db(db.orders.id> 0).select()
>>>> orders4 = db(db.orders.id> 0).select()
>>>> logging.info('time to fetch %s' % (time.time()-t0))
>>>> return orders
>>>> This takes about, 5x1,8s (to fetch data) + 35 seconds = 45 seconds
>>>> I tried with a different table that contains about 1250 rows, 6 columns.
>>>> It takes 1.2-1.3 seconds to fetch the data, but then over 60 seconds to
>>>> display it.
>>>> Kenneth
