Latest Contributions by VinceAngelo

Oh, dear.  It's not very fair to assign this task to a non-DBA.  Database transition is the time when inexperience can introduce inefficiencies which are much harder to eliminate down the line.   ORDER BY is the database's "sort on" directive. It's a row thing not a column thing (though reorganizing columns that have been added in dribs and drabs is a good example of a low-cost update during transition). Spatial fragmentation occurs when data is added to a table in an unordered fashion, such that, when a basic draw request at full extent is made, the features seem to draw in random order. Spatial defragmentation is the process of organizing row order such that draw requests result in a systematic fill, such that, when the extent is at a much more restrictive scale, the features that are drawn are from at most several nearby clusters of rows (reading fewer rows to meet a query request means a faster draw time, and less space consumed in the cache for any one query, which means other queries can also be faster).  In extreme cases, spatial defragmentation of, say, 10 million lighting strike records, can result in simple query times dropping from minutes to sub-second. In regular use, it might mean the difference between 700-800 and 200-300 milliseconds during a JOIN -- still significant, but you're much less likely to get rose petals scattered before you and cheering crowds forming spontaneously on your way back to your car. - V ... View more

Keeping a database backup isn't going to be of much use if you won't have a SQL Server instance into which to restore it (sort of like making floppy backups before taking delivery of a new ultra-thin laptop). I usually have the SQL used to create my tables available, so I've been able to create a new empty template in the PG database, then use SQL to optimize the table organization for spatial index order, or at least somewhat spatially defragmented (e.g., counties left to right within states in UTM Zone order), before re-registering with the geodatabase. If you're going to have to migrate, you might as well get some optimization out of the deal. Sometimes you can create views for the data export to add an optimal ORDER BY when loading the FGDB out of the database, other times it easier to FeatureClassToFeatureClass into a staging schema, then use INSERT INTO finalschema.table1( {fieldlist}) SELECT {fieldlist} FROM tempschema.table1 ORDER BY sort_seq_added_earlier Integrating as many other moderately costly updates into your design as the migration effort can support will manifest in reduced runtime in the new instance.  Conversely, not taking the time to optimize can hobble the performance of the new environment indefinitely. - V ... View more

Good call.  The intertubes says the "SQLSTATE=42601" error is associated with IBM DB2. - V ... View more

Getting folks to download a Microsoft file format with security challenges to review your data could result in low participation. Providing an ASCII table in a code block would get more viewers. Note that Esri and Microsoft (and the other RDBMS vendors) have different definitions of valid geometry (at times), so using Esri tools like Check Geometry might be required to confirm data integrity. I transfer tens to hundreds of millions of records between FGDB and PostgreSQL quite frequently without data loss, so I wonder if there's something specific about the data that you are using.  Have you tried to identify common properties in the dropped data? (One way would be to pass the preserved unique key values back into the database, then create a LEFT OUTER JOIN where the returned keys have a NULL value.) -- V    ... View more

There are no spatial operators in the basic SQL supported on file geodatabase, so no, that is not an option. It would be an option in a true database source. - V ... View more

No, Esri libraries are not going to update the database tables when they aren't used. You'd need to write your SQL trigger to also update the last_editor and last_update. Getting triggers to play nice with versioning seems an opportunity for "bleeding edge" experimentation. - V ... View more

>  I've never seen the updating of an attribute field result in the changing of the geometry of a feature. But you didn't change an attribute field, you changed the geometry.  There's no way for SQL to rewrite the CAD BLOB, so the CAD data is lost. - V ... View more

I connect to PostgreSQL RDS databases all the time, however, I *NEVER* connect as the postgres login. I suggest you create a data ownership login and try connecting as that.  You should make sure that you have enabled the postgis extension in the database, and that the database name is all lower-case (which would give a different error). - V ... View more

It's been ages since I last used 9.3.1, which has been out of support over seven years now (it's so different from modern releases it might as well be a different product). There isn't nearly enough information here to begin to speculate about what could be the problem. Among the details missing: RDBMS in use (exact version) Definitions of the tables involved in the query The exact SQL to create the view The exact parameters (except password) used in the sdetable commands An indication of the cardinality of the joins Example output An indication of the uniqueness of the registered rowid column Working with ancient software is a considerable risk to your organization. I recommend you migrate your data to a modern software stack (supported OS, DBMS, and application stack). - V ... View more

Low hanging fruit on file  geodatabase query performance includes: Making sure the dataset is on a local device (not a network-mounted drive) Periodically rebuilding the spatial index Building attribute indexes to cover query terms Spatial queries on badly fragmented datasets can also take a long time, even with a fresh spatial index.  I use relational databases, not file geodatabase, to juggle tens to hundreds of millions of rows, and I always make sure my data is physically organized  from left to right and top to bottom in chunks (e.g., countries by UTM Zone and MGRS tile; counties by state, from west to east). The  same physical ordering options are available for file geodatabase as enterprise, but they're rarely as necessary in the smaller table sizes (under 100-200k rows). Tech Support can be of use with modern software, but when using something ancient you'd need to test against something modern to see if it's already been addressed. - V ... View more

ArcGIS 10.1 was retired from support over three years ago.  The current release is 10.8.1. Your issue may be release independent, but there's really no way to tell, since you haven't mentioned data format, indexing, data volume, or actual timing (in seconds). Please help us to help you by including enough details to make an answer possible. - V ... View more

The REST endpoint provides all the data. Any client which can emulate the client app's request protocol can get that data. You could harden the endpoint in front of the Web Adaptor's servlet engine, but it might not take a lot to engineer around that (especially if a concerted effort was made), and there might be a significant performance cost. If you want to protect the back-end database, you might stage a publishing database (or FGDB folder), which is effectively read-only, in the DMZ to prevent the risk of tampering. I'm afraid this might be a "Doctor, doctor, it hurts when I do this!"/"Don't do that." situation - V ... View more

Are you editing the hosted data or the source of the Enterprise feature class used to create the hosted data? You can publish a map/feature service which accesses the Enterprise geodatabase feature class directly, but the "hosted" part of the name generally indicates that the federated DataStore is now managing the data, not the original source geodatabase (at least, with respect to the hosted service), which would correlate to the behavior you are seeing. - V   ... View more