This is more of a discussion question than a "help me find the right answer" question. I'm looking for others with experience using query layers in Pro. I don't use them a lot, but it's the right solution for a project currently under development. For context, I have Pro 2.8.1 installed on a Windows 10 64-bit machine. The query layers are pulling from SQL Server (currently using version 18) and I'm writing the queries on the ArcGIS Pro side rather than in SQL Server Management Studio.
With regard to "buggy" issues, I've noticed several undesirable, yet inconsistent behaviors when updating a query.
- When I click the edit button to modify the query, the option to name the Query is not available. Most of the time that's not a problem. Occasionally, it will prevent me from saving my updates because I get an error message that says that the name is empty (it's not, but Pro seems to think it is). **EDITED TO ADD screenshot:
- When I validate a query, it is generally quick to resolve. Sometimes, it hangs for several minutes. I've had to wait for at least 15 minutes on more than one occasion.
- When I advance to the screen to set unique identifiers, I am occasionally blocked from selecting a geometry type and/or a coordinate system. Again, I've waited 15+ minutes on this screen for the "hang" to resolve itself.
- After updating a query and clicking OK, the OK button on the Properties window also hangs on occasion. Again, wait times can be anywhere from seconds to 15+ minutes.
The biggest problem is that I cannot replicate the behavior consistently, so calling tech support isn't going to do any good. So, I thought it might be useful to post here and see if there are others experiencing this issue and/or have identified possible causes.
I love using query layers. I've occasionally seen similar issues to what you describe, though it seemed more likely to happen when my query was hitting a table that was not registered with the geodatabase. We also had some connectivity issues w/ our server in the past, and that made the query builder hang as well.
I never really identified a root cause, and we've since made some changes to our Enterprise GDB that makes query layers a bit less relevant to our workflow. But things you might try:
Also, is your DB using esri's spatial type for the geometry, or something else? If possible, you could try constructing your query somewhere else altogether to see if the performance changes. We use postgreSQL, and the program pgAdmin had a geometry viewer built-in, so we were able to develop and test query layer statements there first.
There were times when a query worked fine in pgAdmin, but failed in Pro, so I'd guess that Pro was just having some very Pro-specific issues with the query layer.
Your comment about rebuilding indexes made me go back and check all of my query layers. Turns out that one of the biggest queries (770 polygons) did not have the join field indexed like I thought it did. Adding an attribute index made a big difference, so thank you for that!