Update Table From Cursor instead of Set Filter??

[dantheinfoman]

Hi All,

I have a new table gathering 25 years of sales notes from memos into individual records. Table is fine, works ok with a grid I made. My question (and related problem) is that since the table has 350,000+ records in it, it takes a bit to update when I do a SET FILTER TO blah and blah. . .

I thought perhaps there might be a way to do a SQL Select statement to get the same grid much faster and possibly update the original table from the cursor when the user is done or closes the form/grid?

1. Is that even possible?
2. If possible, does this speed things up when you have lots of records and want to do data entry with several possible filters/ sorts?

Thanks

Dan

 

[TamarGranor]

Probably the easiest way to do what you suggest is to create an updateable view based on the table, and use the view as the RecordSource for the grid. Then, to send the changes back to the original table, all you have to do is call TableUpdate().

Tamar

 

[dantheinfoman]

Hi Tamar,

Thanks so much for quick input! PS I loved your talk at the SW Fox 2015 on Optimization for Speed!!! I've used your whitepaper and info a lot these past few months.

I've never used a view before, only cursors, tables or rarely arrays. Can you tell me how it works a bit? How exciting!

Here's what I use to make the cursor for grid:

SET FILTER TO EVALUATE('PRCOMM.PROSPID=215')

OR Something like:

m.test123 = 'PRCOMM.PROSPID=215'
SELECT PRCOMM.* FROM PRCOMM ;
WHERE &test123;
INTO CURSOR SUPPLMT2 READWRITE

Thanks Tamar!!! Brilliant

Dan

 

[Olaf Doschke]

It is possible, when going deeper than just with simple queries. You need a view and better yet a cursoradapter configured to adapting to DBFs (native mode).
Cursoradapter is combining the strengths of SQLPassthrough (via ODBC driver to external data) and native sql ino cursor access.

It's not described in one sentence. The native builder already is a help getting you through all the important settings in its tabs step by step. It doesn't generate ideal but usable code. If you never used a builder, open any form, right click on a control and click builder... That's also available for the CA class.

You need:
A primary key field in all your tables
A primary index on that
A CA class set updatable via that keyfield.

If you never did classes, now is the time to start that.
CTRL+N->Class->Name as you like (eg myfirstca), Based on "Cursoradapter", save to "cas.vcx"

When you're in the class designer right click->Builder and go through this step by step. You have eberything at hand to do a simple to complex query starting from SELECT * FROM yourtable to setting SelectCmd at runtime.

What's the background, why is this an "adapter"?

You're used to USE a table and SET FILTER, buffer it, edit it and commit changes by TABLEUPDATE(). If you're not used to that and use a tabel without buffer learn these steps with native DBF usage first. Take a look at SET MULTILOCKS and setting Buffering of a workarea via CURSORSETPROP.

The cursoradapter enables you to work on a cursor you queried from a DBF in the same manner, so it adapts to the DBF ("backend") via a cursor. Since you configure keyfield, updatable fields etc. you have more than just a READWRITE cursor, you don't need to write UPDATE or DELETE statements to save the changes, you do TABLEUPDATE() as with DBFs themselves. So while you define what get's into the cursor by SQL instead of a SET FILTER, you still have the backlink to the DBF as if the cursor was the filtered DBF. To be able to order the cursor you either use ORDER BY in your query or index the cursor. Creating indexes on the small subsets you query may seem a step back from the permanent indexes of the DBF, but is totally realistic with just a few rows.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

I've looked into this a lot this evening and you've piqued my curiosity a LOT! I've had success only with making the cursor using the new CursorAdaptor, and when I mess with a field in the cursor called cur_prcomm and run TABLEUPDATE(), it does nothing. If I do ?TABLEUPDATE() it says .T., but the original table 'PRCOMM' does not update with my change to cursor cur_prcomm.


Here are my settings in case this reveals anything to you smart people:

DEFINE CLASS prcommca AS cursoradapter 
 	*< CLASSDATA: Baseclass="cursoradapter" Timestamp="" Scale="Pixels" Uniqueid="" />

	Alias = "cur_prcomm"
	CursorSchema = PROSPID N(6, 0), CTYPE C(3), COMTOP C(3), C_IMPORTED L, CONFER C(3), DATE D, IMPORT_DAT D, SHCOMMENT C(250), CREATEDATE D, USER_ID C(10), ALERTED L, SHCOMM M
	DataSourceType = "Native"
	Flags = 0
	Height = 22
	KeyFieldList = PROSPID, CTYPE,  DATE
	Name = "prcommca"
	SelectCmd = select * from PRCOMM where PRCOMM.PROSPID=318
	Tables = PRCOMM
	Tag = "C:\MITC2000\DEMO.DBF\SYS2000.DBC"
	UpdatableFieldList = PROSPID, CTYPE, COMTOP, C_IMPORTED, CONFER, DATE, IMPORT_DAT, SHCOMMENT, CREATEDATE, USER_ID, ALERTED, SHCOMM
	UpdateNameList = PROSPID, CTYPE, COMTOP, C_IMPORTED, CONFER, DATE, IMPORT_DAT, SHCOMMENT, CREATEDATE, USER_ID, ALERTED, SHCOMM
	UseDeDataSource = .F.
	Width = 23
	
	PROCEDURE AutoOpen
		*** Setup code: DO NOT REMOVE
		if not pemstatus(This, '__VFPSetup', 5)
			This.AddProperty('__VFPSetup', 1)
			This.Init()
		endif
		*** End of Setup code: DO NOT REMOVE
		
	ENDPROC

	PROCEDURE Init
		*** Setup code: DO NOT REMOVE
		local llReturn
		do case
			case not pemstatus(This, '__VFPSetup', 5)
				This.AddProperty('__VFPSetup', 0)
			case This.__VFPSetup = 1
				This.__VFPSetup = 2
			case This.__VFPSetup = 2
				This.__VFPSetup = 0
				return
		endcase
		set multilocks on
		llReturn = dodefault()
		*** End of Setup code: DO NOT REMOVE
		*** Select connection code: DO NOT REMOVE
		
		local lcDBC
		lcDBC = 'C:\MITC2000\DEMO.DBF\SYS2000.DBC'
		if dbused(lcDBC)
			set database to (lcDBC)
		else
			open database (lcDBC)
		endif
		*** End of Select connection code: DO NOT REMOVE
		
		*** Setup code: DO NOT REMOVE
		if This.__VFPSetup = 1
			This.__VFPSetup = 2
		ENDIF
		SET STEP ON
		this.CursorFill()  &&ds 06/01/16
		
		*** Setup code: DO NOT REMOVE
		if This.__VFPSetup = 1
			This.__VFPSetup = 2
		endif
		return llReturn
		*** End of Setup code: DO NOT REMOVE
		
	ENDPROC

ENDDEFINE

Is it obvious? Anybody know what I'm doing wrong? Thanks Olaf and Tamar!!!

Dan

 

[Olaf Doschke]

I just sparsely looked.

Did you use the builder or set the properties yourself?
Updatenamelist needs a comma separated list of space separated pairs of names.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

Just now got it working. My issue was just what you said, I didn't have the CURSORFIELD1 TABLE.FIELD1,CURSORFIELD2 TABLE.FIELD2 syntax correctly.

I got clarification on syntax from Doug Hennig's paper here: doughennig.com/papers/Pub/DataStrategiesAdvanced.pdf

Thanks a lot. I'm going to do some speed comparison using this method and report back.

Dan

 

[Olaf Doschke]

Using the builder, this is automatically created for you. For example I created a CA for the Products table of the Northwind sample database and choose update all fields, and the builder filled the UpdateNamelist with:

CATEGORYID PRODUCTS.CATEGORYID, PRODUCTID PRODUCTS.PRODUCTID, DISCONTINUED PRODUCTS.DISCONTINUED, PRODUCTNAME PRODUCTS.PRODUCTNAME, QUANTITYPERUNIT PRODUCTS.QUANTITYPERUNIT, REORDERLEVEL PRODUCTS.REORDERLEVEL, SUPPLIERID PRODUCTS.SUPPLIERID, UNITPRICE PRODUCTS.UNITPRICE, UNITSINSTOCK PRODUCTS.UNITSINSTOCK, UNITSONORDER PRODUCTS.UNITSONORDER

Don't do this manually, use the builder.

The one downside of it, is, it only allows you to build the sql command, refer the schema and allows to pick from the field list, if you have a DBC, as the DBC is the core setting on the first tab in the last configuration item "Database". If that is not set you can only hand write the Select command in the Data Access tab, the Build button is disabled. You then miss a lot of automatisms.

There is a very strong belief free tables are faster, that's surely true for small operations, but a DBC just adds another free table to the game, the DBC itself. It's true each long fieldname is a lookup in that free DBF and that of course is an extra step to take. It matters less and less in case of larger data volumes and complexer queries, once a long field name is in VFPs memory internal Nametable, it's not a file lookup anymore. I'd opt for adding all your free DBFs into a DBC, if you don't use a DBC up to now. It's not changing everything, ie you can still USE some.dbf without first OPEN DATABASE, the database is then opened automatic, if it's not open. All code working now continues to work, but you unlock things like a default value. Essential, if you ask me.

Bye, Olaf.

 

[dantheinfoman]

Thank you, Olaf for that descriptive answer. I appreciate it!

A follow up question, since I've invested several hours in the CursorAdapter class and integrating it with my grid:

Which would be faster:
1. A vague cursor and just filtering it by using

BROWSE FOR ... AND BLAHBLAH

Or
2. Is it better to load the CursorAdapter with an updated cursor 'Where' statement when you want to update filtering such as

loPrcomm.SelectCmd = loPrcomm.r_basesql + " where " + thisform.r_filter + " AND '" + ALLTRIM(thisform.txtsearch1.Value) + "' $ UPPER(supplmt.shcomm)"
loPrcomm.CursorFill()

Any thoughts or opinions are welcome!

Thanks

Dan

 

[dantheinfoman]

Just a quick update:

When I tried doing

*fill cursor based on updated settings
loPrcomm.CursorFill()

it closed out the cursor 'supplmt' and the grid now is blank, no gridlines and no visible data.

Does that mean I have to do a cursor at the beginning with the whole table and do BROWSE FOR ... in order to filter the cursor and if so, does that actually speed things up using a cursoradapter. OR is there a way to refresh the CA and requery it with updated WHERE statements while maintaining my grid.

Thanks

Dan

 

[Olaf Doschke]

When you bind to a grid, you have the same problem as with normal queries, but you can REQUERY() or use the ca.CURSOERREFRESH() call, indeed a REQUERY(ca.Alias) does cause a ca.CURSORREFRESH(), the alias is linked to the cursoradapter.

The requery or cursrorefresh will redo the .SelectCmd query as it was at CursorFill. Put ? cSelectCmd in BeforeCursorRefreh and then do REQUERY() or CursorRefresh and you'll see.
So one way for different results at a requery would be parameterized queries using ?variable in any possible place and changed variable values.

Aside of that you can change .SelectCmd and do a .cursorfill(), if you don't bind the grid to the ca cursor but have a separate grid cursor you initialize the grid cursor by a SELECT * FROM ca.Alias into CURSOR grdCursor READWRITE, later refresh by CursorFill of the ca, then ZAP in grdCursor and APPEND FROM DBF(ca.Alias). That way you obviously can't edit data in the CA cursor in the grid, but that's not a major use of grids anyway.

But besides that I'm sure I use a ca class, that upon cursorrefresh executes the current SelectCmd and not the Selectcmd as it was at CursorFill. I have to look into this later.

Bye, Olaf.

Edit: I find a very simple (and somewhat bad) wiring of CursorRefresh WITH CursorFill here. It would not work with grids, but I mainly use an ActiveX all-in-one grid/treeview control, where cursor records are translated to node items anyway, so that doesn't strike me.

I had an idea setting the cSelectCmd of BeforeCursorRefresh influences CursorRefresh, but that doesn't seem the case, so this isn't passed in as byref parameter as the url of a BeforeNavigate2 event of the WebBrowser control. There might be a chance using the RefreshCmd property with a differing SQL. I may play with this over the weekend, but the secondary cursor is an idea always working anyway.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

I got it working by using the second cursor like so:

PUBLIC nSecs
nSecs = SECONDS()

** determine which optionbutton is pressed
DO CASE
CASE thisform.optiongroup1.Value = 1 && This contact only
	m.obResult = thisform.r_filter 
CASE thisform.optiongroup1.Value = 2 && This contact only
	m.obResult = "uppe(PRPRSPCT.PROSPECT) = uppe('" + thisform.r_prospect + "')"
OTHERWISE
	m.obResult = thisform.r_filter && this only
ENDCASE

**Whether to get imported or just Sales (direct) notes
m.obResult = m.obResult + thisform.r_imported &&eg " AND EMPTY(prcomm.c_imported)" or "" if false

IF !EMPTY(thisform.txtsearch1.Value)
	loPrcomm.SelectCmd = loPrcomm.r_basesql + " where " + m.obResult + " AND '" + ALLTRIM(thisform.txtsearch1.Value) + "' $ UPPER(prcomm.shcomm)" + thisform.r_orderby 
ELSE
	loPrcomm.SelectCmd = loPrcomm.r_basesql + " where " + m.obResult + thisform.r_orderby
ENDIF

[b]*temp change recordsource as filler
thisform.grdComAll.RecordSource = 'dummy'

*fill cursor based on updated settings
loResult2 = loPrcomm.cursorfill()
thisform.grdComAll.RecordSource = 'supplmt'[/b]

WAIT WINDOW TRANSFORM(SECONDS() - nSecs) + ' seconds for speed test' nowa
thisform.grdComAll.Refresh

And everything was finally working smashingly, however, now when I delete a record in the cursoradapter, it does not give this information back to the original table, despite doing TABLEUPDATE(). Also, once I delete a record in the grid, it doesn't receive other updates to that record, such as changing a combobox or typing somewhere. Once deleted, it sends NOTHING to the original table.

Thoughts?

Thanks

Dan

 

[Olaf Doschke]

>however, now when I delete a record in the cursoradapter, it does not give this information back to the original table,
Well, the solution I propopsed would use a grid cursor and I asiad, this way, the grid will NOT edit the cursoradapter cursor.

Is supplmt the CA cursor alias? Then you do bind to it and use another solution strategy with a secondary dummy cursor just for temporary recordsource replacement.
That's OK, too, but then where is your save here?

You have to do the TABLEUPDATE at least before you requery other data by the next CursorFill(), otherwise you throw away the buffered changes.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

Yes supplmt is the CA cursor alias. Fields in the grid are like ControlSource = 'supplmt.ctype' (and the like).

The real table is called prcomm.dbf. I didn't have a "Save" button currently as I thought I was experiencing automatic saves once I leave a record and once the CursorFill() runs again. The original table prcomm seemed to always be updated, so I didn't give it much thought. I can add a Save button though. When I execute a Tableupdate(), it does not tell prcomm that the current record needs to be deleted.

Sorry if I'm not fully grasping it all just yet.

Thanks

Dan

 

[Olaf Doschke]

I don't understand what you're saying. Too much and contradicting information.

You say
>I didn't have a "Save" button currently
You don't need a save button, you can do Tableupdate before CursorFill, can't you?
The user does cause an action to cause your Cursorfill code to run, changing the filter. Anytime you do a CursorFill again, you can first do a Tableupdate. You could also make this a behavior/code of the CA.

>I thought I was experiencing automatic saves once I leave a record and once the CursorFill() runs again
Automatical changes happen without buffering. You don't need to set buffering, but it makes things more controllable to buffer and then save or revert.

If you want all the filters to contribute to the buffered changes, that's not working, then rather have theree cursoradapters and finally commit all of them or none.

People tend to always want every change to go directly into the DBF. That is the worst thing you can do and totally unfair about all other LAN usage. Don't let every single field change and valid event change the DBF. That's what's causing slowness with several applications done the same way and several users. It doesn't scale well, bacause you have network packets transport just one small value of net data with meta data at least two ip addresses (source and destination) and much more.

>The original table prcomm seemed to always be updated, so I didn't give it much thought
You talk of the time when using SET FILTER and before introducig CA? You know how that behaved.

Using buffering alone (without CA, buffering the DBF) means concentrating all saving to closeing the DBF. Using CA also means reading is concentrated and not happening with each scroll.

Bye, Olaf.