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. Consider the following hints and the data transfer can significantly be accelerated:

General instructions

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 machines in a network.

Export data

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

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 the 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 the 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 (e.g. if 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 considering 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 Standalone Table is shown, ArcGIS produces a block that reduces the access speed. The attribute table must not even be in an edit session to slow down the GISconnector for Excel; a visible/open attribute table is enough to slow down the GISconnector for Excel.

Finish your edit session before transferring large amounts of data

The GISconnector for Excel requires no edit session for the exchange of data. Data is transferred significantly faster between Excel and ArcGIS when no edit session is involved. If you transfer data during an edit 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 the data to be transferred in advance, otherwise no data will be transferred.

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).

Performance optimization in Excel

Excel combines consecutive cells with the same values or with rising/falling values into areas respectively arrays internally. If e.g. 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 e.g. the cells A1 - A10 contain the consecutive values of 100, 101, 102, 103, 104, 105, 106, 107, 108 and 109, 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 represent one area or array again.

If you wish to send 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 sent 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 sending selections or filters from Excel to ArcGIS

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