Slow APPEND FROM How can I speed it up ? 3

[FoxEgg]

FPD2.6

I have a large database... 100 MB
I want to copy a set of entries to a new temporary database...

But it is very slow (15 seconds or more)

USE 1
COPY STRUCTURE TO QUOTE
USE QUOTE TO 10
SELECT 10
APPEND FROM 1 FOR FNUM = FILE_NUM

Is there any way that I can speed it up ?

Thanks for any advice

Fox Egg

 

[JRB-Bldr]

I'm not sure if it would speed things up noticeably or not, but why aren't you using a SQL Query to accomplish this?

Something like:

USE MyTable in 0
SELECT MyTable  && Reference by Alias instead of workspace

SELECT *;
  FROM MyTable;
  WHERE FNum = File_num;
  NOCONSOLE;
  INTO TABLE Quote

Try it and see if it is any faster.

Good Luck,
JRB-Bldr

 

[cricket]

alternatively you can use COPY TO command.

USE Mytable
if you have the table indexed on Fnum, select that index.

COPY TO mewtable FOR FNum = File_num

 

[Olaf Doschke]

The thing that would speed up all of those methods is an index on Fnum. Don't you have it on the original table?

Bye, Olaf.

 

[FoxEgg]

Thanks to you both for the quick riposte(s).

JRB... I have used your code and it seems to speed things up significantly on MY computer which hosts the large database.

First I wrote a single line set up code....

!erase quote.dbf

to remove any chance that there might be a spare copy of Quote.dbf lying around.

When I run your code on my computer..(see code box below) . it seems to go well.. no warnings and fast execution.


BUT... on a networked computer ... It asks me... "Do I want to overwrite Quote.dbf ?" and if I say 'yes' it crashes on that SQL SELECT CODE... File access denied ! THAT IS CRASH AND THEN NOTHING HAPPENS

If I say NO... it seems to work ... but when the next line of code executes ...

Append from quote

It CRASHES again with a File Access Denied error.

Do I have to have an alias for my large database named 1.dbf

*****
Sorry Cricket I havent tried your code yet... perhaps tonight.
******

This is the code that I have installed .. My 100M Database is called 1.dbf

SELECT *;
  FROM 1;
  WHERE FNum = File_num;
  NOCONSOLE;
  INTO TABLE Quote

FoxEgg

 

[JRB-Bldr]

First - "Do I want to overwrite Quote.dbf ?"

If you get this error on your networked computer, then you have not successfully erased/deleted the Quota.dbf prior to running the SQL Query command.

And since you get other errors associated with this, you might look into issues with user Permissions granted to those running the code in regards to the directory where Quote.dbf 'lives'.

Rather than merely referencing Quote.dbf, you might want to put in a full path description.

USE MyTable in 0

m.Result = "C:\Temp\Quote.dbf"
DELETE (m.Result)
SELECT *;
  FROM MyTable;
  WHERE FNum = File_num;
  NOCONSOLE;
  INTO TABLE (m.Result)

Next - Append from quote

I assume that one the table Quote.dbf is built and populated you use the records somewhere else?

One thought is to get those records directly into the 'somewhere else' without needing to go through the effort of putting them into Quote.dbf in the first place.

When you get the error File Access Denied it is a clear indication that something has the file 'tied up'. Either another user or another process.

When I encounter these type of problems I use a tool called "WhoHasNT" (

http://www.gadgetfactory.com/whohasnt.shtml

) which enables me to see the file and who is using it.

As to use of Reference by Alias...
When you USE a table, it automatically acquires an Alias which is the table name unless that alias is already in use.

USE MyTable IN 0  && 'In 0' allows FP to auto-assign workspace
SELECT MyTable  && No need to reference by workspace number

I would recommend not naming your 100MB table 1.dbf. It will cause FP some degree of 'confusion' if you should also want to open another table into workspace 1.

Your 100MB table might be opened into workspace #1, but it should have a filename (example: MyTable.dbf). The Alias would not be '1', but instead the filename. If you are using FPW issue a ACTIVATE WINDOW VIEW and you can visually watch this occur.

Good Luck,
JRB-Bldr

 

[FoxEgg]

Thank you to all for the advice... I had bit of time to work on it today.

Interesting findings

1. To Cricket and Olaf.. you are spot on...By indexing the database on the correct field (FILE_NUM) it sped the whole thing up greatly... So it was dumb for me to miss it and star material for spotting it. Thanks.

2. I am still trying to work on the "File Access Denied" problem... I am a little nervous fiddling with the database name... esp as the app has been fine for 16 years.

(Another version of old dogs new tricks)

I will keep trying

JF

 

[FoxEgg]

Wow !.... As I mentioned I like the elegance of the SELECT, but with the "File Access Denied" problem... I tried indexing PLUS Cricket's solution of

COPY TO my_new_table FOR FNum = File_num

and it works on all networked computers and goes like an absolute race horse...

My problem is solved.

Thanks to all

Fox Egg

Sorry Cricket.. I tried to give you another star, but was beaten by the system.

 

[Olaf Doschke]

Hi Fox Egg,

great you got it working.
I gave Cricket a star on behalf of you.

Bye, Olaf.