Seek Vs. Locate on 5Milion Record Table!!

[EzLogic]

Hello Everyone!..

A while back, I posted a question as to what is better Seek or Locate. Many users posted repsones and some of them had mentioned that Locate (with indexed table) could be as effective as the Seek Command.

So I tested it.

I created one Table, and ONLY 1 Field (Char 10) Indexed Ascending.
and then I created a small prg to insert 5 milion records.
nCount = 1
do while nCount <=5000000
Y = ltrim(str(nCount))
Append Blank
Replace Field1 with Y
Enddo

And then, I create a small form, with a Grid, and 2 text boxes. On each one of them in the Lost focus, I used Either
Seek and one I used a Locate, and I timed it.
x = seconds()
Set order to Field1
Seek this.value
if !eof()
Messagebox('found and it took: ' + str(seconds()-x,5,5))
else
messagebox('Sorry, not found')
endif

Same thing with the locate.

And the seek always took .0016 or so seconds,

And the locate took from 1 second to 7 seconds....

Is that normal?

I know that seek will ONLY work on 1 index at a time. And locate can optimise the query on multiple index.... But, I haven't tried it.... How fast can a locate be on such table or that size?

Let me know what you think.

Thank you in advance! Tekno

 

[Mike Gagnon]

>>>>>>>>>
Is that normal?

I know that seek will ONLY work on 1 index at a time. And locate can optimise the query on multiple index.... But, I haven't tried it.... How fast can a locate be on such table or that size?
>>>>>>>>>

Yes, I believe that is normal. But you can seek on a concatinated index (ie: INDEX ON DTOS(DATE1)+ALLT(STR(PKField)) tag myIndex

 

[ramani]

HI
SEEK will almost always win when compared with LOCATE. SEEK is like an expert while LOCATE is like an allrounder. NOt to say that you dump LOCATE. Often we get situations where we dont want to index. IN that case LOCATE will solve the matter wher SEEK cannot do it.

LOCATE.. has to go thru all the records and find the answer. So if the record number 1 is what you are looking far.. LOCATE could find that as fast as SEEK.

Hope this helps ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[EzLogic]

Thank you both,

You made it more clear to me!

Tekno

 

[chpicker]

LOCATE can be just as fast as SEEK, even if you're only using a single index. You have to use an optimizable FOR clause in the LOCATE statement. Since you didn't post the LOCATE version of your code, it's hard to say, but I just tried your experiment and had very different results.

One table, 5 million records, created like this:
[tt]
CREATE TABLE test (field1 c(10))
FOR i=1 to 5000000
INSERT INTO test VALUES (ALLTRIM(STR(i)))
NEXT
INDEX ON field1 TAG field1
[/tt]
I then close the table, wait a few minutes for the hard drive activity to finish up *grins*, and try this:
[tt]
USE test
LOCATE FOR field1=&quot;1565786&quot;
[/tt]
The record pointer moved instantly because the expression I used in the FOR clause is fully Rushmore optimizable.

Ian

 

[EzLogic]

Ian,

I tried that, but, you don't always get the same speed with the locate command. Sometimes it is very fast, and sometimes it could take upto 5 seconds, and even 10 seconds.

I have P4 1.6, 384DDR Ram, 7200 rpm disk...

But, seek never failed the .016 second mark!

I even tried the seek on a Pii 266 mhz, same result. you don't even wanna hear about the locate, It is embarrasing!



Tekno

 

[Mike Gagnon]

TeknoSDS,

Well, then you certainely don't want to hear about
LOOKUP(), which is even slower then LOCATE()

 

[EzLogic]

lol ..

Well, the reason I even asked in the first place is to know how you can get the best out of locate. Since, I use locate alot. (multiple fields)....

Tekno

 

[rianeiromiron]

TeknoSDS

Hi:

Just a comment.

I think there are somo points you should consider:

You are using LOCATE in a &quot;linear&quot; database, meaning a table with only one field per record. However, your LOCATing speed would decrease (bigger search times) in a real world table.
Try creating a 5,000,000-record table but instead of 1 field try, let's say 20 fields per record. You will notice the difference.
To avoid this problem, try to &quot;normalize&quot; your database

(A comment for all: I bet you all remembered your college years when you heard &quot;normalize&quot.

If you haven't heard about &quot;database normalization&quot; I suggest you take a look at

http://databases.about.com/library/weekly/aa080501a.htm

I hope my comments helped you in the understanding of the relation of speed-design.

Rianeiro

 

[Uncanny]

TeknoSDS,

You could also try INDEXSEEK(). It searches an Index that is active or specified in the function parameters and only moves the pointer if the condition is met. Very Fast! Wicked Fast! My bet is it would beat a standard SEEK and always beat LOCATE in a contest of speed. The INDEXSEEK() returns a .T. or .F. so you can use it in an IF/ENDIF statement. If the record is found, the pointer moves to that record and you can start whatever process you need to.

Warning! If you use compound indexes you have to PADR() all the data together. Or, you can SET EXACT OFF and match a string in the beginning of the data. Remember to SET EXACT ON after you have completed the INDEXSEEK().

I can provide a detailed example if needed.

Good Luck.

Allen &quot;Uncanny&quot; Schott

 

[EzLogic]

Uncanny, Please do provide an example. I am interested in this &quot;new&quot; function to me!!!

I am always open to learn new techniques and ways to improve.

Thank you in advance! Tekno

 

[Uncanny]

Hi TeknoSDS,

You want Code, you got Code! I have provided a couple of ways to use the INDEXSEEK(). One small drawback, INDEXSEEK() is only available in VFP6.0 and higher. Hope this code will work for you.

* Create Test Database
Create Cursor TEST (Key C(5), Data C (5))
Select Test
* Populate Test Database
For nX = 1 to 10
For nI = 65 to 90
Scatter Memvar Blank
Append Blank
* Create KEY data from nX and nI
m.Key = PadL(Alltrim(Str(nX)),2,&quot;0&quot + ;
PadL(Alltrim(Str(nI)),2,&quot;0&quot
* Create DATA data from Upper
* Case A-Z combinations
m.Data = Chr(nI) + Chr(90-nI+65) + ;
Chr(nI) + Chr(90-nI+65)
Gather Memvar
Endfor
Endfor
* Create Compound Index on KEY + DATA
Index on KEY+DATA Tag KEYDATA
* Create Compound Index on DATA + KEY
Index on DATA+KEY Tag DATAKEY
* Define Search Strings
cKeySearch1 = &quot;0772&quot;
cDataSearch1 = &quot;HSHS&quot;
cSearch1 = PadR(cKeySearch1,5) + ;
PadR(cDataSearch1,5)
cKeySearch2 = &quot;0588&quot;
cDataSearch2 = &quot;XCXC&quot;
cSearch2 = PadR(cDataSearch2,5) + ;
PadR(cKeySearch2,5)
* Set the Order to KEYDATA
Set Order to KEYDATA
* Search KEYDATA index for Search String
Select Test
If IndexSeek(cSearch1,.T.)
Wait Window &quot;<&quot;+cSearch1+&quot;> Found!&quot; Timeout 10
Replace DATA with &quot;A A&quot;
Else
Wait Window &quot;<&quot;+cSearch1+&quot;> Not Found!&quot; Timeout 10
Endif
* You don't have to set the order.
* You can define the INDEX to search
* and TABLE to Search.
Select Test
* Clear Ordering
Set Order to
* Search for DATA+KEY = &quot;XCXC 0588 &quot;
If IndexSeek(cSearch2,.T.,&quot;TEST&quot;,&quot;DATAKEY&quot
Wait Window &quot;<&quot;+cSearch2+&quot;> Found!&quot; Timeout 10
Replace KEY with &quot;9999&quot;
Else
Wait Window &quot;<&quot;+cSearch2+&quot;> Not Found!&quot; Timeout 10
Endif
* You can also search for the beginning of an index key
Select Test
Set Order to DATAKEY
Set Exact Off
* Search for &quot;XCXC&quot; in DATA
If IndexSeek(cDataSearch2,.T.)
Set Exact on
Wait Window &quot;<&quot;+cDataSearch2+&quot;> Found!&quot; Timeout 10
* Scan data and replace KEY data
Scan Rest While Data = cDataSearch2
Replace KEY with &quot;****&quot;
EndScan
Else
Set Exact on
Wait Window &quot;<&quot;+cDataSearch2+&quot;> Not Found!&quot; Timeout 10
Endif


Have Fun.

Allen &quot;Uncanny&quot; Schott