Not at all!!! 

The reason that we have never done this is because there are better ways to work through large data sets.  This is, I beleive, what Greg was reffering to in his post regarding "SQL base."  You are correct that VFP could handle 1 million records plus very easily...but SQL can go MUCH bigger.  You are used to working in a "connected" data environment, which was FoxPro and the reason for all of the corrupted memo fields   With SQL Server and business objects, you are working in a disconnected environment so you want to work with as little data on the client side as possible.

When we need to work with very large data sets, for example prescription contraindications which can have a a million+ plus records to deal with.  We create an SQL Project and deploy it to SQL Server and then execute it through a stored procedure.  The DDT will automatically create the stored procedures required in order to execute it for you.  What is nice about this all of the work is done on the server, like a T-SQL stored procedure, but you are talking ADO.NET or whatever you you need.  You can then send your ADO.NET results back to the BO.  This is the best approach when dealing with large data sets....or create a T-SQL stored procedure, but this is not nearly as fun

and yes, that is the column I set to be the new PK



Me.RentitemsBO1.CurrentDataTable.PrimaryKey = New DataColumn() {Me.RentitemsBO1.CurrentDataTable.Columns("rent_RPMrecnum")}

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

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

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

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

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).

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

Keith Chisarik

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

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. 

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



Trent: /points and laughs

Ben:




Keith Chisarik

Sigma Data Systems