Performance optimization

A key aspect during the development of the GISconnector for Excel was the speed with which data, selections and filters can be exchanged between ArcGIS and Excel. At this point we would like to point out the possibilities for how the performance of the GISconnector for Excel can be optimized.

The GISconnector for Excel was designed to be able to accommodate the processing of very large volumes of data in a short time interval. By acting on the following tips, data transfers can be significantly accelerated:

General instructions

Export data from ArcGIS to Excel

If you do not need all attribute data of your ArcGIS dataset in Excel, it is possible to transfer only a part of your features to Excel when initially exporting. See here to do so.

Transfer of specific rows

The smaller the amount of data transferred between Excel and ArcGIS, the quicker the transfer process will be. Therefore it is best only to transfer those data which actually must be transferred. Use the functions "Transfer filtered data"

, for both filtered and blanked out rows, and "Transfer selected data"

, in order to limit the range of the data to be transferred and therefore reduce the time needed to transfer (can be found as the submenu in Excel menu “Transfer Data”

), or directly in the ArcGIS toolbar.

Transfer of specific columns

Limit not only the amount of rows to be transferred, but also the amount of columns. To achieve this, columns that should not be transferred (if, for example, they have not been altered) can be excluded from the transfer (refer to dialogue Columns/fields in the menu "Edit connection"

). All columns not ticked in their respective check box are not managed by GISconnector for Excel and not taken into consideration for transfer.

Performance optimization in ArcGIS

To increase the speed of the GISconnector for Excel, the most important measure to take in ArcGIS is to avoid blocks or schema blocks. The existence of blocks reduces the speed with which the GISconnector can access data, selections and filters. By observing the following instructions, you can achieve maximum performance from the GISconnector.

Close the attribute table!

As soon as the attribute table of a layer or a stand-alone table is shown, ArcGIS produces a block that reduces the access speed. The attribute table must not be in an editing session to slow down the GISconnector for Excel; a visible/open attribute table already slows down the GISconnector for Excel.

Finish your editing session before transferring large amounts of data!

The GISconnector for Excel requires no editing session for the exchange of data. Data are transferred significantly faster between Excel and ArcGIS when no editing session is involved. If you transfer data during an editing session, the best way to reduce the amount of data to be transferred is by using the transfer option "Transfer selected data"

. To do this you must select in advance the data to be transferred, otherwise no data will be transferred. If you would like to copy a large amount of features from one feature-class to the feature class with connection, we recommend to proceed as follows: Paste the new features in the feature-class with connection, stop the editing session and afterwards transfer the new features to your Excel table by limiting the scope of the transfer to the new features only.

Close the data preview!

Avoid having feature classes or tables used by the GISconnector for Excel currently displayed in a preview (e.g., "Preview" tab in ArcCatalogue or in the "Preview" tab of the element description of the catalogue window).

Ensure that you have exclusive data access to your feature-classes or tables and your Excel workbooks.

In general, feature-classes or tables and Excel workbooks should not be simultaneously left open in other ArcGIS projects (MXD), in other applications (ArcCatalog) or in other Excel-instances on other computers in a network.

Performance optimization in Excel

Excel internally combines consecutive cells with the same values or with rising/falling values into areas respectively arrays. If, for example, each cell A1: A10 contains the word "tree", Excel internally manages these cells as a one area or array with the same values. Or if, for example, the cells A1 – A10 contain the consecutive values of 100, 101, 102, 103, 104, 105, 106, 107, 108 and 109, then Excel does not internally manage each cell separately but knows that the values from A1 – A10 are one higher than the previous cell each time, and hence again represent one area or array.

If you wish to transfer selections and filters to ArcGIS using the GISconnector for Excel, these internal areas of Excel play an important role. The more such areas or arrays exist in Excel, the faster the GISconnector for Excel will operate.

The transfer speed hence does not primarily depend on the number of selected (or filtered) rows, but on the number of areas or arrays that must be transferred to ArcGIS. The slowest kind of processing occurs when every second cell is selected or filtered. The fastest processing occurs when a selection or a filter consists of consecutive rows respectively of a single array. This internal mode of operation of Excel can consequently be exploited for increasing the processing speed:

Sort your data before transferring selections or filters from Excel to ArcGIS.

To enable Excel to work with the smallest possible number of areas or arrays, data to be selected or filtered should be appropriately sorted in advance so that the subsequent selection or filter results in the minimum possible number of areas or arrays (in the optimum case, just one).