Your virtual fields are doing one select for each record of the parent
select. You should use joins.
On Feb 16, 12:19 am, Kurt Grutzmacher <gr...@jingojango.net> wrote:
> I have three tables that aren't particularly large (1500, 2500, and
> 3500 records in each). With VirtualFields enabled a select() query
> take a very long time to process.
>
> class VirtualFields(object):
> def name(self):
> return self.table1.table2.name
> def email(self):
> return self.table1.table3.email
> def phone(self):
> return self.table1.table3.phone
> db.table1.virtualfields.append(VirtualFields())
>
> Query: db(db.table1.id <= 1500).select()
>
> Virtual fields enabled: 69185322 function calls (69042017 primitive
> calls) in 99.450 seconds
> No virtual fields: 357819 function calls (357815 primitive calls) in
> 0.376 seconds
>
> cProfile output with virtual fields enabled:
>
> Ordered by: standard name
>
> ncalls tottime percall cumtime percall
> filename:lineno(function)
> 1 0.001 0.001 99.450 99.450 <string>:1(<module>)
> 2583/1 0.022 0.000 99.449 99.449 dal.py:1004(select)
> 2583 0.007 0.000 1.176 0.000 dal.py:1008(response)
> 7749/2583 0.025 0.000 0.049 0.000 dal.py:1045(tables)
> 2583 0.008 0.000 1.135 0.000 dal.py:1086(log_execute)
> 2583 0.004 0.000 1.139 0.000 dal.py:1090(execute)
> 2583 0.010 0.000 0.017 0.000 dal.py:1093(represent)
> 2583 0.001 0.000 0.001 0.000 dal.py:
> 1160(represent_exceptions)
> 2583 0.001 0.000 0.001 0.000 dal.py:1169(rowslice)
> 2583/1 0.701 0.000 99.436 99.436 dal.py:1173(parse)
> 8397955 12.372 0.000 13.636 0.000 dal.py:3225(__getitem__)
> 5089122 5.700 0.000 5.700 0.000 dal.py:3234(__setitem__)
> 6666724 4.173 0.000 14.886 0.000 dal.py:3237(__getattr__)
> 7746 0.005 0.000 0.013 0.000 dal.py:3240(__setattr__)
> 145822 0.182 0.000 0.252 0.000 dal.py:3510(__getitem__)
> 2583 0.006 0.000 0.007 0.000 dal.py:3513(__setitem__)
> 48996 0.033 0.000 0.111 0.000 dal.py:3516(__getattr__)
> 2583 0.004 0.000 0.010 0.000 dal.py:3519(__setattr__)
> 2583 0.004 0.000 0.008 0.000 dal.py:3528(__call__)
> 5000043 2.280 0.000 5.662 0.000 dal.py:3631(__allocate)
> 5000043 5.543 0.000 13.679 0.000 dal.py:3637(__getattr__)
> 25820 0.029 0.000 0.040 0.000 dal.py:3643(__setattr__)
> 144605/19378 0.288 0.000 3.422 0.000 dal.py:
> 3827(__getitem__)
> 20658 0.016 0.000 0.077 0.000 dal.py:3895(__getattr__)
> 50361 0.036 0.000 0.163 0.000 dal.py:3903(__iter__)
> 14201 0.044 0.000 0.093 0.000 dal.py:4113(__eq__)
> 2583 0.008 0.000 0.017 0.000 dal.py:4116(__ne__)
> 1 0.000 0.000 0.000 0.000 dal.py:4122(__le__)
> 50361 0.037 0.000 0.037 0.000 dal.py:4488(__str__)
> 16785 0.018 0.000 0.018 0.000 dal.py:4509(__init__)
> 14202 0.012 0.000 0.012 0.000 dal.py:4565(__init__)
> 2583/1 0.009 0.000 99.449 99.449 dal.py:4598(select)
> 2583 0.003 0.000 0.003 0.000 dal.py:4663(__init__)
> 1291 19.583 0.015 99.071 0.077 dal.py:
> 4677(setvirtualfields)
> 2582 0.007 0.000 0.014 0.000 dal.py:4718(__getitem__)
> 2582 0.004 0.000 0.018 0.000 dal.py:4742(first)
> 2582 0.004 0.000 0.032 0.000 dal.py:761(EQ)
> 1 0.000 0.000 0.000 0.000 dal.py:774(LE)
> 55527/50361 0.054 0.000 0.123 0.000 dal.py:807(expand)
> 2583 0.116 0.000 0.535 0.000 dal.py:911(_select)
> 2583 0.015 0.000 0.015 0.000 dal.py:
> 998(select_limitby)
> 1666681 6.140 0.000 16.133 0.000 db_02_autospang.py:
> 181(f_nexid)
> 1666681 5.044 0.000 11.345 0.000 db_02_autospang.py:
> 185(f_proto)
> 1666681 5.342 0.000 13.868 0.000 db_02_autospang.py:
> 189(f_number)
> 29627 0.018 0.000 0.073 0.000 utf_8.py:15(decode)
> 29627 0.055 0.000 0.055 0.000 {_codecs.utf_8_decode}
> 61968 0.080 0.000 0.080 0.000 {built-in method match}
> 1666681 18.971 0.000 18.971 0.000 {dir}
> 6666724 1.384 0.000 1.384 0.000 {getattr}
> 6669306 3.894 0.000 7.651 0.000 {hasattr}
> 547359 0.130 0.000 0.130 0.000 {isinstance}
> 5165 0.001 0.000 0.001 0.000 {len}
> 2583 0.000 0.000 0.000 0.000 {method 'add' of 'set'
> objects}
> 55524 0.010 0.000 0.010 0.000 {method 'append' of
> 'list' objects}
> 29627 0.035 0.000 0.109 0.000 {method 'decode' of
> 'str' objects}
> 1 0.000 0.000 0.000 0.000 {method 'disable' of
> '_lsprof.Profiler' objects}
> 29627 0.042 0.000 0.042 0.000 {method 'encode' of
> 'unicode' objects}
> 2583 1.116 0.000 1.116 0.000 {method 'execute' of
> 'psycopg2._psycopg.cursor' objects}
> 2583 0.029 0.000 0.029 0.000 {method 'fetchall' of
> 'psycopg2._psycopg.cursor' objects}
> 13418662 3.740 0.000 3.740 0.000 {method 'get' of 'dict'
> objects}
> 144605 0.014 0.000 0.014 0.000 {method 'isdigit' of
> 'str' objects}
> 1666681 0.527 0.000 0.527 0.000 {method 'items' of
> 'dict' objects}
> 7748 0.004 0.000 0.004 0.000 {method 'join' of 'str'
> objects}
> 7747 0.003 0.000 0.003 0.000 {method 'keys' of 'dict'
> objects}
> 294302 0.137 0.000 0.137 0.000 {method 'lower' of 'str'
> objects}
> 64551 0.055 0.000 0.055 0.000 {method 'split' of 'str'
> objects}
> 243666 0.077 0.000 0.077 0.000 {method 'startswith' of
> 'str' objects}
> 59397 0.009 0.000 0.009 0.000 {method 'strip' of 'str'
> objects}
> 5166 0.002 0.000 0.002 0.000 {method 'union' of 'set'
> objects}
> 1666681 1.224 0.000 1.224 0.000 {method 'update' of
> 'dict' objects}
>
> Suggestions?
