Adding Data to GDB extrem slow

[FrankGlandorf1]

Hi,

I am not quiete sure if I am right in this discussion group.

I have written an Add-On for ArcCatalog (Button) which allows me to add data into a filegeodatabase.

The import of the data into the tool takes about 5 second. The import into the filegeodatabase takes about 5 hours. Maybe someone can have a look at the sourcecode and tells me what to do to get the imort faster.

Option Explicit On

Imports ESRI.ArcGIS.Catalog
Imports ESRI.ArcGIS.CatalogUI
Imports ESRI.ArcGIS.Display
Imports ESRI.ArcGIS.DataSourcesGDB
Imports ESRI.ArcGIS.Framework
Imports ESRI.ArcGIS.Geodatabase
Imports ESRI.ArcGIS.Geometry
Imports System.Xml
Imports System

' Structure für eine Haltung
Structure Haltung
    Public bezeichnung As String
    Public projekt As String
    Public gebiet As String
    Public baujahr As String
    Public lage As String
    Public entwaesserung As String
    Public baustatus As String
    Public bodenart As String
    Public strasse As String
    Public anfangsschacht As String
    Public endschacht As String
    Public koordinaten As ESRI.ArcGIS.Geometry.Polyline
    Public historischerName As String
    Public anfangshoehe As Double
    Public endhoehe As Double
    Public material As String
    Public durchmesser As String
    Public laenge As Double
    Public bemerkungen As String
    Public zustand As String
    Public letzteBefahrung As String
    Public videoBefahrung As String
    Public pdfBefahrung As String
End Structure

' ... some more code

Public Class kanalXMLImport
    Inherits ESRI.ArcGIS.Desktop.AddIns.Button

' ... some more code

Protected Overrides Sub OnClick()

' ... some more code

        Try
            ' Herstellen einer Verbindung zur Geodatabase
            Dim pWorkspaceFactory As IWorkspaceFactory = New ESRI.ArcGIS.DataSourcesGDB.FileGDBWorkspaceFactory
            Dim pWorkspace As IWorkspace = pWorkspaceFactory.OpenFromFile(gdbDialogErg.FullName, 0)
            
            ' Einlesen des Dataset 'Kanalkataster'
            Dim featureWorkspace As IFeatureWorkspace = TryCast(pWorkspace, IFeatureWorkspace)
            Dim pDataSet As IDataset = featureWorkspace.OpenFeatureDataset("Kanalkataster")

            ' Durchlaufen aller Unterpunkte
            Dim pEnumDatasubsets As IEnumDataset = pDataSet.Subsets
            Dim pDatasubset As IDataset = pEnumDatasubsets.Next()

            While pDatasubset IsNot Nothing
Select Case pDatasubset.Name
' ... some more code
                    Case "SW_Haltungen"
                        MsgBox("SW-Haltungen")
                        If Not importHaltungen(pDatasubset, SW_Haltungen) Then
                            'Beenden der Methode
                            Exit Sub
                        End If
' ... some more code
                ' Nächster Datensatz
                pDatasubset = pEnumDatasubsets.Next()
            End While

            'Alle Daten erfolgreich eingelesen
            MsgBox("Alle Daten erfolgreich importiert", MsgBoxStyle.Information, "Importieren erfolgreich")

            ' Abfangen eines evtl. auftretenden Fehlers
        Catch ex As Exception
            MsgBox("Fehler beim Importieren " & ex.Message)
            Exit Sub
        End Try

        My.ArcCatalog.Application.CurrentTool = Nothing

    End Sub

    ' Funktion zum Importieren der Haltungsdaten in die GeoDatabase
    ' Liefert true/false je nachdem ob erfolgreich
    Private Function importHaltungen(ByRef pDataset As IDataset, ByRef haltungen As List(Of Haltung)) As Boolean

        Dim pTable As ITable = Nothing

        Try
            ' Casten in ein iTable
            pTable = TryCast(pDataset, ITable)
            ' Löschen aller vorhandener Einträge
            pTable.DeleteSearchedRows(Nothing)

            ' Für alle zu importierenden Haltungen
            For Each haltung As Haltung In haltungen

                ' Anlegen einer neuen Zeile
                Dim ds As IRow = pTable.CreateRow()

                ' Für alle Spalten in der Tabelle
                For i As Integer = 0 To ds.Fields().FieldCount - 1
                    ' Je nach Spalte werden die Eigenschaften gesetzt
                    Select Case ds.Fields().Field(i).Name.ToLower
                        Case "shape"
                            ds.Value(i) = haltung.koordinaten
                        Case "bezeichnung"
                            ds.Value(i) = haltung.bezeichnung
                        Case "historische_bezeichnung"
                            ds.Value(i) = haltung.historischerName
                        Case "anfangsschacht"
                            ds.Value(i) = haltung.anfangsschacht
                        Case "endschacht"
                            ds.Value(i) = haltung.endschacht
                        Case "durchmesser"
                            ds.Value(i) = haltung.durchmesser
                        Case "material"
                            ds.Value(i) = haltung.material
                        Case "baujahr"
                            ds.Value(i) = haltung.baujahr
                        Case "lage"
                            ds.Value(i) = haltung.lage
                        Case "strasse"
                            ds.Value(i) = haltung.strasse
                        Case "gebiet"
                            ds.Value(i) = haltung.gebiet
                        Case "status"
                            ds.Value(i) = haltung.baustatus
                        Case "bemerkungen"
                            ds.Value(i) = haltung.bemerkungen
                        Case "zustand"
                            ds.Value(i) = haltung.zustand
                        Case "letztebefahrung"
                            If haltung.letzteBefahrung <> "" Then
                                Dim ij As Long
                            End If
                            ds.Value(i) = haltung.letzteBefahrung
                        Case "videobefahrung"
                            ds.Value(i) = haltung.videoBefahrung
                        Case "pdfbefahrung"
                            ds.Value(i) = haltung.pdfBefahrung
                    End Select
                Next

                ' Speichern des Datensatzes
                ds.Store()

            Next

            ' Alles erfolgreich -> Rückgabe true
            Return True

            ' Abfangen eines evtl. Fehlers
        Catch ex As Exception
            MsgBox("Fehler beim Importieren der Haltungen" & ControlChars.CrLf & "Lfd.-Haltung: " & pTable.RowCount(Nothing) + 1 & ControlChars.CrLf & ex.Message, MsgBoxStyle.Critical, "Fehler beim Importieren der Haltungen")
            ' Beenden der Funktion
            Return False
        End Try
    End Function

' ... some more code
End Class‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

My importHaltungen() has to add abaout 1600 objects and it tooks about 30 minutes to do so.

My only idea is to put the gdb to the local-drive insteadt of a network-drive

[KarlHuber · Accepted solution]

You should consider using an InsertCursor together with a recycling RowBuffer to implement an insert that performs well. In your case you do a IRow.Store together with a dedicated ITable.CreateRow on every single item. This seems to be a bad idea...

Use:

ITable table = ...
IRowBuffer rowBuf = table.CreateRowBuffer();
ICursor insertCursor = table.Insert(true);
foreach (var entry in entries)
{
    IFields fields = rowBuffer.Fields;
    int fieldIndex = .... // Resolve your field index from column name
    object o = ... // Resolve your value from field index
    rowBuffer.SetValue(fieldIndex, o);
    insertCursor.InsertRow(rowBuf);
}
insertCursor.Flush();

‍‍‍‍‍‍‍‍‍‍‍‍‍‍

HTH + Cheers

Karl

[FrankGlandorf]

Many thanks for your help. The time for running the code is nearly by 5 seconds instead of half an hour. Unbelievable but both ways works.

But I have to mention one thing. The IRowBuffer doesn't have a function "setValue()". It has to be rowBuffer.value(fieldIndex) = o

[DuncanHornby]

Frank you should tick Karl's answer as correct!