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é

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

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é

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.

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.

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.

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.

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.

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.

Is this still on your radar?

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

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.

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

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.

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

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.

Hooray!