Make Query Layer from Two Databases

[WalterDziuba1]

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

[StevePtak1]

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

[George_Thompson]

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-3664f0e035e...‌

[WalterDziuba1]

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

[VinceAngelo]

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

[WalterDziuba1]

Vince,

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

Could you please expand on this?

Walter

[VinceAngelo]

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