POST
|
That's exactly what I thought would be the conclusion, Martin. Thanks for answering my post. I'd like a link to the incident. Also - when creating a materialized view (or in SQL Server - a table since materialized views are not support in SQL Server), how do you generate and calculate the spatial indexes. I could look it up myself, but if you have the information by hand and would like to share it, it'd a little easier for me
... View more
03-26-2015
04:10 AM
|
0
|
1
|
1738
|
POST
|
Well - I did take a look at the use of indexes wondering whether spatial indexes were used optimally. Apparently though, as data is versioned, SQL Server insists on using indexes optimizing the deduction of data from base tables, add tables and delete tables. I tried forcing it to use the spatial indexes (WITH(INDEX(S64_idx))) but was told that other hints were preferred. Omitting the Order By part did not improve performance, though that was actually a plausible assumption. I then tried leaving out the string conversion (str(eft.Year)) completely - also this had no effect on performance. So - the question likely is: how can I force the query to make use of the spatial indexes, when SQL Server seems to prefer using the indexes used in the versioned views optimizing aggregating information from the base tables and the corresponding add and delete tables? I'll try looking at the query that ArcMap generates using the Profiler tool. I am though kind of worries that this simply will reveal extremely complex queries that I am unable to convince (or even teach) any business intelligence department.
... View more
03-24-2015
01:37 PM
|
0
|
0
|
1738
|
POST
|
A customer of ours (an electric utility company) wants to increase the use of the grid data registered in ArcGIS. Recently their BI department has asked us to help in establishing various reports / queries from outside ArcGIS. An example of such query is to list substations per maintenance area / sub area. This query delivers the requested list: Select Byg.Navn, eft.Betegnelse, str(eft.Year) From ELDB.EL.BYGVAERK_EVW byg, ELDB.EL.Eftersynsomraader_evw eft Where Byg.BygvaerkArtKode IN (1,2) AND Byg.SHAPE.STIntersects(eft.SHAPE) = 1 Order by eft.Betegnelse, str(eft.Year) (BYGVAERK_EVW is the versioned view of substations and Eftersynsomraader_evw is the versioned view of maintenance areas). Executing this query in MS SQL Server Management Studio takes 3 minutes. If in ArcMap I do a spatial join between the two feature classes I am able to present the result in approximately 1½ minutes. If ArcMap is able to deliver the requested answer in 1½ minutes I don't see why asking for the same trough non-ArcGIS queries shouldn't be able to also deliver the answer in 1½ minutes - or even faster. I have a feeling that the answer to optimizing my query lays in the use of spatial indexes. But how should I approach implementing this? Any help would be appreciated.
... View more
03-10-2015
02:49 AM
|
1
|
5
|
7430
|
Title | Kudos | Posted |
---|---|---|
1 | 03-10-2015 02:49 AM |
Online Status |
Offline
|
Date Last Visited |
11-11-2020
02:24 AM
|