Will using the "seek" function instead of "locate for" reduce my performance?

Question

I was told that rishmore is optimizable for functions like locate for. Will the seek command slow my performance in a multiuser environment?

Answer 1

I think you misunderstood Rushmore optimization. There is nothing in VFP that beats seek.

Answer 2

Seek and Indexseek functions are the best options to use.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 3

SET KEY TO is the fastest....

---

dni

Answer 4

>> SET KEY TO is the fastest....

>> Seek and Indexseek functions are the best options to use.

 

For what ?

 

Sorry, but this answers are as helpful as anything else. 

As ususal: It depends... It depends on what Larry wants to do.

 

@Larry:  Let me explain that Rushmore thingy a little bit more:

 

1) an Index is a good thing. Using an index with SEEK() is the fastest way to position to a single record, but has nothing to do with Rushmore. As long as you have only exactly that indexed value to look for (like a customer number, a Order number, a ZIPcode), then you use SEEK. And SEEK needs an index. That's all about that. At any time you can have only one active index, even though you could have multiple indices created on different index expressions. That's why the SEEK() funtion has an optional parameter to use a different index-tag for each call.

 

2) But sometimes you have to find a record based on not only one expression (OrderNo = 1234) but on several other conditions, like "Find that order, which is from customer 1234 and was issued in last November and is not yet payed". In that case a SEEK will not help too much, because SEEK is optimized to work on only one search-expression.  Of course you could theoretically build an index based on all those filter expressions into one huge index expression, but that's not really practical in real life scenarios.  But that multiple search-requirement is a typical scenario for the LOCATE command:

LOCATE FOR CustNo = 1234 AND MONTH(OrderDate)=11 AND YEAR(Orderdate) = YEAR(date())-1 AND PayState =.F.

 

Let's assume, that VFP doesn't know about that Rushmore technology. In that case VFP will now have to GO TOP in your order-table (because a FOR condition implies a default-range of ALL records), and then skip through your whole table until it finds the first matching record. Depending on your companies excellent business over the last ten years, you will have maybe 1 million orderrecords originating from 1995 up to 2010. And VFP will happily skip through that table from top to end, and this will surely take up some time.... until it finds that record. Or maybe not, maybe there's no matching record, thus VFP will have done a full table-scan from top to bottom without any result. Bummer: You have waited/wasted 1 minute of your lifetime for nothing.

 

That's where Rushmore comes into the game: VFP can look at that LOCATE FOR expression, slice it into seperate pieces and tries to find a matching index for each expression. It could well be that there's an index on CustNo. Rushmore now builds a Bit-Array in memory, where each position represents one recordnumber (i.e. Bit1 is record 1, bit 5 is record 5, etc). That array is initialized with each bit = 0. And for each matching index and expression it builds one of those Bit-arrays.    

Now it looks for CustNo=1234 in the index and gets the corresponding record numbers back from the index. It flips the bits of the BitArray on that recordnumbers to 1; and then continues with the next expression. Normally you wouldn't have an index on "Month(orderdate)" or "year(orderDate)", thus these two expressions cannot get retreived from an index. But let's assume that you have indexed that PayState field (and since you're using VFP9 and since it's a logical field, you surely used that fancy new BINARY index for that one). VFP transfers that matching paystate records into the second bitmap-array.  After that it has now two filter-expressions saved in two bit-arrays and two more filter-expressions yet unresolved. (This is also called a "partly-optimized query") VFP now combines those two Bit-Arrays and finds those bit-positions which are on "1" in both arrays. The bitposition represents the record number, thus only now it really has to read the big fat DBF file, and only for those some result records. Those records will now get compared with that two Date() checks and if those are matching it will return that record to you. 

BTW:  Reading a single record from a given recordnumber is very, very fast in a DBF (because of the fixed recordlength schemas of a DBF), compared to any Flex-length based databases like Access, or any SQL-Server. 

  

Thus, there are some key factors for understanding Rushmore:

a) It needs a matching index for each expression.

b) It needs to read the whole index-tree into that bit-array in memory

c) The more filter expressions you have, the more bit-arrays are created

d) reading that index-tree costs some time,

e) normally, Rushmore gets you much faster results than without it.

f) sometimes, Rushmore takes more time to get you the results than without it ;)

 

If you wonder about bullet f):

Sometimes it takes more time to read a whole index-tree, as it would have taken to transfer that DBF-records. That's typical for that DELETED flag: Since you normally work with SET DELETED ON, you have an implicit FOR NOT DELETED() attached to any operation. Regarding to the rules of Rushmore, that filter can get optimized with a matching index. Thus, you build a regular index like INDEX ON DELETED() TAG BlaBla.  Let's look at the storage here: We need 1 bytes for that "T" or "F" of a logical field, and 4 bytes for an Integer, representing the corresponding recordnumber. That makes 5 bytes per indexed record. If you have a table with 1 million records, that Deleted-Index alone needs 5 MBytes of storage. And needs the time to read that 5 MegaBytes into memory, each time you do your LOCATE or whatever.  All that for filtering out maybe 5 or ten deleted records which then did not get retrieved from the DBF, which would maybe have a recordlength of 1Kbyte. Thus we have read in 5 Mbyte of index information to save the transmission of 5Kbytes... Not a real good result.  

 

That's why before VFP9 everyone said: You should build an index for any often used Filter-expression, but don't build an index on Deleted().  Since VFP9 we have that new BINARY indextype, which is basically already that Bit-Array on disk, that's why it is very compact and very fast: We store only 1 Bit per record, instead of 5 Byte per Record, which makes a 40-times reduction in index-size. So now it's ok to index on deleted(), as long as you do it BINARY.

 

Hope that you now understand a little bit more what's happening "behind the scenes".

 

 

---

wOOdy
Microsoft Visual FoxPro Technology Advisor
Microsoft "Most Valuable Professional" from 1996 to 2009
Visit my XING profile! Don't know what XING is?

*´¨)
¸.·´¸.·*´¨) ¸.·*¨)
(¸.·´. (¸.·` *
.·`.Visual FoxPro: It's magic !
(¸.·``··*

 

Answer 5

Hi Jurgen,

While all that Rushmore explanation is good in general, we know that still it doesn't beat seek:) What I mean? Think of multiple expression query, where each field used in query is indexed (aka fully optimizable). Doing a straight query with multiple expressions is slower then doing seek or do a query with a single expression first and than do further query on that result set. ie:

Search records of a customer whose id is X and orders done between date1, date2. Simple it seems. Table would have indexes on customerID and OrderDate. A query:

select * from myOrders where customerId = ?m.id and orderDate between ?m.date1 and ?m.date2 into cursor xx

is slower than:

select * from myOrders where customerId = ?m.id into cursor yy nofilter

select * from yy where orderDate between ?m.date1 and ?m.date2 into cursor xx

or it is slower than:

set order to tag customerID

seek m.CustomerID

scan while customerId = m.customerID for between(orderDate, m.date1, m.date2)

So yes, it depends on what you want to do and despite that slowness one may choose first query (so called fully Rushmore optimized) because of its readability.

Answer 6

Hi Cetin,

 

I fully agree: Nothing beats a direct Index access (as I have written anyway). And of course any SQL-Select is supposed to be slower, just because it has to read in all indices, has to build the maps, has to copy the result records to a new table etc. But stil this is faster with Rushmore instead of no Rushmore. Thus even though there are some more sophisticated finetunings possible, you can get already stunning results if you just adhere to the principles of Rushmore.

 

I remember when that Rushmore technology was introduced in FoxPro 2.0, we had a running joke:

SET OPTIMIZE OFF (which disables Rushmore) is equal to SET CLIPPER ON  ;)

 

But in the end all boils down to: You need experience to unleash the full power of VFPs database engine. But in fact that holds true with any tool: As long as you're not knowing what you're doing, as long as you don't understand what's happening in the machine-room, you can't get the best results.

 

 

---

wOOdy
Microsoft Visual FoxPro Technology Advisor
Microsoft "Most Valuable Professional" from 1996 to 2009
Visit my XING profile! Don't know what XING is?

*´¨)
¸.·´¸.·*´¨) ¸.·*¨)
(¸.·´. (¸.·` *
.·`.Visual FoxPro: It's magic !
(¸.·``··*

 

Answer 7

"For what ?

 Sorry, but this answers are as helpful as anything else."

As anything what ? Are you sure ?  Are you moderator ? 

---

dni

Answer 8

Hi dni,

 

>> Are you sure ? 

Yepp ;)   As long as we don't know how Larry is doing his searches, a plain "SET KEY is the fastest" sentence without some more information is just misleading and not very useful; at least in my humble oppinion...  Sorry if I trampled on your feelings ;)

 

 

>> Are you moderator ? 

Nope. Do only moderators have the right to argue with you? ;)

 

---

wOOdy
Microsoft Visual FoxPro Technology Advisor
Microsoft "Most Valuable Professional" from 1996 to 2009
Visit my XING profile! Don't know what XING is?

*´¨)
¸.·´¸.·*´¨) ¸.·*¨)
(¸.·´. (¸.·` *
.·`.Visual FoxPro: It's magic !
(¸.·``··*

 

Answer 9

Then your answer is also not very useful too...In fact, you said nothing...you just slapped you keyboard ....and trampled nothing.
If you did not get the sense, why you comment my answer ? 

---

dni

Answer 10

I know that set optimize on is automatically set by default in VFP 9.0. I use the seek function to search for my records. The filter part in an sql is what I do not understand. I saw an example that you put using filter.

Is this optimizable?

select *;
from table_name;
where code_name=2;
into cursor examples

How can I make this faster?

Answer 11

Hi,

Do not use select * ....
Please select field that you need.

is the table you selected got index key ?
if got index pls do set order to tagname

and perform sql.

Thanks HTH.

---

MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST

Answer 12

Hi Larry,

 

>> Is this optimizable?

Of course, Why not?

>> select * from table_name where code_name=2 into cursor examples

Look at the where-expression. Take the left part, build an index on it. Ready.

 

INDEX ON code_name TAG Whatever

 

 

---

wOOdy
Microsoft Visual FoxPro Technology Advisor
Microsoft "Most Valuable Professional" from 1996 to 2009
Visit my XING profile! Don't know what XING is?

*´¨)
¸.·´¸.·*´¨) ¸.·*¨)
(¸.·´. (¸.·` *
.·`.Visual FoxPro: It's magic !
(¸.·``··*

 

Answer 13

You may try to index table by code_name, then use command:

SET KEY TO 2

You will have selected in table all records with this condition, it is the fastest way in a network environment....

---

dni

Answer 14

Hi,

Do not use select * ....
Please select field that you need.

is the table you selected got index key ?
if got index pls do set order to tagname

and perform sql.

Thanks HTH.

---

MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST

Actually, that's bad advice. As long as the tag exists, SQL commands will use it. SET ORDER before querying slows things down.

Tamar

Answer 15

Hi Tamar,

Thanks for correct me. then u mean i shud perform sql then only set order to ?
or how about set key to ?

Thanks.

---

MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST

Answer 16

If you're performing a SQL command and you're then working with the result, you don't need SET ORDER or SET KEY.

Tamar

Answer 17

HI Tamar,

Noted with thanks.

thanks,
Jeff.

---

MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST