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

I am seeking against a BO, that already has a primary key field defined. I'm confused on your fix Ben

Keith Chisarik

Ah, the PrimaryKey property of a DataTable isn't really used by us... it's just used by ADO.NET to allow the Find() method to locate records based upon the internal index.  That index is the only index that exists on a DataTable in ADO.NET, so if you want to speed up the seeking to data on a column, you'll need to somehow move your DataTable's index to that column.  Hence, you redefine the PK, which moves the index to that column.

I'm not talking about changing the PK in the database... just the DataTable.PrimaryKey property on the BO.CurrentDataTable itself (just your local copy).

Oh, and my sample was in C#, sorry, here's the VB:

BO.CurrentDataTable.PrimaryKey = New DataColumn() {BO.CurrentDataTable.Columns("columnname")}