Search

PowerBuilder – Filter vs Find performance

Posted on Friday, May 6th, 2011 at 8:20 pm in

If you have rows in a datawindow/datastore and you want to look through them for a match against some new code (say you are looking for an existing entry so you can add to the quantity), it is better to use the Find method in a loop than to use the Filter method.

Filtering large sets of data causes the movement said data from the Primary to the Filter buffer which takes more time than finding a single row. If the initial selection of the data was limited (WHERE clause or JOINS) then there wouldn’t be much difference (and you wouldn’t be sucking all the extra data back to the client either).

If you are dealing with data displayed to the user you have a new set of issues with Filter including triggering of RowFocusChanged, loss of selected row if it’s filtered out, display ‘jumping/flicker’, etc.

An interesting take on this can be found in the following posts from 1999.

No Good

FOR ll_i = 1 TO dw_A.Rowcount()
	ls_code = dw_A.Getitemstring(ll_i,'code_column')
	ls_filter = "this_code = '" + ls_code + "'"
	dw_B.Setfilter(ls_filter)
	dw_B.Filter() // hits the database each time
	FOR ll_j = 1 to dw_B.Rowcount()
		ll_quantity += dw_B.Getitemnumber(ll_j,'this_quantity')
	NEXT
NEXT

Good

FOR ll_i = 1 TO dw_A.Rowcount()
	ls_code = dw_A.Getitemstring(ll_i,'code_column')
	ls_find = "this_code = '" + ls_code + "'"
	ll_foundrow = dw_B.Find(ls_find, 1, dw_B.Rowcount() + 1)
	DO WHILE ll_foundrow > 0
		ll_quantity += dw_B.Getitemnumber(ll_foundrow,'this_quantity')
		ll_foundrow = dw_B.Find(ls_find, ll_foundrow + 1, dw_B.Rowcount() + 1)
	LOOP
NEXT
Tags:

Top