Exclude excluded fields in generated query

Posts   
 
    
nweinit
User
Posts: 21
Joined: 16-Nov-2016
# Posted on: 16-Nov-2016 23:31:37   

Starting from version 4 you are including excluded fields in the generated query, this is described in your breaking changes v4.0 (http://www.llblgen.com/documentation/4.0/llblgen%20pro%20rtf/migratingcode.htm) This however is causing us a lot of issues as the generated code become really large when the table/view we're querying has many fields. The problem is that the DB optimizer (Oracle in this case) can't handle such lengthy queries, it also messes up the query cache. To top it all off, since the generated queries are fully qualified, it adds some extra length to the already lengthy query. I've seen a way to remove the schema name from the query (even though you're not encouraging it) but that causes a lot of side affects for us.

Can you provide any guidelines to alleviate both issues? (Excluded fields being more important to remove). Thanks

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Nov-2016 07:35:54   

To be precise, I quote the relevant breaking change:

Docs wrote:

Excluded fields are now enlisted in the resultset as: NULL as fieldname. This means the resultset layout is always the same, excluded fields or not. In general this isn't a breaking change, but if you rely on the resultset not having NULL values for excluded fields, your code needs adjustment. The change was made to make the resultset always be the same, to make code consuming the resultset easier and faster.

IMHO, I don't see a way of skip this. You mentioned:

nweinit wrote:

The problem is that the DB optimizer (Oracle in this case) can't handle such lengthy queries, it also messes up the query cache.

Please elaborate more on that, and how it represents a real problem in your scenario.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Nov-2016 10:46:09   

While the DQE can be altered to remove the fields in the query, this isn't going to work, as the resultset is used as-is for entity instances, so if the fields aren't in the query, the resultset has less columns and therefore the row won't match the entity.

Is this a known limitation on Oracle? I've never heard about this. How many fields in the query in total do you have which causes this problem?

All fields are likely also aliased with different cased names (e.g. CUSTOMERID AS "CustomerId") which also causes the query to be longer than necessary)

Frans Bouma | Lead developer LLBLGen Pro
nweinit
User
Posts: 21
Joined: 16-Nov-2016
# Posted on: 17-Nov-2016 19:47:55   

I've been told that the optimizer need to parse the query before it can perform any optimization, with a set allotted time to optimize each query, parsing a lengthy query consumes the majority of time of the optimization window. In addition, query cache can hold a certain size of query(s) and with each query being large, the amount of queries in the cache gets thrown out more rapidly.

To give you a rough estimation of the query length consider this: The amount of excluded fields we're getting is in the hundreds where the amount of requested fields are in the (lower) tens.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Nov-2016 20:59:31   

You can map additional entities on the same tables for the purposes where you don't need the excessive amount of fields.

LLBLGen Pro supports table splitting, i.e. you can map more entities on the same table, which all have a 1:1 relationship with each other, and where they all have a subset of the fields (plus PK field).

It's likely you don't need all the fields ever. So you have table Customer, and you map 'Customer' on that table with the PK and 10 fields, then 'SpecialCustomer' also to that table, with 1:1 relationship with Customer over PK and you add additional fields of the table to SpecialCustomer. 'Customer' is the PK side of the 1:1 so inserting goes OK. It's of course required the fields not in 'Customer' are all nullable in the DB otherwise inserting a Customer will fail.