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'd tend to not use .NET for this or any procedural language. I'd try to figure out how to use set base SQL to get the job done. Much faster.

"Utt oh", Trent called Ben. I must have done something really dumb



Trent: /points and laughs

Ben:




Keith Chisarik

Sigma Data Systems

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. 

Greg,



Thanks but "set base SQL" doesn't mean much to me. I used to do this with millions of records in VFP and it didn't blink, I must have something wrong... and seek() and the "wait window" were selling point's for me so I want to know how to make it work like it should. If that falls through i will look up "set base SQL", 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")}

Cool that took it from unusable to just slow (good enough to get me past the jump), thank you.



So if I want it faster (VFP fast) I should try to write a stored proc in SQL or something like Greg suggested?



(it has been about 2 minutes and it is 1/2 done)



Thankfully this procedure only has to be done once a day.




Keith Chisarik

Actually I get an error when calling add() on the BO after adding your code. If I remove your code, it runs fine again, but back to a crawl.



The column is set to not allow nulls, and has a default value of 0 defined.





System.Data.NoNullAllowedException was unhandled by user code

Message="Column 'rent_RPMrecnum' does not allow nulls."

Source="System.Data"

StackTrace:

at System.Data.DataColumn.CheckNullable(DataRow row)

at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)

at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException)

at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos, Boolean fireEvent)

at System.Data.DataRowCollection.Add(DataRow row)

at MicroFour.StrataFrame.Business.BusinessLayer.NewRow()

at MicroFour.StrataFrame.Business.BusinessLayer.Add(Boolean CheckSecurity)

at MicroFour.StrataFrame.Business.BusinessLayer.Add()

at ladesktop.frmSyncTables.syncrentitems(String p_sqlstring) in V:\2005 Projects\LightActionRPMScan\ladesktop\frmSyncTables.vb:line 564

at ladesktop.frmSyncTables.bgwRentitemsSync_DoWork(Object sender, DoWorkEventArgs e) in V:\2005 Projects\LightActionRPMScan\ladesktop\frmSyncTables.vb:line 103

at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)

at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)


Keith Chisarik