Cross referencing to UD table

(Brandon Anderson) #1

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.

Thanks

(Mark Damen) #2

No need to do any join between base table and UD table.

Just query from the VIEW instead, which is prefixed dbo.TableName rather than ERP.TableName. The view contains all the fields from both tables.

Example

Erp.Part Base Epicor Table

Erp.Part_UD Extended UD Table

Dbo.Part SQL View automatically created by Epicor, which contains all fields from both tables.

(Brandon Anderson) #3

It’s not an extended UD field. It’s a totally different table UD08 in this case. (due to JobEntryBO performance problems)

(Nathan Woolen) #4

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.

Nathan

(Jose C Gomez) #5

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

(Brandon Anderson) #6

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.

Thanks for you input guys!

(Jose C Gomez) #7

Depending on the number of records , not using the Index could be significantly noticeable.