Web2py dal significantly slower than raw query

[urban....@gmail.com]

  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. 

[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

[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.

[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

[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. 

[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?

[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.