Speeding up this process

[wlynch]

There is a faster way to accomplish this but I am not sure where to start. This places an additional line into the primary database and I know how much filters and insert blank will slow the process down. Any ideas?

******************
Set Deleted On
Select 1
Use Mydatabase
Set Safety Off
Set Filter To '###' $VSLPCKSCKS And '###' $VSLPCKSCK1
Copy To QCHOLD &&&the filtered data
Set Filter To
Select 2
Use QCHOLD

Do While Not Eof(2)

Select 2
Go Top
Store sqncnmbr To mSEQ
Scatter To mINS
Delete

Select 1
Locate For sqncnmbr = mSEQ
Insert Blank
Gather From mINS
Replace FullName With 'DO NOT MAIL ************************************'

Enddo

 

[DSummZZZ]

You are right. Filters are one of the worst inventions since RDBMS's were thought of. If possible, you should rethink how you want to extract the data and create an index on that field. You could then use a COPY TO FOR ...
Especially if the '###'s are the first characters in the field.
If no index is doable, even:
Copy To QCHOLD FOR '###' $VSLPCKSCKS And '###' $VSLPCKSCK1
may be quicker.
You may also want to create an index on DELETED().

Also, I would get away from using explicitly numbered work areas such as SELECT 1, SELECT 2. You should be using SELECT 0. From then on, you can SELECT it using its name without wondering if you've selected the correct work area:
Select 0
Use Mydatabase
SELECT 0
USE QCHOLD
And so on.

Another issue you have is that each pass through the DO WHILE, you're repositioning the record pointer back to the first record with GO TOP. There is no need to do that.
Also, SCATTERing to an array can sometimes be slower or hard on CPU/memory usage, destroying and reallocating that array constantly may cause memory leaks.

One question I have is why are you doing a LOCATE, then immediately afterward doing an INSERT BLANK? LOCATE will take time, then adding a record will take more. Why would you want to have duplicate records with only the 'fullname' field being different?
Anyway, if you have a proper index on your sequence number, adding a record will place it in the proper place according to the index.

Here is how I would change your code without any index creation though:

Set Deleted On
Select 0
Use Mydatabase
Set Safety Off
Copy To QCHOLD FOR '###' $VSLPCKSCKS And '###' $VSLPCKSCK1
Select 0
Use QCHOLD

SCAN
    Store sqncnmbr To mSEQ
    Scatter memvar
    Delete

    m.FullName = 'DO NOT MAIL   ************************************'
    INSERT INTO Mydatabase FROM MEMVAR

    Select QCHOLD  &&... really not necessary
ENDSCAN

Or, if all you are trying to do is add a 'do not mail' phrase to the name:

Set Deleted On
Select 0
Use Mydatabase
REPLACE ALL FullName WITH 'DO NOT MAIL   ************************************' ;
   FOR '###' $VSLPCKSCKS And '###' $VSLPCKSCK1

-Dave Summers-

Even more Fox stuff at:

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

 

[Mike Lewis]

Wlynch,

One thing you don't mention is indexes.

Depending on the size of Mydatabase, performance is likely to be very much better if there is an index on sqncnmbr. And if there was such an index, you should change the locate on that table to a seek.

Also, INSERT BLANK is very much a no-no. The old INSERT command (as opposed to the SQL equivalant) actually moves records around the table, which takes a long time. It is much better to insert new records at the end of the table (with INSERT or APPEND BLANK), and rely on the index to retrieve them in the right order.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

The first question would be - Why do you want to try to insert one or more blank records into the middle of an existing data table?

If this is for visual purposes, then you should do this by just Appending blanks into the table at the end and using an Index to make those records 'appear' where you want them. That is the fastest approach.
You might even want to do this to a Cursor (READWRITE) or a temporary Data Table rather than to the data table itself.

If this is for Reporting purposes, the same applies.

If this is a one-time 'fix' for a messed up data table, then just copy the intended records out to another table, APPEND BLANK, and then copy the remaining records out (repeat as necessary).

Dave has made some good suggestions above, but even before considering use of those suggestions, you have to ask WHY are you attempting to do so.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Wlynch,

Looking more closely at your code, am I write that it is basically looking for records that meet a filter, and copying those records back to the original table, but with the person's name set to DO NOT MAIL?

In other words, you end up with two copies of the filtered records: one contains the name, and the other contains DO NOT MAIL.

If so, here is a possible alternative, which should be much faster:

SELECT * FROM MyDatabase ;
  WHERE '###' $VSLPCKSCKS AND '###' $VSLPCKSCK1 ;
  INTO CURSOR QCHold READWRITE 
 
UPDATE QCHold SET FullName = 'DO NOT MAIL'
  
INSERT INTO MyDatabase SELECT * FROM QCHold

If we knew the names of all the fiels in MyDatabase, this could be speeded up further, by combining the first and second statements. But, even without that, I think it should be a lot faster than your original code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[wlynch]

Thanks!! I'll have to study all this. To answer your question DSumm, "Why would you want to have duplicate records with only the 'fullname' field being different?"

That is intentional. The DO NOT MAIL record is a QC piece that is pulled off the production line at the end of each pallet when printing the addesses.

Thanks for all the input guys!

Will

 

[wlynch]

Cool Mike! Ok, are the fields:

Field Field Name Type Width Dec Index Collate Nulls Next Step
1 OEL Character 40 No
2 FULLNAME Character 50 No
3 DLVRYDDRSS Character 50 No
4 LTRNTDDRSS Character 50 No
5 CSZ Character 50 No
6 ONECODE Character 65 No
7 KEYCODE Character 30 No
8 SQNCNMBR Character 16 No
9 STORENAME Character 65 No
10 STOREADDR1 Character 65 No
11 STORECSZ Character 65 No
12 STOREPHONE Character 65 No
13 SCKNDPCKNM Character 11 No
14 VSLPCKSCKS Character 3 No
15 VSLPCKSCK1 Character 3 No

 

[Olaf Doschke]

To complete Mike's idea now you gave the field names:

Insert Into MyDatabase;
Select OEL, PADR("DO NOT MAIL ",50,"*") As FULLNAME,;
DLVRYDDRSS, >>all the other field names here<<;
From MyDatabase;
Where '###' $ VSLPCKSCKS AND '###' $ VSLPCKSCK1

And you better index on the where condition expression like already said. It would be better though, if the index was more meaningful and reusable, so if '###' is not just anywhere in the field but at the start you could also check for VSLPCKSCKS = '###', which would be optimizable already with an index on the field VSLPCKSCKS, and then would also be usable to optimiza any other field = value expression, while an index on '###' $ VSLPCKSCKS only will optimize exactly that expression and not any other value $ field expression.

Bye, Olaf.

 

[Mike Lewis]

Will,

Olaf beat me to it. His code is just what I had in mind.

There's one more piece in the puzzle. In your original code, you went out of your way to insert the "do not mail" record immediately after the original record to which it relates. This new code won't do that, but (assuming it's still a requirement), you can achieve the same effect by indexing MyDatabase on a suitable combination of fields.

I would expect this code to be very much faster than the original, even if you can't optimise the '###' $ VSLPCKSCKS AND '###' $ VSLPCKSCK1 expression.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

"The DO NOT MAIL record is a QC piece that is pulled off the production line at the end of each pallet when printing the addesses."

So, as I guessed, this is a requirement for printing.

If that is so, then as I suggested above:
1. Do this in a ReadWrite Cursor or a temporary data table containing only the necessary records.
2. Don't bother trying to figure out how to INSERT a record into a specific location within the table - just APPEND records at the bottom and use an index to make them appear where you want (as Mike confirms just above).

That would be the fastest way to get things to work.

Good Luck,
JRB-Bldr

 

[wlynch]

Interesting stuff. I am working on the suggestion. To answer one question, I don't care if the inserted record is first or second in order. The index idea sounds like a winner JRB. Mike and Olaf, you two always seem to have an answer. Again, thanks to you all!

 

[JRB-Bldr]

If you were to use some cursor you might consider adding an extra field (e.g. SortFld) to the data so that you can then populate it as desired to support your Index.

Something like:

SELECT MyData.*,;
   SPACE(1) as SortFld;
   FROM MyData;
   WHERE <whatever criteria>
   INTO CURSOR LabelData READWRITE

SELECT LabelData
INDEX ON SortFld TAG SortFld

* --- Set Default SortFld Values ---
REPLACE ALL SortFld WITH '3'

* --- Set Other SortFld Values ---
REPLACE ALL SortFld WITH '1';
    FOR <some criteria>
REPLACE ALL SortFld WITH '2';
    FOR <some other criteria>

* --- Now add blank records ---
APPEND BLANK
REPLACE SortFld WITH '1',;
   < and any other blank record field values >

APPEND BLANK
REPLACE SortFld WITH '2',;
   < and any other blank record field values >

APPEND BLANK
REPLACE SortFld WITH '3',;
   < and any other blank record field values >

* --- Now to examine the results... ---
SELECT LabelData
BROWSE

Good Luck,
JRB-Bldr