Think an int, that needs to be "casted" to an int for 50k times. You can't
expect an abstraction layer to be as fast as raw sql. DAL doesn't just
write queries for you, it adapts the resultset to the model. 50k rows in
less than 2 seconds is not that bad.
On Tuesday, October 3, 2023 at 8:29:12 AM UTC+2 urban....@gmail.com wrote:
>
> 12 columns, 50k rows (17k rows in the query result)
> cacheable=True is about 0.3s faster.
>
> The costliness of building the whole model comes (mostly) from reference
> types?
> On Monday, October 2, 2023 at 11:22:53 PM UTC+2 Niphlod wrote:
>
>> 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/2b768764-1060-48d0-844e-c302bff66130n%40googlegroups.com.
