I'll try to explain better.
I have to exctract some statistics from a radius accounting table that
is generally a really big table.
After exctracting tha data, i pass it to jqplot.com via a json service
to be rendered.
For some graphics, I have to exctract a big amount of dates from
accounting timestamps, and I cannot make it every other way. I have to
exctract a list of dates + something else like counts avarages.
I made a test:
@service.json
def test():
from datetime import datetime
tstart = datetime.now()
rows = dbradius(dbradius.radacct).select(dbradius.radacct.AcctStartTime)
for row in rows: row.AcctStartTime = row.AcctStartTime.date()
tstop = datetime.now()
print tstop - tstart
return rows
@service.json
def test1():
from datetime import datetime
tstart = datetime.now()
rows = dbradius.executesql('''SELECT DATE(AcctStartTime) FROM radacct''')
tstop = datetime.now()
print tstop - tstart
return rows
In my test with 60000 records, test1 function completes in around 30
secs. The second one in about 15. As you can see the only visible
difference is traversing the list insted of use sql to excract the
dates.
I think that the problem get worse with many more records. I think
it's wasteful use a for cycle to excrtact dates or time when an sql
construct could be better.
Anyway I think I'm stick with the sql version, thank you to all of you
for the help!
Angelo
2011/7/21 Massimo Di Pierro <massimo.dipie...@gmail.com>:
> You can just call row.data.date() instead of row.data when you need
> the value.
>
>
> On Jul 21, 10:49 am, Angelo Compagnucci <angelo.compagnu...@gmail.com>
> wrote:
>> Thanks Massimo!
>>
>> So I must traverse the list at least one time. I dont know how much
>> time is spent in this operation, but I have a very large number of
>> records, so I'll test this solution.
>>
>> Thank you!
>>
>> 2011/7/21 Massimo Di Pierro <massimo.dipie...@gmail.com>:
>>
>>
>>
>>
>>
>>
>>
>> > No that does not work for the reasons discussed above but this does
>> > def getcommentsbydate():
>> > rows = db(db.test).select(db.test.data.comment, db.test.data)
>> > for row in rows: row.data = row.data.date()
>> > return dict(rows=rows)
>>
>> > On Jul 21, 8:06 am, Angelo Compagnucci <angelo.compagnu...@gmail.com>
>> > wrote:
>> >> Sorry for being retarted!
>>
>> >> I have this:
>>
>> >> db.define_table('test', Field('comment','string'),
>> >> Field('data','datetime'))
>>
>> >> but the function:
>>
>> >> def getcommentsbydate():
>> >> rows = db(db.test).select(db.test.data.comment, db.test.data.date())
>> >> return dict(rows=rows)
>>
>> >> throws an exception complaining that the date() method does not exist.
>>
>> >> How can I exctract the date without traverse the rows object? I dont
>> >> want to use a list comprehension or something else because with
>> >> thousands of records is painfull slow (I'm thinking of a radius
>> >> accounting table ...)!
>>
>> >> I think there is nothing in the DAL that translates to a "DATE()" sql
>> >> function, am I wrong?
>>
>> >> Thank you for your suggestion!
