Visual FoxPro SQL Queries - Which way is the quickest?

Author

Which way is the quickest?

Rotsj

2005-09-19, 11:24 am

Hi,

i have to insert records in a table from another table. What is the quickest
way while looping through the original file, the append / replace method or
the sql - insert method?

Thanks!

Rotsj

Lee Mitchell

2005-09-19, 8:25 pm

Hi Rotsj:

The INSERT-SQL command is faster than APPEND BLANK and REPLACE. Also, you
can use SYS(305) to improve performance even more. See

http://support.microsoft.com/defaul...KB;EN-US;176483

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 HAS ARRIVED!! --*
Read about all the new features of VFP9 here:
http://msdn.microsoft.com/vfoxpro/

*--Purchase VFP 9.0 here:
http://www.microsoft.com/PRODUCTS/i...cid=54787e64-52
69-4500-8bf2-3f06689f4ab3&type=ovr

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/gp/lifeselectindex
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

>Hi,

>i have to insert records in a table from another table. What is the
quickest
>way while looping through the original file, the append / replace method or
>the sql - insert method?

>Thanks!

>Rotsj

Cindy Winegarden

2005-09-19, 8:25 pm

Hi Rotsj,

To go a little further with what Lee said, append/replace has to update
indexes twice where Insert updates them only once. If you've got a lot of
records and you can have exclusive use of the file, removing the indexes
from the target table before the Inserts and then indexing again after that
is your best bet.

Is there a way you can SQL Select the records you want (i.e. a way to
identify them) and then use Append From to the target table?

By the way, for "which is faster" type questions you can always take a
reading of the time [Seconds()] before and after and then try 10,000 of
whatever you're doing and compare the times.

Create Cursor Test (Field1 I)
Index On Field1 Tag Field1 && Optional
Start1 = Seconds()
For i = 1 To 10000
Insert Into Test Values (i)
EndFor
End1 = Seconds()
? End1 - Start1
Start2 = Seconds()
For i = 1 To 10000
Append Blank
Replace Field1 With i
EndFor
End2 = Seconds()
? End2 - Start2

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

"Rotsj" <r.knipscheer@home.nl> wrote in message
news:dgmnsf$ssi$1@ne
ws6.zwoll1.ov.home.nl...
> Hi,
>
> i have to insert records in a table from another table. What is the
> quickest
> way while looping through the original file, the append / replace method
> or
> the sql - insert method?
>
> Thanks!
>
> Rotsj
>
>

Anders

2005-09-19, 8:25 pm

INSERT INTO Table1 (<column list> ) SELECT <column list> FROM Table2 [WHERE
clause]

If the tables have matching column order and cardinality its even simpler
INSERT INTO A SELECT * FROM B

The WHERE clause can also be used to precvent entering duplicates.
Note: APPEND matches column names, not column order.
-Anders

"Rotsj" <r.knipscheer@home.nl> skrev i meddelandet
news:dgmnsf$ssi$1@ne
ws6.zwoll1.ov.home.nl...
> Hi,
>
> i have to insert records in a table from another table. What is the
> quickest
> way while looping through the original file, the append / replace method
> or
> the sql - insert method?
>
> Thanks!
>
> Rotsj
>
>

Paul

2005-09-20, 7:24 am

Cindy Winegarden wrote:
> Hi Rotsj,
>
> To go a little further with what Lee said, append/replace has to update
> indexes twice where Insert updates them only once. If you've got a lot of
> records and you can have exclusive use of the file, removing the indexes
> from the target table before the Inserts and then indexing again after that
> is your best bet.
>
> Is there a way you can SQL Select the records you want (i.e. a way to
> identify them) and then use Append From to the target table?
>
> By the way, for "which is faster" type questions you can always take a
> reading of the time [Seconds()] before and after and then try 10,000 of
> whatever you're doing and compare the times.

When i've done this in the past I found that SCATTER MEMVAR, APPEND
BLANK and GATHER MEMVAR the quickest, but only by a fraction. Probably
depends on indices, number of fields etc.

--
Paul

Sponsored Links