how many rows and how many columns ? the raw one is returning whatever type the underlying database structure has as it is, the pydal one is building the whole model (including references) did you try with cacheable=True in the pydal one ? it won't build update_records and delete_records, for starters.
On Monday, October 2, 2023 at 8:04:04 PM UTC+2 Jim S wrote: > The only thing I can see is that the SQL needs to be 'built' by pydal, but > I find it hard to believe it takes a whole second. Massimo might have to > add context here. > > -Jim > > On Monday, October 2, 2023 at 1:00:19 PM UTC-5 urban....@gmail.com wrote: > >> Sorry my bad! I mixed up the timings when editing the post. >> >> The slower timing is for the dal version. >> Moreover the dal version is slower even if I remove the .as_list() call. >> I had originally tried that. >> When I get the time I'll try "debugging" it by looking at the dal.py >> source. >> Asking here if anyone knows an obvious reason for this that I am missing. >> On Monday, October 2, 2023 at 5:53:47 PM UTC+2 Jim S wrote: >> >>> It's possible I'm reading this wrong (it is Monday morning), but .09s >>> (DAL) is faster than 1.8s (raw SQL). >>> >>> Is that a typo? Or, is it my Monday-morning-brain? >>> >>> If your raw query is slower, could it be because you're converting to a >>> dict instead of a list as in your dal query? >>> >>> -Jim >>> >>> On Monday, October 2, 2023 at 3:07:26 AM UTC-5 urban....@gmail.com >>> wrote: >>> >>>> ids = tuple(m['id'] for m in relevant_models) >>>> raw_q = db.executesql(""" >>>> SELECT >>>> * >>>> FROM "table" >>>> WHERE ("table".ref_id" IN {}); >>>> """.format(str(ids)), as_dict=True) >>>> #################### 1.8s >>>> >>>> ids = tuple(m['id'] for m in relevant_models) >>>> dal_q = db( >>>> db.table.ref_id.belongs(ids) >>>> ).select(db.table.ALL).as_list() >>>> ##################### 0.09s >>>> *Web2Py 2.22.3* >>>> >>>> Why would the dal query be so much slower than the raw sql? The >>>> generated sql (db._lastsql) is the same as the raw. >>>> >>>> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/21b30c28-5378-416f-86de-3e686e8023dbn%40googlegroups.com.