asked on

Faster to create field in foxpro using sql statement or alter table

Hi Foxpro Experts,

I have a select statement which creates a table that I manipulate. There are another 20 fields in the select that I am not showing. I need to create some adjusted fields for the cost, packaging,freight,and sga. I am going to increase them by a factor in another table. The question is how to create these adjusted fields. Should I do it in the select statement below? Or should I create the table without them and then use alter table to create them? I want to do things in the most efficient way possible. Thank you very much.

select distinct;
            a_tmpf1.cost * a_tmpf1.qtyshp as cost,;
            a_tmpf1.cost * a_tmpf1.qtyshp as cgsadjusted,; && mrb 9/20/10
            a_tmpf1.qtyshp * .10 as packaging,; && mrb 10/22/04
            a_tmpf1.qtyshp * .10 as pkgadjusted,; && mrb 9/20/10
            round(a_tmpf3.mrbfreight,2) as mrbfrei1,;
            round(a_tmpf3.mrbfreight,2) as frtadjusted,; && mrb 9/20/10
            a_tmpf1.lalbrcstar * a_tmpf1.qtyshp * 5 as sga, ;
            a_tmpf1.lalbrcstar * a_tmpf1.qtyshp * 5 as sgaadjusted ;
      from a_tmpf1,;
            a_tmpf3,;
            a_tmpf9;
      into table (gcTableName);

tusharkanvinde

I think you should create them in the select statement rather than use the alter table. Also, in case you don't want a permanent table, you could create a read write cursor.

Cyril Joudieh

The fastest way is to do them in the query as above with the relationship.
The professional way in my opinion is to do the QUERY INTO CURSOR READWRITE and then run an UPDATE command.

Pavel Celba

"Thanks guys. Unfortunately, the program is written in vfp6 so I don't have cursor readwrite capability. It would be quicker into a cursor than into a table though, right, because it would be going into memory as opposed to hard disk?"

Your explanation has just two major mistakes:

1) Even the cursor is written to a disk - look at DISPLAY STATUS and/or at temporary folder contents. We may just guess what part of the cursor remains in memory. Maybe it is bigger than for a table of the same structure but who knows...

2) VFP 6 allows cursors to be readwrite:

SELECT ... FROM ... INTO CURSOR cTemp1

SELECT 0
USE DBF("cTemp1") AGAIN ALIAS cTemp
USE IN cTemp1

Now just one cursor is open in readwrite mode.

And ALTER TABLE executed after SELECT is less efficient than one simple SELECT-SQL.

mbibmdi

ASKER

Pcelba, did you try that in VFP6? I followed what you said and it wrote to the original table that I did the select on. I even tried it with closing the original file from the set command. Sorry, I only know enough to be dangerous. Don't really know a lot.

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cyril Joudieh

You can do a SELECT INTO TABLE in a temporary space and then delete this table after closing it.

SELECT ... INTO TABLE temppath\mytemptable
DO WhateverIsNeeded
USE IN mytemptable
DELETE FILE temppath\mytemptable.dbf

Olaf Doschke

Cyril,

mbibmdi IS selecting into a table right now, Pavel Celba is proposing rather to not create temp tables and dispose them yourself, as cursors autodispose themselves and are rather in memory tables than written to disc.

proof:

Create Cursor curTest (id I)
? Dbf("curTest")
? File(Dbf("curTest"))

Createing a field in the first place, selecting into a cursor with NOFILTER, then using it AGAIN makes it readwrite.

A table is the second best solution, but stay with generating the field with the query in the first place, ALTER TABLE rewrites the full table to add a new column, so that will give you the worst performance.

Bye, Olaf.

mbibmdi

ASKER

Pcelba, you mean Of course, I supposed you WERE selecting data from three tables... Right, when I only used one it didn't work. When I used your nofilter it worked. I just learned that Foxpro does not actually create cursor if the SELECT meets these three criteria: (i) it is based on a single table; (ii) it contains no calculated fields; and (iii) it is fully Rushmore optimisable.
http://www.ml-consult.co.uk/foxst-09.htm

Question regarding your original response. What is the purpose of the Use in cTemp1 statement? Aren't all the cursors going to close anyway?

Thank you very much for your insights.

mbibmdi

ASKER

CaptainCyril, yes, your way is how I am doing it now. But I thought, like Olaf, that cursors are faster because they are in memory, but now Pcelba is confusing me as to whether they are or not. He's saying they go to disk? Maybe Pcelba can comment. Is using cursor faster than using table or not?

Cyril Joudieh

If for small data always use a cursor since they most probably won't be written to disk.

If you are handling large data might as well use a table. If you are not sure then go for cursor which when large enough will be a table by itself.

In VFP 9 I always use CURSORS. I never use arrays because they are slower. Cursors always are easier since they clean themselves up.

In FPW26 I would use TABLEs or CURSORs with APPEND FROM.

In FoxPro you have to think like this:
ARRAY is very flexible as variables types and therefore slow.
CURSORS are organized types and therefore extremely fast specially when properly indexed for large data.

mbibmdi

ASKER

Thanks CaptainCyril

Olaf Doschke

cursors only are sapped to disc when neccessary, eg due insufficient RAM. With nowaday RA in the area of GB almost any cursor is hold in memory. But it's not in your control. If the cursor is written to disc, FILE(DBF()) will return .T. finally, so internally it's a file/dbf already anyway, therefore also a USE dbf("alias") works, but that's still a cursor.

In regard to why pcelba did close cTemp1 in his sample, I'm not him, but I also tend to tidy up as soon as I can, you don't need to wait until the datasession is closed that way. When querying data in a library used by other developers you might only want to add one result cursor in a method or function, not all the temporary cursors created underway.

Or with Orwell: Some cursors are more temporary than others.

Not polluting a datasession with things unneeded also makes debugging easier.

Bye, Olaf.

Olaf Doschke

I meant to say swapped to disc, not sapped.

Bye, Olaf.