Attribute query performance with indexing on SDE dataset

[GinoMellino]

Hi,

I have a layer with 3mil+ polygon features of fairly simply geometry which I need to perform attribute queries on using a custom application I am developing. The layer sits within an SDE and I need to attempt to improve performance as currently the query can take a while to run. My question is two fold: If I index the search field on this layer is that index stored locally on the machine accessing the SDE? And further, if this is true, when the dataset gets updated (I think it is weekly) will I be notified that it needs to be re-indexed somehow or will I need to re-index at regular intervals?

Any other performance hints and tips anyone can give me will be well appreciated too. I am using VB.Net and ArcGIS 10.

Thanks in advance.

[JamesCrandall]

Hi,

I have a layer with 3mil+ polygon features of fairly simply geometry which I need to perform attribute queries on using a custom application I am developing. The layer sits within an SDE and I need to attempt to improve performance as currently the query can take a while to run. My question is two fold: If I index the search field on this layer is that index stored locally on the machine accessing the SDE? And further, if this is true, when the dataset gets updated (I think it is weekly) will I be notified that it needs to be re-indexed somehow or will I need to re-index at regular intervals?

Any other performance hints and tips anyone can give me will be well appreciated too. I am using VB.Net and ArcGIS 10.

Thanks in advance.

Edit: your index(es) should be applied to the SDE layer under the design approach defined by the SDE administrator.  Additionally, logfile management should be implemented as this could potentially be a source of your peformance issues.

Is the data versioned?

If it is just attribute querying and not on the geometry, maybe consider a parameterized Stored Procedure along with a DataGridView for displaying the result?  (you say this is part of a custom app and I suspect you have UI elements you are creating for attribute display and manipulation).  It is my understanding that the SProc's execution plan will be pre-compiled on the server and aids in peformance.  I have several apps that utilize this approach and it works very well --- parcels as geometries and SDE table as attributes with a parameterized StoredProcedure setup for queries on the SDE table.

I don't have direct answers to your other two questions, but I'd take a guess that you or your DBA will have to re-index after updates.  You might want to double-check/verify that in the SDE forum.

[GinoMellino]

Hi jamesfreddyc,

Thanks for the reply. Unfortunately I have no control over anything on the design side of things. I was hoping that the attrib index was stored locally on the machine used to create it so that I could add a little code to create the index periodically on the end users machines. Forgive me (I am still a novice at a lot of this stuff), I have never had experience with stored procedures...is this something I can set up on the client side?

My application uses an attribute search to select features and then creates a selection layer from these. It then zooms to the selection layer and uses this as an area of interest to spit out some automated maps. In this I don't need to display the attributes as such, I do have some routines that grab a few bits and pieces out of the selection layer for legend details but that's about it.

Thanks again!

[JamesCrandall]

Hi jamesfreddyc,

Thanks for the reply. Unfortunately I have no control over anything on the design side of things. I was hoping that the attrib index was stored locally on the machine used to create it so that I could add a little code to create the index periodically on the end users machines. Forgive me (I am still a novice at a lot of this stuff), I have never had experience with stored procedures...is this something I can set up on the client side?

My application uses an attribute search to select features and then creates a selection layer from these. It then zooms to the selection layer and uses this as an area of interest to spit out some automated maps. In this I don't need to display the attributes as such, I do have some routines that grab a few bits and pieces out of the selection layer for legend details but that's about it.

Thanks again!

I see.  My suggestion wouldn't really offer any benefit since your requirement is to apply a selection on the layer and zoom functionality.

Since you are dealing with queries on a SDE layer, I would think that the index would have to be applied on the database that the layer is stored in.  You can verify that in the SDE forums or hopefully someone here can confirm that, but that just seems logical to me.  In thinking about this, I've never had to really worry about indexes because the SDE administrator I work with is very good and has tuned the database to required spec and as a result the development work I do goes smoothly.

Can you inquire with your DBA/SDE admin person or group about this?  See if an index is even applied to the field you are querying on AND if it is a good idea to have one (it *may* be a case where too many indexes hurt performance!).  Again, you should check with the folks who are maintaining the db/sde environment.  Or perhaps someone here can confirm that your idea of a local index is valid or not.