Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

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);
0
mbibmdi
Asked:
mbibmdi
  • 4
  • 3
  • 3
  • +2
1 Solution
 
tusharkanvindeCommented:
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.
0
 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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.
0
 
pcelbaCommented:
"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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
mbibmdiAuthor Commented:
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.
0
 
pcelbaCommented:
Of course, I supposed you are selecting data from three tables...

If you need to avoid the original table reusing then you have to use NOFILTER keyword:

SELECT ... FROM ... INTO CURSOR cTemp1 NOFILTER

If you add some calculated column into the SELECT column list then NOFILTER is not necessary.
0
 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
mbibmdiAuthor Commented:
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.  
0
 
mbibmdiAuthor Commented:
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?
0
 
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
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.
0
 
mbibmdiAuthor Commented:
Thanks CaptainCyril
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.

0
 
Olaf DoschkeSoftware DeveloperCommented:
I meant to say swapped to disc, not sapped.

Bye, Olaf.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now