X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Views And Indexes, Any Versions    
post Aug 13 2019, 10:13 PM

Posts: 303
Joined: 4-April 12
From: Bendigo, Australia

I have a problem in using SQL Server 2014 as an input to ArcGIS and am trying to figure out what is happening and how to work around it.

The background is that I have a SqlServer table (Bores) that includes a geography field of point data (PK is BoreID) and a table (WaterLevels) that includes time series data for those points (BoreID is a FK in this table; WaterLevelID is the PK). I have created a view (vewWaterLevels) that relates these tables into a flat structure for use in the GIS system. WaterLevelID is a unique field in the view. In the GIS I want to do a spatial selection of the data in the view so I can get the results within a particular area, defined by a polygon. I can plot the Bores table and vewWaterLevels in the GiS just fine and can select the points in the Bores table that are contained in my polygon. However, when I do a similar select to get records in the view, it hangs the GIS, or at least chugs away until I give up. I have selected records from the view to include only a subset of bores and can then select the water level results for the bores within my polygon if I have about 10 bores (about 100 records) but if I expand it to 100 bores (about 1000 records) the thing crashes. So I know the process 'works' but something is going on that falls over with even modest amounts of records.

I suspect that the GIS is smart enough to use the index on the table but doesn't in the view and gets its knickers in a twist.

Am I correct that a query against a view will use the indexes of the underlying tables where possible? So that it is smart enough to use the WaterLevelID index when doing a non-spatial query within SQL Server? Does the same hold true for spatial queries? Is there a way to tell SQL Server that the WaterLevelID field is effectively a key field? I can see places where I might not have a single-field key in a view.

If I import my polygons into a SQL Server table with a geography field, could I make the query more efficient? That would be a possible fix, but inconvenient because I would have to store different polygons that come from multiple sources. Currently I just attach to those sources with a database connection in ArcGis and would rather not have to manage them myself.

-- Evan
Go to the top of the page
post Aug 14 2019, 07:51 AM

UA Admin
Posts: 36,800
Joined: 20-June 02
From: Newcastle, WA

Have you considered creating Indexed Views?

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Aug 14 2019, 03:07 PM

UtterAccess VIP
Posts: 2,955
Joined: 12-April 07
From: Edmonton, Alberta Canada

Yes, the underlying indexes are always used. This is no different than submitting say on the fly SQL to the query processor.

You can consider placing indexes on the view but I would only do so if you can create some kind of “key” or type of index that would speed things up.

In other words, any basic index on a column in the base table(s) are ALWAYS used by the query processor. So, if you need say an index on customer name, or invoice etc., then such indexes are better placed in the “base” table. They will in all cases if possible to be used will thus be used.

You could however say perhaps create an index say on city + invoice number if such a view were hitting a large table, and you only needed say one city. However, even in this case, such an index on the base table would be used by the view anyway.

As noted, index(s) on views often only make sense if you need some sub-set of data that you can index by some “group” or index that would not really work on the main table.

So, for “most” basic indexing, then any store procedure, pass-through query, or a view submitted to the query processor, the underlying base table indexes are used when possible.

One of the better use cases for indexing a view that involves multiple tables and joins, and also some aggregations. These types of queries can benefit from re-using all of the work the query engine spent pulling together such views. However, such indexes can cost significant amounts of time when the base tables are updated.

To be honest, I don’t have experience with indexed views, but my basic point is indexing on the base tables is always used when possible.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post Aug 15 2019, 07:59 PM

Posts: 303
Joined: 4-April 12
From: Bendigo, Australia

Thank you for the information on indexed views. I had been thinking that a materialized view might help and indexed views are materialized. Weirdly, it doesn't seem to have helped on the ArcMap end.

I think it must be something weird going on in the GIS application. I tried a lot of variations, including building the query on the fly, rather than using a view. Adding a spatial index to the Bore table didn't help (Can't create a spatial index on the view). I'll see if anyone has any ideas on their support BB.

-- Evan
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    28th February 2020 - 04:13 AM