StrataFrame Forum » StrataFrame Application Framework - V1 » Issues » seek() slowness

seek() slowness

Keith Chisarik — Tue, 03 Apr 2007 18:43:09 GMT

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????

[b]Code:[/b]
searchstr = "rent_RPMrecnum = " & "'" & currow("record_number") & "'"
'If Me.RentitemsBO1.Seek(searchstr) Then
‘do nothing
Else
‘still do nothing for testing purposes
End if

Thanks.

RE: seek() slowness

Greg McGuffey — Tue, 03 Apr 2007 18:43:09 GMT

I'm going to throw out another approach. Is it possible to just create a fill method that returns just the records you want? You must be determining somehow which records to seek, so why not just use that criterion in the Where clause. You could also do a sort (on SQL, not in BO). Then you just loop through the returned records and do whatever you have to do.

RE: seek() slowness

StrataFrame Team — Tue, 03 Apr 2007 10:38:20 GMT

OK, back to business...

Keith, if you change the column that is the primary key, you'll have to change it back after you are done with your loop in order to add a new record. Also, when you set the PK column back to where it actually is the primary key, you'll also need to set the auto-increment on the column... seed of -1 and value of -1.

RE: seek() slowness

Ben Hayat — Tue, 03 Apr 2007 10:06:04 GMT

[quote][b]Ivan George Borges (04/03/2007)[/b][hr][quote][b]Ben Hayat (04/03/2007)[/b][hr][quote]I did!

With FORTRAN. [Crazy] [/quote]

IBM 360/370
Univac 1108

Fortran
Cobol
Basic
Assembly
JCL
Algol
.
.
.
:P[/quote]

Hey, Ben.

I walked the same path![/quote]

We should definitely talk about it when we are having Beer "On" Trent. :D

RE: seek() slowness

Ben Hayat — Tue, 03 Apr 2007 10:04:43 GMT

[quote][b]Steve L. Taylor (04/03/2007)[/b][hr]1983 - Keith was 10, Trent was 7, Ben was 2 -- ok I am a little older than you guys -- by a freaking lot. I was 31 in 1983.

Either y'all are precocious or maybe I am mature. Actually, I started on punched cards and tape.At one time Icould read Baudot...

I think Ben and Trent are counting the days until the nursing home comes and gets me...[/quote]

Steve, it's great to see you active in design and development. And that's what keeps everyone young...Staying on top of things.

Hope to see you when I get to your class!

RE: seek() slowness

Ivan George Borges — Tue, 03 Apr 2007 09:20:36 GMT

[quote][b]Steve L. Taylor (04/03/2007)[/b][hr]1983 - Keith was 10, Trent was 7, Ben was 2 -- ok I am a little older than you guys -- by a freaking lot. I was 31 in 1983.

Either y'all are precocious or maybe I am mature. Actually, I started on punched cards and tape. At one time I could read Baudot...

I think Ben and Trent are counting the days until the nursing home comes and gets me...[/quote]

I was 19. But I'm not worried... I've decided to live 120 years, so, nothing to worry about. :hehe:

RE: seek() slowness

Keith Chisarik — Tue, 03 Apr 2007 09:10:38 GMT

[quote][b]Keith Chisarik (04/02/2007)[/b][hr]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
[b] Message="Column 'rent_RPMrecnum' does not allow nulls."[/b]
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)
[/quote]

this is the problem, can you help with this. I think my problem is misunderstood, the BO.Seek() is horribly slow unless the primary key on the BO is changed to the field I am seeking against as Ben suggested above, BUT when I do this, calling BO.Add() results in the error above even thought I have default values defined for all my "NON NULL" fields.

RE: seek() slowness

Ivan George Borges — Tue, 03 Apr 2007 09:07:03 GMT

[quote][b]Ben Hayat (04/03/2007)[/b][hr][quote]I did!

With FORTRAN. [Crazy] [/quote]

IBM 360/370
Univac 1108

Fortran
Cobol
Basic
Assembly
JCL
Algol
.
.
.
:P[/quote]

Hey, Ben.

I walked the same path!

RE: seek() slowness

Steve L. Taylor — Tue, 03 Apr 2007 08:59:04 GMT

1983 - Keith was 10, Trent was 7, Ben was 2 -- ok I am a little older than you guys -- by a freaking lot. I was 31 in 1983.

Either y'all are precocious or maybe I am mature. Actually, I started on punched cards and tape. At one time I could read Baudot...

I think Ben and Trent are counting the days until the nursing home comes and gets me...

RE: seek() slowness

StrataFrame Team — Tue, 03 Apr 2007 08:46:55 GMT

What's a punch card? :D

RE: seek() slowness

Ben Hayat — Tue, 03 Apr 2007 08:23:01 GMT

[quote]I did!

With FORTRAN. [Crazy] [/quote]

IBM 360/370
Univac 1108

Fortran
Cobol
Basic
Assembly
JCL
Algol
.
.
.
:P

RE: seek() slowness

Ivan George Borges — Tue, 03 Apr 2007 07:33:20 GMT

I did!

With FORTRAN. :crazy:

RE: seek() slowness

Greg McGuffey — Mon, 02 Apr 2007 21:55:30 GMT

Jeesh, you guys probably never even used a punch card! :P

RE: seek() slowness

Trent L. Taylor — Mon, 02 Apr 2007 21:26:09 GMT

[quote]do I have it right?[/quote]

Basically. I was not aware that this was the type of database your were working with. Could you use scalar methods on the database to give you the info you need rather than using the Seek on the ADO.NET datatable? Just a thought....

[quote]PS - in 1983 I was 10 [/quote]

ROFL....not that I have any room to talk....I was 7 :D

RE: seek() slowness

Keith Chisarik — Mon, 02 Apr 2007 20:58:08 GMT

PS - in 1983 I was 10 :)

RE: seek() slowness

Keith Chisarik — Mon, 02 Apr 2007 20:56:58 GMT

I must be reading the old school stuff, .dat files, a file for each index.... stuff like that. I have no clue what they are doing these days, I downloaded Visual Dataflex just for the database tool. I was handed an old application that is no longer supported and has no documentation to work with.

RE: seek() slowness

Ben Hayat — Mon, 02 Apr 2007 20:52:35 GMT

[quote]Dataflex table[/quote]

Good old DATAFLEX :)
Are they still around? I used it from 1983 to 1985. It's proprietary data file format (back then) was very solid.
Are they using Btrieve now? Or Pervasive SQL?

RE: seek() slowness

Keith Chisarik — Mon, 02 Apr 2007 20:08:37 GMT

Part of the problem is that the volume isn't in SQL exclusively, I have to read a table of roughly 1/4 million records from a Dataflex table via ODBC (seems all I do these days is read old data but thats another story).

I am trying to create a sync method that will mirror the table in SQL for me to program against. So for each of the 250,000 records I need to either add a record or check the fields to see of they have been updated. It is the initial "look for this record" that is taking the time. I think what you are saying here is that I want to create a SQL proc to search for the record, not rely in the BO seek() method since ADO wasnt built to do that... do I have it right?

Thanks everyone for your input and time. For the time being I have abandoned doing the whole table at once, and gone for only updating the records the user calls for at any given time, I will eventually need to be able to "refresh" the entire SQL table against the live Dataflex datasource for reporting though.

RE: seek() slowness

Ben Hayat — Mon, 02 Apr 2007 18:41:40 GMT

[quote]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 [Smile] 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[/quote]

Hi Trent; This was a very valuable point. Could you please make a note to bring this up in the class to cover it in more detail?

Much appreciated!

RE: seek() slowness

Greg McGuffey — Mon, 02 Apr 2007 17:12:13 GMT

Man, if I'd just waited a few minutes, I would have see what Trent posted....:blink:

Seems I not as much a dinosaur as I thought :w00t:

RE: seek() slowness

Greg McGuffey — Mon, 02 Apr 2007 17:07:07 GMT

By "set based" I'm referring to the way SQL does things the most efficiently. It works best on sets of records, not on individual records (or rows). Looping through the BO (in SQL Server, cursors) is row based. Row based manipulation provide lots of power as to what can get done, but is not as fast (generally) as using SQL to manipulate a whole set at once (that is not as fast for SQL server to get the work done. Languages like .NET are more efficient at row based manipulation. Which is fastest would probably depend on all sorts of variables, like efficiency of code, hardward, connectivity, ect.).

Set based manipulation is great if you have something you want to do to the data based on some criterion. E.g. lets say you have a database containing a table of movie rentals. You want to mark all rentals more than a year old as needing maintenance (like polishing the DVDs). It is more efficient to use an update statement than to loop through the rows:

[codesnippet]Update rentals -- table with rentals
Set MaintenanceStatus = 1 -- lets say this means it need maintenance
Where PurchaseDate <= DateAdd(year,-1,GetDate()) -- gets date one year ago[/codesnippet]

Since this sort of thing only needs to be done infrequently, the user doesn't actually need to see the data to get the job done, a SQL statement is the way to go. You could create a sproc (for max speed, as it would be precompiled in SQL) or you could just create a method in a BO.

However, there are times when this won't work too well. If what you need to do is complicated or not data related or the data is in another database or the user needs to see all the data to decide what to do (the data would thus be on the client anyway), then row level processing is needed. It turns out that it isn't actually needed very often though.

I hope this helps. I come from MS Access background, not a VFP, so I had to learn a lot about SQL Server as I had it doing lots of work to speed up things. Sounds like VFP was speedy (always heard that, but I wasn't allowed to use it when I started doing db development...long story). The whole disconnected data thing in .net is different and I'm still getting used to it. It may be that this world view is now inaccurate or not as accurate, as it might be more efficient to use a loop in .net than in SQL server. I'm watching this post, hoping to learn a lot also :hehe:

RE: seek() slowness

Trent L. Taylor — Mon, 02 Apr 2007 16:53:46 GMT

[quote]Trent: /points and laughs[/quote]

Not at all!!! ;)

[quote]and yes, that is the column I set to be the new PK[/quote]

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

RE: seek() slowness

Keith Chisarik — Mon, 02 Apr 2007 14:41:25 GMT

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")}

RE: seek() slowness

Keith Chisarik — Mon, 02 Apr 2007 14:38:47 GMT

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
[b] Message="Column 'rent_RPMrecnum' does not allow nulls."[/b]
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)

RE: seek() slowness

Keith Chisarik — Mon, 02 Apr 2007 14:15:33 GMT

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

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.