Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Designer> Field order in UCs
 

Pages: 1
Designer
Field order in UCs
Page:1/1 

  Print all messages in this thread  
Poster Message
twaindev
User



Location:
The Netherlands
Joined on:
08-Oct-2007 21:02:40
Posted:
156 posts
# 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é
André Prins
Software Architect & Developer
Twain Development
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14470 posts
# 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.

  Top
twaindev
User



Location:
The Netherlands
Joined on:
08-Oct-2007 21:02:40
Posted:
156 posts
# 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é
André Prins
Software Architect & Developer
Twain Development
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37405 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
twaindev
User



Location:
The Netherlands
Joined on:
08-Oct-2007 21:02:40
Posted:
156 posts
# 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.
André Prins
Software Architect & Developer
Twain Development
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37405 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
twaindev
User



Location:
The Netherlands
Joined on:
08-Oct-2007 21:02:40
Posted:
156 posts
# 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.
André Prins
Software Architect & Developer
Twain Development
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37405 posts
# 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:
Code:
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.

Quote:

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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
twaindev
User



Location:
The Netherlands
Joined on:
08-Oct-2007 21:02:40
Posted:
156 posts
# 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.
André Prins
Software Architect & Developer
Twain Development
 
Top
twaindev
User



Location:
The Netherlands
Joined on:
08-Oct-2007 21:02:40
Posted:
156 posts
# Posted on: 20-Sep-2018 21:26:56.  
Is this still on your radar?

André Prins
Software Architect & Developer
Twain Development
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37405 posts
# Posted on: 21-Sep-2018 10:14:15.  
Yes, but not for 5.5, but for a 5.x version.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.