Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-21
13
Medium Priority
?
649 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 43

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 43

Accepted Solution

by:
pcelba earned 1000 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
 
LVL 30

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 30

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 30

Expert Comment

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

Bye, Olaf.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Let's take a look into the basics of ransomware—how it spreads, how it can hurt us, and why a disaster recovery plan is important.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

604 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