Table Join in MXD

Permanently deleted user

26 September 2012 21:43

 

I have a parcel layer joined to a tax table (all in the same SDE database).

The workflows I have set up for Search by address, roll number and owner are all coming from the same layer (Assessment Parcels (with the join)).

Here is an example of the fields:

COE.KINGSVILLE.KingsvilleAssessment.OBJECTID (Type: esriFieldTypeOID, Alias: OBJECTID)

 

COE.KINGSVILLE.KingsvilleAssessment.ARN (Type: esriFieldTypeString, Alias: ARN, Length: 20 )

 

COE.KINGSVILLE.KingsvilleAssessment.CIVIC (Type: esriFieldTypeString, Alias: CIVIC, Length: 50 )

 

COE.KINGSVILLE.KingsvilleAssessment.Shape (Type: esriFieldTypeGeometry, Alias: Shape)

 

COE.KINGSVILLE.KINGSVILLETAX.OBJECTID (Type: esriFieldTypeInteger, Alias: OBJECTID)

 

COE.KINGSVILLE.KINGSVILLETAX.ARN (Type: esriFieldTypeString, Alias: ARN, Length: 254 )

 

COE.KINGSVILLE.KINGSVILLETAX.ROLLNMBR (Type: esriFieldTypeString, Alias: ROLLNMBR, Length: 15 )

 

COE.KINGSVILLE.KINGSVILLETAX.ROLL (Type: esriFieldTypeString, Alias: ROLL, Length: 254 )

 

COE.KINGSVILLE.KINGSVILLETAX.OWNER1 (Type: esriFieldTypeString, Alias: OWNER1, Length: 61 )

 

COE.KINGSVILLE.KINGSVILLETAX.OWNER2 (Type: esriFieldTypeString, Alias: OWNER2, Length: 61 )

They each have an autocomplete search box. For Roll Number and Address, the autocomplete works, but for the owner name it does not.

It will eventually come out with the results for owner name, but it is lagged, and again the autocomplete box does not work.

I have also had some inconsistences with the results I get from this layer when generating reports (ie. parcel report or mailing labels). Some moments it works fine and others I get an Error performing the query: [Unable to complete  operation.

Unable to complete Query operation.].

I have had the table join in place since ArcIMS days and have never had an issue with querying.

I am currently on version 3.10.1 and svl 1.6

Thanks Cathy

0

• 

  Permanently deleted user

  27 September 2012 14:16

  Hi Cathy

  how much indexing have you set up for fields in the parcel layer?, the tax table?

  Are you doing the 'autocompletes' against the joined table in every case?

  How about just pointing them at the tax table with it well indexed (ie not a joined view) so that you can quickly get values for the auto-complete and then do the search on the spatial data.

  Can you just have the tax table as linked data rather than 'permanently' joined to to it?

  How many records are in the parcel layer? How many in the tax table?

  I think that is probably enough questions for the evening ;-)

  Regards

  Ralph

  0
• 

  Permanently deleted user

  27 September 2012 20:55

  Hi Ralph,

  • There are indexes on the ownername, address fields
  • I am doing autocompletes on the CIVIC, ARN, and OWNER fields
  • The tax table should be permanently joined, not linked. I see no need in changing something that had been in place for years.  Worked in WebADF and IMS.
  • There are 9640 records in the tax table and 9371 records in the parcel layer for Kingsville.

  This is for one of the 7 municipalities that are in the County. Each municipality has their parcel layer separate from their tax table.

  There is no way I can tell each GIS technician to alter their database structure because of something that should work, something ESRI created for efficiency.

  I am sure someone else has come across this problem - table joins are fairly common.

  Cathy

   

  0
• 

  Permanently deleted user

  28 September 2012 04:37

  Hi Cathy

  merely suggestions ;-)

  however in my case I have a table (well indexed including full-text indexing) with 350,000 records in it with very good performance.

  When I suggested your auto-complete search to be on the tax table, you could do that without unjoining it. What version GE are you using?

  You could publish the tax table as a query layer and use that in the autocomplete. I am sure there are ways without changing your data structure.

  Regards

  Ralph

  0
• 

  Permanently deleted user

  04 Oktober 2012 02:40

  Thank you for your suggestions Ralph.  We are using GE 3.10.1

  Pointing the autocomplete to the table itself does make it much faster. (and without having to un-join)

  We also created a direct connection to the database for the parcel and tax table for speed and added indexes on both join fields.

  It is not perfect, but much better performance than before.

  Cathy

  0
• 

  Permanently deleted user

  04 Oktober 2012 08:07

  Hi Cathy

  Glad to hear that you have got some improved performance, just out of curiosity, what would make it perfect?

  Regards

  Ralph

  0

Du måste logga in om du vill lämna en kommentar.