Performance - better with all alt names as dup records or as alt name table?

Question

ESRI whitepaper suggests there is a significant performance hit for using an alt name table. One of our data vendors puts all alt names as duplicate records on the primary street table. The other uses an alt name table. Performance with the alt name table seems better, but there are too many other differences to make conclusions I think.

Is there a known answer to this question: better to have alt names as duplicate features in primary feature class or as alternate name table?

JoeBorgione · Answer

ESRI whitepaper suggests there is a significant performance hit for using an alt name table. One of our data vendors puts all alt names as duplicate records on the primary street table. The other uses an alt name table. Performance with the alt name table seems better, but there are too many other differences to make conclusions I think.

Is there a known answer to this question: better to have alt names as duplicate features in primary feature class or as alternate name table?

I have used alt-names tables for a number of years and geocoded countless thousands of addresses.  I don't know the specifics of the white paper you mention, but the last thing I would like to maintain (or pay for) is double (or more) features in the primary street table.  That to me just wreaks of performance hit, if not more headaches than I'm willing to suffer through. I will admit that I have what I call anomalies streets that have the same geometry as existing streets but with local names instead of 'official' or 'official aliases'.  I use these as part of a composite locator only when I have to, and with a 55,000 + primary streets feature class, I only have maybe 200 anomalies.

Recently I've been using a tool that creates an alt name of each individual word in a street name.  So, let's say you have a street called S MAIN ST.  It will create an alias of S MAIN, S, MAIN ST, MAIN, and ST.  It has the option to drop certain elements, so I don't have it create one for ST or S.  This creates a lot more aliases than I ever have in the past and I haven't noticed a hit in performance with it; these data are used for 9-1-1 dispatch.  If there is a performance hit, my phone starts ringing from 100 + dispatchers....

My thoughts are to stick with alt names. Be sure to index the join item in the primary streets as well as in the alt names table before you build your locator.

Hope this helps-

HarryBowman · Answer

ESRI whitepaper suggests there is a significant performance hit for using an alt name table. One of our data vendors puts all alt names as duplicate records on the primary street table. The other uses an alt name table. Performance with the alt name table seems better, but there are too many other differences to make conclusions I think.

Is there a known answer to this question: better to have alt names as duplicate features in primary feature class or as alternate name table?

From the ESRI whitepaper in 2009: "You can run an extract, transform, and load (ETL) process to derive a reference dataset that appends extra records having alternate name values into the primary names and achievethe benefits of speed and alternate name testing."

So, at least on Server in 2009, it would have been better to append the records. Is this still true in 2012? On Desktop?