I have some code that loops through about 1/4 of a million records, it is taking a very long time (I stopped it after 15 minutes and it wasn't close to done).



I took out all the processing in the loop so that all it was doing was the loop with a single seek() and it was still slow. I replaced the seek() with "if 1 =2 then" and it went as expected (about 20 seconds).



I am seek()ing against a single integer field that I have an index on in SQL. Help????



Code:

searchstr = "rent_RPMrecnum = " & "'" & currow("record_number") & "'"

'If Me.RentitemsBO1.Seek(searchstr) Then

‘do nothing

Else

‘still do nothing for testing purposes

End if





Thanks.










Keith Chisarik

It doesn't matter that you have an index in SQL Server... it's irrelavent once you have the data in a data table.  What you want to do is add an index to your DataTable.  But since you cannot directly add an index, you'll have to change the column that it considers to be the "primary key".  I've never done this before, but the only index that ADO.NET keeps on a DataTable is the one on the primary key column.  That should certainly help speed things up.

table.PrimaryKey = new DataColumn[] {table.Columns["columnname"]};

I'm not entirely sure if you can do this one data is already in the table... if you can't you'll need to create a new table, set the PK, and then copy the data over.