[web2py] Web2py dal significantly slower than raw query
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/f8add820-3bc0-4220-af0e-ea58e7a18c64n%40googlegroups.com.
[web2py] Re: Web2py dal significantly slower than raw query
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/da6b265e-7f57-42f0-bea2-aad39ea56851n%40googlegroups.com.
[web2py] Re: Web2py dal significantly slower than raw query
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/cffb0608-3b6e-4eb1-9318-975eedb0779bn%40googlegroups.com.
[web2py] Re: Web2py dal significantly slower than raw query
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/338e6176-abbc-4bc2-80fe-8a97d13b5637n%40googlegroups.com.
[web2py] Re: Web2py dal significantly slower than raw query
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.
[web2py] Re: Web2py dal significantly slower than raw query
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/cb9040d8-968c-4900-b655-8c20625dc870n%40googlegroups.com.
