Routing Algorithm with Spatial SQL

Question

currently, I search for a solution to speed up my code. I have a DB with different geometrical components (Points, Polygons). Each Structure is given by several properties called Con_Number (default: 0), LayerName, individual Index (ObjectIndex). Thereby the Con_Number of each instance should be the same value for ...

• Structures which are overlapping on the same layer.
• Structures which are overlapping on neighbouring layers.

I used the STIntersects command to get overlapping instances.

sqlString := 'declare @g geometry select @g = Geom from obj where ObjectIndex = :index select ObjectIndex, Con_Number from obj WITH(index(idx_Spatial)) where Geom.STIntersects(@g) = 1 and ObjectLayerName in (' + QuotedStrList(neighbourLayers) + ')';

I tried to perform this algorithm in a parallel loop going through all elements of a certain layer. Afterwards, I run the SqlCommand for each instance on a separate Connection/Query. The result will be handled differently depending on the number of different con_numbers. Last but not least, the Con_number of overlapping structures will be updated.

1. Step: Find all overlapping elements!

2. Step: Update Con_Number for overlapping elements!

   Parallel.ForEach<Integer>(indexList)
         .NumTasks(ThreadCount).Execute(
         procedure(const item: Integer)
   var
       Con_Number: Integer; 
       Con_List: TIntegerList;
   begin
   //Create TADOConnection and TADOQueries
   
       // 1.STEP: get signals of all overlapping elements
       ADOQueryOverlap.SQL.Text := sqlString;
       ADOQueryOverlap.ParamCheck := True;
       ADOQueryOverlap.Parameters.ParamByName('index').DataType := ftInteger;
       ADOQueryOverlap.Parameters.ParamByName('index').Value := item;
       ADOQueryOverlap.Open;
       ADOQueryOverlap.First;
   
       while not ADOQueryOverlap.Eof do
       begin
         indexThreadList.add(ADOQueryOverlap.FieldByName('ObjectIndex')
           .AsInteger);
         Con_Number:= ADOQueryOverlap.FieldByName('Con_Number').AsInteger;
         if (Con_List.IndexOf(Con_Number) < 0) and (Con_Number > 0) then
         begin
           Con_List.add(Con_Number);
         end;
         ADOQueryOverlap.next;
       end;
   
   //2. STEP UPDATE CON_NUMBER
   
   if indexThreadList.Count > 0 then
   begin
     // elements have no Con_Number -> Assign new Con_Number to overlapping elements
     if signalList.Count = 0 then
     begin
       InterlockedIncrement(FId);
       ADOQueryWriteBack.SQL.Text := 'update obj set Con_Number = ' +
         FId.ToString + ' where ObjectIndex in (' +
         indexThreadList.AsString + ')';
       ADOQueryWriteBack.ExecSQL;
     end;
     // one Con_Number exist -> write that number to all other objects
     if signalList.Count = 1 then
     begin
       ADOQueryWriteBack.SQL.Text := 'update obj set Con_Number = ' +
         Con_List.Items[0].ToString + ' where ObjectIndex in (' +
         indexThreadList.AsString + ')';
       ADOQueryWriteBack.ExecSQL;
     end;
     // more than 1 Con_Number exists -> Take one Con_Number and overwrite other Con_Numbers 
     if signalList.Count > 1 then
     begin
       ADOQueryWriteBack.SQL.Text := 'update obj set Con_Number = ' +
         Con_List.Items[0].ToString + ' where ObjectIndex in (' +
         indexThreadList.AsString + ') or Con_Number in (' +
         Con_List.GetListAsStringFromIndex(1) + ')';
       ADOQueryWriteBack.ExecSQL;
     end;
   end;
   

Currently, I try to get a better performance. Furthermore, the threads are crashing after a while (several hours). Do you think Multithreading is suitable for my application? Do you have other improvement ideas?