I have to process a large list of numbers in an Excel spreadsheet and then add the new numbers to the database (if they aren't already there). If a number is in the database already but isn't in the new list it must be deleted from the database.

Presently there are over 75000 numbers and its taking forever to process and I'm pretty sure my method isn't efficient. What are my alternatives?

  If NewNumbers.Count > 0 Then ' There are some numbers to process
                Dim ExistingNumbers As AtkvMemberNumberCollection = FetchExistingNumbers() ' Fetch all the existing numbers

                Dim TotalRemoved As Integer = 0

                Dim NumbersToRemoveFromOriginal As New AtkvMemberNumberCollection()

                ' Loop through each number from Excel and remove all numbers 
                ' that already exist in the database.
                For index As Integer = 0 To NewNumbers.Count - 1
                    Dim Filter As New PredicateExpression()
                    Filter.Add(AtkvMemberNumberFields.Number = NewNumbers(index).Number)
                    Dim Matches As List(Of Integer) = ExistingNumbers.FindMatches(Filter)
                    If Matches.Count > 0 Then
                        ' The number already exists in the database so we add it to the 
                        ' collection that checks if it needs to be deleted
                        NumbersToRemoveFromOriginal.Add(NewNumbers(index))
                        TotalRemoved += 1
                    End If
                Next

                ' Loop through each number in the list to be removed and remove all numbers 
                ' that already exist in the database.
                For Each number As AtkvMemberNumberEntity In NumbersToRemoveFromOriginal
                    NewNumbers.Remove(number) ' Removed so won't be re-processed
                    Dim Filter As New PredicateExpression()
                    Filter.Add(AtkvMemberNumberFields.Number = number.Number)
                    Dim Matches As List(Of Integer) = ExistingNumbers.FindMatches(Filter)
                    If Matches.Count > 0 Then
                        ' The number already exists in the database so we remove it from the 
                        ' collection that is going to be deleted
                        ExistingNumbers.RemoveAt(Matches(0)) ' This is safe because there will only ever be one match
                    End If
                Next

                _TotalAdded = NewNumbers.Count
                NewNumbers.SaveMulti() ' All number that are left in the collection are new
                _TotalRemoved = ExistingNumbers.Count
                ExistingNumbers.DeleteMulti() ' All numbers that are left weren't in the new list so are deleted.
            End If

What part exactly you want to modify? As I think what you are doing is necessary. You can use Linq2Objects but conceptually is the same:

var numbersToDelete = (from en in ExistingNumbers where NewNumbers.Contains(x -> Number) select en.Number).ToList();

Also, using that list of numbers in a predicate would be quickest than a DeleteMulti() without parameters, that work on the entities in memory, so it would generate a sql delete for each entity. So try this:

ExistingNumbers.DeleteMulti(new PredicateExpression(AtkvMemberNumberFields.Number == numbersToDelete));

I used C# but you just need to translate it to VB. For more info about that predicate FieldCompareRangePredicate.

What part exactly you want to modify? As I think what you are doing is necessary. You can use Linq2Objects but conceptually is the same:

The finding of the matching number in the collection is definitely where the problem is so I'll give this a try and see how it works.

Also, using that list of numbers in a predicate would be quickest than a DeleteMulti() without parameters, that work on the entities in memory, so it would generate a sql delete for each entity.

There is a limit of 2000 numbers in a SQL statement so I'm not sure that would always work.

trancehead wrote:

What part exactly you want to modify? As I think what you are doing is necessary. You can use Linq2Objects but conceptually is the same:

The finding of the matching number in the collection is definitely where the problem is so I'll give this a try and see how it works.

Ok

trancehead wrote:

Also, using that list of numbers in a predicate would be quickest than a DeleteMulti() without parameters, that work on the entities in memory, so it would generate a sql delete for each entity.

There is a limit of 2000 numbers in a SQL statement so I'm not sure that would always work.

You are right, but if you can split the parameter in groups of 2000, that will be faster than delete one by one, I think.