topic Re: Make Query Layer from Two Databases in ArcGIS Enterprise Questions

Make Query Layer from Two Databases

WalterDziuba1 — Wed, 17 May 2017 18:04:00 GMT

Afternoon,

I'm attempting to create a query layer from a feature class in our SDE database plus three additional tables from an external database that is stored in the same SQL Server instance. The SQL statement supplied contains four joins. One to our feature class, and the remaining three to the external database.

I'm using the "Make Query Layer" tool set. It accepts the SQL statement supplied and I select three columns to be the unique identifiers. Great so far.

My Question: Is the query layer dynamic? Meaning, does it react like a view where it will continue to read data from the three external tables when additional data is added to those external tables?

And finally, it's weird, but the results window states that the process was successful, but I cannot find the output query layer anywhere in my database...!!

Walter

Re: Make Query Layer from Two Databases

StevePtak1 — Wed, 17 May 2017 20:17:40 GMT

Hi,

I know query layers are dynamic in the sense that the query is executed every time the layer is displayed. In my experience, query layers perform best when you specify 1 unique identifier field.

However, since a query layer requires only 1 database connection, I don't think it's possible to make a query layer using data from multiple databases directly in arcgis.

Another option to explorer - I have never tried it, but I know you can use database links to make a connection across multiple databases, then create a view from that which arcgis can read those joins.

There's more on this topic here - Distributed Database Concepts

Hope this helps!

- Steve

Re: Make Query Layer from Two Databases

George_Thompson — Wed, 17 May 2017 20:25:26 GMT

To add to what Steve said: yes, the query layer is dynamic. It does not create an object in the database. Query layers can only be stored in a specific MXD (Map Documnet). If you need it to persist in the database, look at creating at Create a database view in ArcGIS Desktop—Help | ArcGIS Desktop

What is a query layer?—Help | ArcGIS Desktop

https://community.esri.com/groups/geodatabase?sr=search&searchId=27f97d8d-f4ed-46ad-a335-3664f0e035e6&searchIndex=0‌

Re: Make Query Layer from Two Databases

WalterDziuba1 — Thu, 18 May 2017 11:58:25 GMT

Thank you gentlemen for your responses. We currently use SQL Server as our database. I just need to find the most efficient way of extracting data from another database on the same instance and linking it to our GIS map services.

Most views I create from this data is in the 300,000 record range. Currently, I'm using a 1:M ArcMap "Relate" and then publishing the map service to consume on the other end ie. Geocortex

But some of our map services ie. Ground Water needs to link to 5x categories of data with an approx. record total of over half million records.

My Question: What is the most efficient way of accomplishing this task?

Walter

Re: Make Query Layer from Two Databases

VinceAngelo — Thu, 18 May 2017 13:42:36 GMT

There are a number of definitions of "efficient" in this context. Cross-database join performance, to paraphrase John Nance Garner, isn't worth, err..., much. The fastest response query would involve replicating an ideal subset of the join data locally (possibly even in denormalized form), then querying that from your app.

- V

Re: Make Query Layer from Two Databases

WalterDziuba1 — Thu, 18 May 2017 13:57:38 GMT

Vince,

Interesting concept....."Replicating an ideal subset on the join data locally"

Could you please expand on this?

Walter

Re: Make Query Layer from Two Databases

VinceAngelo — Thu, 18 May 2017 14:09:06 GMT

Not at this time, but there should be plenty of discussion on the topic if you look for it. The are many variables to consider, especially frequency of update.

- V