Find and edit a record in large table

[Toman]

I wish a nice day to all

For viewing certain records in large table (more then 400 000, more then 30MB on disk) I use SQL, because it is quick and the filtering can be constructed easily and even from code.

Problem occurs when some records need to be edited.

To move the record pointer to the matching record in original table I'm using LOCATE FOR firstfield=... ...,lastfield=..., which seems to be too clumsy here.

Is there a better way? (neither UPDATE nor SEEK is suitable in my case)

Thank you
Tom

 

[BPeisch]

How about a parameterized view? You can make the view updatable, which would be a lot easier.


-BP (Barbara Peisch)

http://www.peisch.com

 

[Toman]

Hi Barbara

I've really overlooked parameterized view. After refreshing my poor knowledge (especially with your excellent View on Views article (

http://63.193.37.117/publicfiles/views.zip).

I can say, that parameterized views would fit my needs and are much more elegant then SELECT and LOCATE FOR. Thank you.

But in the meanwhile I also did some speed testing on my large table and the results are very surprising (at least to me). I've supposed the SELECT * (and VIEW respectively) to win hands down, then LOCATE FOR and the slowest SET FILTER.

For testing purposes one additional unique record with field Surname = "qwerty" was added to the end of the table. By means of simple "speed test" program

SET HEADINGS OFF
CLEAR
USE BigTable	&& about 400 000 records, no index no relations
SET FILTER TO


	***** 1. SELECTING record
GO TOP
time0 = seconds()
SELECT Surname FROM bigtable WHERE Surname = "qwerty" TO SCREEN PLAIN
time1 = seconds()
? " Time to SELECT ..." + STR(time1 - time0,8,2)


	***** 2. LOCATING record
GO TOP
time0 = seconds()
LOCATE FOR Surname = "qwerty"
DISPLAY FIELDS Surname NEXT 1
time1 = seconds()
? " Time to LOCATE ..." + STR(time1 - time0,8,2)


	****** 3. FILTERING record
GO TOP
time0 = seconds()
SET FILTER TO surname = "qwerty" 
GO TOP							&& forcing filter to work
DISPLAY FIELDS Surname NEXT 1
time1 = seconds()
? " Time to FILTER ..." + STR(time1 - time0,8,2)

I have discovered that all three methods are almost the same as for the speed. On my computer

SELECTING took about 0.17
LOCATING took about 0.18
FILTERING took about 0.22 seconds

The conclusion seems to be, that for my purposes is the best way to find and eventually edit records with the old good SET FILTER.

Thank you for your answer once more
Tom

 

[BPeisch]

What you're seeing is the power of Rushmore optimization. Just keep in mind that things can get weird with changing the filtering if you want to display the results in a grid. If you do run into problems, just post another question here.


-BP (Barbara Peisch)

http://www.peisch.com

 

[jimoo]

Is there an index on surname?

If so, the seek and locate should be the fastest?


Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Toman]

Yes. Seeking is so fast that it can't be measured with seconds() function on my "BigTabel". To take my "research" to the end I made new "artificial" table by three times appending BigTable into the SuperBigTable (it has nearly 1 million records and 100 MB on disk). And also here is seeking immeasurable – it means that it takes less then 1 millisecond.

Unfortunately I can't use seeking, because I'm always finding records which must meet more conditions simultaneously (or can I?).

By the way – later I tried to work with even bigger table (3 mil recs, 0.3 GB) but it completely knocked my computer down. Just closing browse window with filtered table inside took one minute.

Working with SuperBigTabel gave me further interesting results and I can' resist to publish them here

-------------------------------------------------------------------------
How long does it take to retrieve records
from table with 1 million records (in seconds)
-------------------------------------------------------------------------
                        single 	                      komplex
               conditional expression             conditional expression

               no index        indexed           no index       indexed
Method
-------------------------------------------------------------------------
SQL SELECT       0.53            0.02              0.83           0.02
LOCATE           0.58            0.16              0.56           0.27
SET FILTER       0.69            0.20              0.69           0.30
SEEK              n/a            0.01               n/a            n/a
-------------------------------------------------------------------------

For me the most surprising fact is that SQL SELECT profits from existing index.
Bye Tom

 

[jimoo]

You have a couple of options.

1.you can still use the seek to go to the exact record if you can identify it based on the multiple keys. In this situation, you will use a compound index.

Example:
Assume it is index on year and customerid

SEEK lcMyYear + lcMyCustomerID
that will bring you to the correct record.

2. If other records have the same key combinations, and you need to do some if/else processing, I still suggest seek on large tables to get you to the first occurrence and then process until it no longer matches and not eof().


SEEK lcMyYear + lcMyCustomerID
that will bring you to the starting point.
DO WHILE lcMyYear = customer.year +;
lcMyCustomerID = customer.customerid +;
AND NOT EOF()
IF whatever

Else whatever

ENDIF
SELECT customer
SKIP
ENDDO

This method of processing is more time consuming to program than a select statement, but almost always (usually much) faster when extracting a small set of records from a large table.

* Side note - You can use SCAN/ENDSCAN instead of do-while. The SCAN command is slightly faster.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[Toman]

These notices from experienced programmer are very valuable for everyone. It only takes me a moment to understand that if you are talking about alternative to DO WHILE command you mean SCAN REST, don't you.
Thank you very much Jim.
Tomas
P.S. There may be something wrong with your pages

http://www.deltabg.com.

My MSIE 6.0 cannot see anything at the leftmost column (for your information I'm putting a PrintScreen of your page to

http://www.radova.cz/deltabg.jpg

temporally).

 

[BPeisch]

Toman,

DO WHILE works fine in the context Jim posted. He could have also used SCAN WHILE, and left out the test for EOF() and the SKIP at the end of the loop. SCAN REST would NOT be the right scoping to use in this case, because you don't necessary want to keep going through ALL the records, just the ones that still match the SEEK condition.


-BP (Barbara Peisch)

http://www.peisch.com