[web2py] Re: Web2py dal significantly slower than raw query

[Niphlod]

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.

[web2py] Re: Web2py dal significantly slower than raw query

[urban....@gmail.com]

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.

[web2py] Re: Web2py dal significantly slower than raw query

[Niphlod]

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

[Jim S]

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

[urban....@gmail.com]

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

[Jim S]

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.