Field order in UCs

Posts   
 
    
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 25-Sep-2013 12:50:23   

Hi,

Quick question: Is there a way to set the field order for UCs in the designer?

The fields are now used in alphabetic order in the scripts. Sometimes another order is desired, so the index can also be used for sorting or filtering.

Using September 2nd build.

-- André

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 25-Sep-2013 19:18:33   

The generated DDL scripts are a starting point, so you may very well change the scripts as you wish before deploying them.

twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 25-Sep-2013 19:40:13   

Walaa wrote:

The generated DDL scripts are a starting point, so you may very well change the scripts as you wish before deploying them.

Of course I can change the script, but sometimes a UC is dropped and added again and then I would have to remember to do the change again.

AFAIK PKs are generated in field order, so perhaps this is also possible for the UCs.

-- André

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-Sep-2013 11:55:08   

Frankly, I didn't know the order of fields in a UC mattered. Do you have more information about this? A multiple-field index is an index where values of all fields participating are stored together, not chained. (so to obtain a value from the index, it will simply use the values of all fields). That's also why you can't re-use a multiple-field index in predicates which use a subset of the fields of the index.

So I'm a bit surprised the order matters, and if so, what the best order should be.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 26-Sep-2013 19:57:21   

Apart from the UC SQL Server also creates an unique index, with the fields in the same order. This index can be used in queries and this is where the order of the fields does matter.

I have a table with a UC on OrderId and LineId. The update script was generated with the fields in alphabetical order, so LineId before OrderId. An unique index was also added with the fields in the same order.

Joining this table with the order was slow. Just changing the order of the fields in the UC, and thus the index, fixed this. This way the index serves 2 purposes. No additional index on OrderId is needed.

I tried setting the field order. This works for setting the fields in the right order in PKs, but apparently it does not for UCs.

Like Walaa suggested I can of course change the generated script, but when some change in the table requires a drop and add of the UC, the fields are again in the wrong order and this can easily be missed.

It is not a very big problem. I was just wondering if I overlooked something in the designer.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Sep-2013 11:16:59   

Ok, but what's the right order? Alphabetical is just a different ordering than some other ordering.

The join was slow because the 2-field index wasn't used, I think (plan statistics would reveal that), as the index is a 2-value index, not a 1 value. For a join over 1 field (1 field being part of the UC) you need to add an additional index on that field.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 27-Sep-2013 12:12:22   

Otis wrote:

The join was slow because the 2-field index wasn't used, I think (plan statistics would reveal that), as the index is a 2-value index, not a 1 value. For a join over 1 field (1 field being part of the UC) you need to add an additional index on that field.

So, how come just reversing the field order in the UC fixed the performance problem?

IMO it is because the field used in the join is now the first in the index. Which leads me to believe the field order does matter. There is no 'right' order. It depends on the situation. That is why I asked if the order could be set in the designer.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 27-Sep-2013 13:07:23   

twaindev wrote:

Otis wrote:

The join was slow because the 2-field index wasn't used, I think (plan statistics would reveal that), as the index is a 2-value index, not a 1 value. For a join over 1 field (1 field being part of the UC) you need to add an additional index on that field.

So, how come just reversing the field order in the UC fixed the performance problem?

IMO it is because the field used in the join is now the first in the index. Which leads me to believe the field order does matter.

I did some searching again (my previous attempts were unsuccessful) and I dug up this:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5671539468597

so for oracle at least it does make a difference indeed. Which suggests, for sqlserver and others it will as well but it depends on how indexes are stored internally.

There is no 'right' order. It depends on the situation. That is why I asked if the order could be set in the designer.

OK, though it will be mitigated if you need a query which is not using the index fields in the order specified in the designer, which is very likely of course (e.g. a query on itemid in your case, or a query with just 1 FK field in the compound FK).

We'll see if we can add it in a future version.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 27-Sep-2013 13:54:17   

Otis wrote:

We'll see if we can add it in a future version.

OK, great. And this confirms I did not overlook a feature in the designer.

twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 20-Sep-2018 21:26:56   

Is this still on your radar?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Sep-2018 10:14:15   

Yes, but not for 5.5, but for a 5.x version.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Jan-2020 15:19:53   

In v5.7, we've implemented a way to preserve the order of the fields of a UC as defined in the database. So if you have a UC in the database and you sync your project with this database, the order of the fields in the UC in the database is the order of the fields in the meta-data and scripts. It's not the order of the fields in the UC in the entity. Model first will still dictate ordering of the fields alphabetical. Fixing that requires cross database synchronization (as which database is leading if you have sqlserver and postgresql and you sync your model?) and a lot more code for an edge case so we'll not implement that at this time.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 15-Jan-2020 15:57:03   

Bummer, because I'm just interested in a solution for model first. I thought you could solve this the way it works for PKs (obey field order).

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Jan-2020 16:07:33   

Hmm, you mean the overall field order?

We could re-use that as the order of the fields in the UC indeed, didn't think of that. Ok, seems logical. If there's no field ordering, we'll fall back to alphabetical (like for field ordering/pk fields) and if there is one, we'll keep that one for new UCs being generated from UCs using model first.

In the generated code the order will kept as before, alphabetical, as it otherwise breaks code, but model first element generation doesn't break code.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 15-Jan-2020 16:32:22   

For PKs I can change the field order to force the required column order in the generated DDL.

All I wish is that this works the same for UCs, since in SQL Server every UC also has an index. Because UCs are now in alphabetical order I have to change the DDL manually. And because I'm old I sometimes forget simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 16-Jan-2020 12:21:45   

simple_smile

This now works as expected (in v5.7). Field ordering is required. I have 2 fields ZUcfield1 and AUcfield2. I want ZUCfield1 to be the first field in the UC. I specify field ordering so I have ZUcField1 above AUcfield2. Then I sync the model model-first and I get a new UC with zucfield1 first, then acfield1.

Changing the field order however won't affect the existing uc in the meta-data.

Interestingly, the generated code already had the fields in 'field ordering' order.

Frans Bouma | Lead developer LLBLGen Pro
twaindev avatar
twaindev
User
Posts: 178
Joined: 08-Oct-2007
# Posted on: 16-Jan-2020 12:49:23   

Hooray! smile