Solved

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

Posted on 2010-09-21
13
627 Views
Last Modified: 2012-05-10
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
Comment
Question by:mbibmdi
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 33726172
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
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 33726181
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
 
LVL 41

Expert Comment

by:pcelba
ID: 33729510
"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
 

Author Comment

by:mbibmdi
ID: 33729700
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
 
LVL 41

Accepted Solution

by:
pcelba earned 250 total points
ID: 33729897
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
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 33731414
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33732280
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
 

Author Comment

by:mbibmdi
ID: 33735076
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
 

Author Comment

by:mbibmdi
ID: 33735196
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
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 33736649
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
 

Author Comment

by:mbibmdi
ID: 33736702
Thanks CaptainCyril
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33739261
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 33739278
I meant to say swapped to disc, not sapped.

Bye, Olaf.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Moving applications to the cloud or switching services to cloud-based ones, is a stressful job.  Here's how you can make it easier.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now