can I do this a better way 2

[Judi201]

Hi!

For the first time I am experiencing a short wait for processing to take place. Still in VFP 6.0 and tables have 20 - 50k records each for the time period I am processing. I used SQL SELECT to get the records and transfer to archive. This is what I am doing to delete them from active tables. Wondering if someone would show me a better (maybe newer?)way.

*** archive complete - remove from current tables
		IF llGotScrap
			SELECT 0
			USE Casting!scrap ALIAS lcAlias EXCLUSIVE
			SELECT ordtemp
			SCAN
				lcJobsno = jobsnumb
				lcOrdrno = ordrnumb
				SELECT lcAlias 
				LOCATE FOR jobsnumb = lcJobsno AND ordrnumb = lcOrdrno
				IF FOUND()
					DELETE 
				ENDIF
			ENDSCAN 
		ENDIF
		*** now orders
		SELECT 0
		USE orders IN 0 EXCLUSIVE
		PACK

I realize I didn't pack scrap table in this, but I will fix that. The pause is not bad but made me think about speed and my method. Any suggestions?

TIA

Judi

 

[jimstarr]

Hi Judi,

Rather than SCAN/LOCATE I'd use DELETE...WHERE...

Jim

 

[DSummZZZ]

Since LOCATE starts at the beginning of the table and has to scan until it find a record matching the criteria, there can be quite a pause.
You should probably create an index and do a SEEK(). Of course, if those are numeric fields, you would have to index them on their STR() equivelent for it to work properly. But I think the time that takes would be worth a good tradeoff:

INDEX ON STR(jobsnumb ) + STR(ordrnumb ) TAG joborder

You could then look for the records this way:

IF SEEK(STR(lcJobsno) + STR(lcOrdrno))
   DELETE
ENDIF

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[baltman]

I concur with Dave. SEEK will seem instantaneous.

 

[Judi201]

Hi!

Thanks for the quick response. This forum is the best!!

Jim, I tried to make that work but couldn't. I'll go back and revisit for maybe it was a different issue. Thanks.

Dave, the fields are not numeric and are of varing length so I have an index (cdx 'jobsordr') like this.(with help from here)
PADL(jobsnumb,10,"0")+PADL(ordrnumb,10,"0")

When I tried to use this in a seek I couldn't get around a syntax error. Maybe you will point me in the right direction?

I might need to add that the ordtemp cursor holds the orders being archived so I need a match for each if it exists and there might not be a match.

Really would appreciate it.

Judi

 

[Judi201]

Jim,

I went back and used DELETE FOR ... in my present setup and it works fine (ALMOST instantaneous)

I would still like to try the SEEK and see how it goes (and I want to know what I am doing wrong as I would use that in other places.

Thanks a bunch!

Judi

 

[Judi201]

Well, back again. I must have had other issues involved when I tried this before.

Dave, I went back and used the index phrase as you did above and it works fine. I can't tell a difference in the two: DELETE FOR and SEEK but probably would if I had larger tables. Since these are as large as I expect them to get, I am happy with either.

Thanks to all.

Judi

 

[DSummZZZ]

I'm guessing that since you are using a cursor, it is an extract of the table you want to remove record from? If so, the field lengths are going to be the same. So you won't need to PAD the fields. Just create the index.

INDEX ON jobsnumb + ordrnumb TAG joborder

Now to delete more than one instance of that criteria, just put the SEEK() in a DO...WHILE loop:

DO WHILE SEEK(jobsnumb + ordrnumb)
   DELETE 
ENDDO

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Judi201]

Thanks Mike,
Of course you are right about lcAlias. I failed to clean up from an attempt at another approach and just used the same term this way. Bad-Bad.

Now I have gone from small problem to major problem. In the process of deleting records in the testing, I have reduced the table to the point that I need to restore it. When I started, this is what I did.

created folder with tables from old 2.6 project that have been modified for new structure but not ever part of DB. I thought I could just copy a new table over when I needed to start over and I have done this in the past.

This time I did something wrong and have error:
DATABASE IS INVALID Need to validate database.

I have never had to do this so I read all I could find and tried but so far no luck.

Would someone please tell me what I should do to safely copy a table into my project?

I removed the old table from the DB deleting it, copied the new table in and added it to the DB. I recreated the indexes and tried to use it. ERROR.

Any help will be appreciated.

Judi

 

[Judi201]

Mike,

Would you tell me more about validate the database? I read several threads here on the subject but not sure exactly how to go about it.

Thanks

Judi

 

[Judi201]

Mike

I apologize for not getting right back as you were staying with me. I took time out for a 'grandson break'.

I followed your directions and saw the message that index was 'fixed' (I don't remember the word), and now it seems to be OK.

I am very worried about this now because the only way I have to recover is to go back to a backup and manually look at each index and go to the new table and recreate. I made a lot of changes in the structure of the tables because the 2.6 developer used basically three huge tables and kept everything in them - customer, orders,jobs data, scrap, everything!!

I pulled out relavent data and set keys (to some extent) and tried to normalize within boundries of my knowledge. As I did this I tried to make sure I could programatically create the new tables, and append data(with lots of help from here - some of it from you).

I don't have anyway to deal with indexes like that but realize that I need it. If it is not asking toooooo much, can you give me an idea of how to go to a backup and get indexes to store in a table some way to include when I rebuild my tables?

Should I start a new thread for this?

Thanks for any help you can give me and for the info that fixed my data. When I tried to do that from other threads, I could not get it to work. The only thing different was that I just used CLOSE DATABASES instead of close all, clear all. I just don't understand all of this.

Thanks so much.

Judi

 

[JimWWinter]

If you have backups, you can just use each table and then

nIndexes = ATAGINFO(aTags)

to get an array of info on the indexes for that table.

Regards,
Jim

 

[benasumwa]

Hi Judi,

If your original tables have the correct indexes
then the code below will write for you a program
(index_fi.prg) which you can use to recreate indexes on the new tables.

close all

nfiles = adir(adbfs,"*.dbf")

if nfiles = 0 then 
   return
endif

my_reind = fcreate("index_fi.prg",0)
= fputs(my_reind,"close all")
= fputs(my_reind,"SET SAFETY OFF")

for x=1 to nfiles step 1
    use (adbfs[x,1])
    if tagcount() > 0 then
       _first = "use "+lower(adbfs[x,1])+" exclusive "
       = fputs(my_reind,_first)
       for j=1 to tagcount()
           _first = "index on "+lower(key(j))+" tag "+lower(tag(j))+" additive"
           = fputs(my_reind,_first)
       ENDFOR
       = fputs(my_reind,CHR(13))
    endif
endfor   

CLOSE ALL
compile index_fi.prg
CLOSE ALL 
return

I hope you find it useful


Time is the father of truth. Time will tell.
---------------------------
Benson O. A. (Infinity Link Limited)

 

[Judi201]

Jim, thanks for the reply. I needed just a little stronger push .

Mike, you've done it again! I ran gendbc and got just exactly what I needed. Studying that will allow me to do what I need to do.

benasumwa , I am studying that as well to incorporate into my build new tables. Thanks for your time in responding.

I have plenty to think about now.

You guys are the BEST - All of you.

Many thanks!!

Judi

 

[Judi201]

Fantastic!

With your help, I now have a prg that will find my old tables, make sure dates are brought in correctly, modify structure as needed and index as needed. I should be disaster proof now

Famous last words, huh?

Many thanks to all

Judi

 

[DSummZZZ]

Don't forget about GENDBC.
Since you're using a database, you can run GENDBC on it and it will create a program that would recreate the entire database, tables and associated indexes from scratch.
It is a good tool for recreating trashed indexes. You can just copy the indexing code from it.

DO Home() + '\tools\gendbc\gendbc'



-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[DSummZZZ]

Gee whiz. I missed that completely.
I saw benasumwa's chunk of code and thought that was what she was using.
Okay, time to get another cup of coffee.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm