I have a question on whether it’s better to use the SysRowID to match up the UD table?
Currently I have Key1 as JobNum, Key2 as AsmSeq and Key3 as MtlSeq. I’m running a union query to get all Assemblies and Materials into one list, (Assemblies just have 0 for the MtlSeq). With that set up, I can run the Union query against what I have in the UD table and add or remove as necessary.
Now, after I have all that set up, I’m wondering if it would have been better to use the SysRowID instead? Theoretically, that would work just as well as the other keys (or maybe in conjunction with?). And would that run faster in a query? I still need to pull the Job Asm and Mtl tables to make keep these tables in sync, so I don’t know if there is anything that I can leave off the query, but would using that key make it run faster?
If anyone has any experience with this type of application that would lend any input, that would be greatly appreciated.
I only use your syntax when grabbing from a ICE ud table ie UD3x. Extended
table UD fields can be accessed using the Sysrowid. As long as you dont
call out the schema the alias will automatically bring back both tablesets.
For example I call part instead of erp.part. Using the schema would mean
that I would then have to join erp.part with erp.part_ud on sysrowid =
foreignsysrowid. Hope that makes sense.
The UD tables have 2 built in indexes
Index 1: Company, Key1, Key2, Key3, Key4, Key5
Index 2: SysRowID
Any query using either of these combinations would run relatively at the same speed. If you are only using some of they keys though it would be technically faster to use the SysRowID. Since only ALL of the fields would use Index 1
But probably not noticeably? There are slight advantages to using it the way I have it, being that if someone removes, then adds the same row (sometimes we are just indecisive here!!), then I don’t have a new SysRowID in my job tables, so I might be able to keep some of the other data that was previously populated. Otherwise that row will never match again and I have to start over.