Easier query layer creation

[Bud]

I find query layers are a bit fiddly to set up. They take just a little bit longer to create than I'd like, so I don't use them as often as I should.

Believe it or not, creating a database view using a SQL client is often faster:

create or replace view ad_hoc_vw as select * from roads where ...  

It seems kind of backwards to create database views for ad-hoc analysis. That's where query layers ought to come in, since they're temporary in the map, not persistent in the database like views.

---

Part of the problem is that we need to connect to the database in question when creating the query layer. That takes ~20 seconds, which is an eternity when you're in a hurry.

Could the Create Query Layer window be changed so that it re-uses existing database connections that are already connected in Catalog? In other words, why do we need to re-connect when there is already an open connection?

Is there anything else that can be done to make creating a query layer easier/more streamlined, to facilitate fast-paced ad-hoc analysis?

Related: Virtual Attribute Fields (ad hoc)

[TanuHoque]

@Bud 

The New Query Layer dialog does show database connections from the Catalog pane.

are you not seeing them? or are they not working for you?

[Bud]

@TanuHoque I can see the .SDE connections in the New Query Layer dialog, yes. But those connections are not open/connected. I would need to reconnect to a .SDE connection in the New Query Layer dialog in order to use it to create a query layer. I want to avoid the reconnecting step since connecting takes too long. The connection would ideally be pre-connected in the New Query Layer dialog, if it’s already connected in catalog.

ArcGIS Pro 2.6.8

[TanuHoque]

@Bud 

Ah, I see... I'd expect it be connected from that dialog too, if you already have it connected from the Catalog pane. I will bring it to the team.

thanks

[Bud]

@TanuHoque 

Or, maybe the slowness happens when the Create Query Layer dialog populates the List of Tables section:

I suppose I could just ignore the fact that the List of Tables takes 20 seconds to populate. Since I don't usually need that list; I just write the SQL manually in the Query section.
I didn't realize until now that I can write SQL in the Query section while the List of Tables is thinking.

[Bud]

Some changes that could be made to that dialog that would save time:

1. Don't force me to click Validate. Let me attempt to create a query layer using the inputs that I've entered.
2. Automatically use the OBJECTID column, if it exists and is an integer:
   
3. Any other ideas?

Does @Luke_Pinner have any ideas for making query layers more practical to create/use? Related: query layers are fiddly.

[TanuHoque]

@Bud 

With one of the newer Pro versions, you have the option to list tables only when you need them. This should help.

[SSWoodward]

Thanks for all the ideas @Bud. 

The original Idea here seems to have been cleared up in the comments. The Create Query Layer window re-uses existing database connections that are already connected in Catalog. 

I see that you've added a few new additional ideas on in the comments.  Additional Ideas should be posted on their own to collect kudos and allow us to understand what specific ideas users are supporting when they give kudos to them. 

If there is a different specific Idea you'd like to refocus this post on, go ahead and edit it into the top post here, otherwise, this Idea should be closed as Already Offered. 

[Bud]

@SSWoodward 

Feel free to close. I submitted a separate idea: Query Layer — Make validation optional to save time

[SSWoodward]

Thanks @Bud!