How can I interact with a Function Statement in SQL Server in ArcGIS?

1618
14
08-21-2017 04:58 PM
JacquelinePursell
Occasional Contributor

I previously have only used Views to do complex queries but recently one of our database guys made me a couple of functions instead because he is pulling data from multiple databases and doing some complex things.  These are not showing up in the table list like a view does so I can't plot the XY manually in ArcMap.  To remedy this, he made a View out of the Function but in SQL Server Management Studio it takes 12 seconds to select the top 1000 records and in ArcGIS it never loads.

Is there a way to interact with SQL Function Statements in ArcGIS?

0 Kudos
14 Replies
JoshuaBixby
MVP Esteemed Contributor

How do you expect to interact with the functions any differently than the view that has been created?  Unless the view is a complete hack job, the functions have some issues if they are taking 12 seconds for the top 1000 records.  I would focus more time on understanding why the SQL view, or really the functions, are performing so poorly and less about directly interacting with the functions from ArcGIS.

0 Kudos
JacquelinePursell
Occasional Contributor

I am not asking why it takes forever, I'm asking if there is a better way to interact with a function in ArcGIS because I am not finding it.  The function itself takes only a second or 2 to load.  The issue is making a view out of the function in order for me to interact with it in GIS.  Because the view is made from a function, the view is essentially running the query twice, hence the 12 seconds.  I was able to create a feature class out of the view in ArcCatalog but I cannot view the records until after the feature class is created.

Views show up in the table list when I connect in ArcGIS but Functions do not.  I want to know how to use a function in ArcGIS without shoving it through a view and making it extremely slow.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Assuming it was possible, which I am not saying it is, how would you interact with the function once it is accessible from within ArcGIS?  Ostensibly, the functions have parameters, how would you expect to pass the parameters into the function?  What is the output of the function?

JacquelinePursell
Occasional Contributor

The function is acting the same as a view which is a permanent query that runs every time the table is viewed or loaded.

I gather data from many non spatial databases in Oracle and SQL Server and plot them and truncate and append my SDE on a regular basis.  Making a query such as this function or a view makes it possible for me to do this quickly and easily.

0 Kudos
George_Thompson
Esri Frequent Contributor

I would also add that if the ArcGIS client showed any "function" in the database it would cause a ton of clutter in the connection properties. It would show all the functions that are added as part of the enterprise geodatabase.

The best way to interact with a function is using SQL Server Management Studio or other management platform.

I would venture to say that it is not possible with the ArcGIS client at this time. I have not seen anyone access them. I also know that passing SQL functions/binding information via query layers have been know to fail in the past.

--- George T.
0 Kudos
JacquelinePursell
Occasional Contributor

Just to be clear, that I am not referring to a SQL Function as in using a Count, Max, or Square Root.  I am referring to a script of sorts called a Function that can perform a query on multiple tables and databases to produce a table of joined results just like any spatial or non spatial view or query layer.

I was unable to even call the function in a query layer so that was not possible.  

Casting the function as a View in the database did allow me to blindly make query layers and feature classes out of them, but I was not able to load the view 'as is' because the load time was so long that the table just remained empty in ArcGIS, hence why I had to do it blind.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

If you are running ArcGIS 10.5.x, have you tried registering the view with the geodatabase to see if that addresses the long load times?

Register a table or view with the geodatabase—ArcGIS Help | ArcGIS Desktop 

0 Kudos
JacquelinePursell
Occasional Contributor

I tried registering a view in my SDE I made (that was not a function) and it was not an option available to me.  Registering these views I am referring to in this post is definitely not an option because they are in non-SDE databases that I do not own or manage.

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

I can suggest a workaround, but you will need to decide whether using it is feasible for you or not.

You mentioned that your Database guy has made you a View based on that function. Now that View is slow to load, but does load in SQL Server Management Studio eventually. The workaround would be to create a separate Table out of this View. This Table would be static and thus will load normally in ArcGIS or SQL Server.

Now, obviously the whole point of using a View is because the data will not be static. So, to see the new data, you will need to create a SQL Server job, which can run at any decided intervals, truncating the Static table and then updating it again with records from the View.

The factors to consider:

1) How frequently will the data in the View change? If it hardly changes, then running the Job at the start of every day, should suffice. If it changes frequently, then you can decide upon the interval accordingly.

2) How much data are you dealing with in the View. Truncating the Table will not take much time, but updating the static table with the data from the View will take time based on the amount of data involved.

Example for reference:

Lets assume that the View that the Database guy gave you is View1, owned by GIS user. So it is dbname.gis.View1

SELECT * INTO dbname.schema.newtable_name FROM dbname.gis.View1

The above will create the new static table. Then use the below two SQL in the SQL Server Job:

Truncate table dbname.schema.newtable_name
Insert INTO dbname.schema.newtable_name SELECT * from dbname.gis.View1