index on freetable?

Hi Patrick,

I'm a little confused. Free tables can have indexes just the same as those
that are part of a DBC, although they cannot have a "Primary" index. They
can, however, have any number of Candidate indexes which will prevent
duplicates the same as a Primary index would. Please see the Index command
in Help.

As you remove and add (the Zap and Append) records to your free table the
FoxPro data engine will keep itself updated automatically. However, if
you're working with a lot of records it may be faster to Delete Tag All,
Zap, Append the data and then Index On, than to keep the index while you add
the data - you can experiment with this.

It sounds like you will be doing something like this on a daily basis:

lcOldSafety = Set("Safety")
Set Safety Off && So you won't get the confirmation message
Use MyTable Exclusive
Delete Tag All
Zap
Append From SomeData
Index On SomeField Tag MyTag1
Index On AnotherField Tag MyTag2
Set Safety &lcOldSafety
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Patrick A Tura
I have a need to link a freetable to a remote view to get the information
we need. I have observed that the downloads of the data from the server is
a bit slow. Based on my experience with these type of problems is due to
lack of index. As we need to keep this information be updated everyday we
need to keep this sql query to run on scheduling. I observed I got no
indexes on these freetables. I can probably integrate this freetable into
a database container but maybe I have to use ZAP and APPEND method in
order to keep this table updated with the latest information. But is there
a clean way to do this or simply make an index to a freetable?

Patrick A Tura

2005-02-06 15:26:32 UTC

Thanks a lot for the tips.

I think you got my description right. My freetables are created from some of
my sql queries and these tables are only made to be link to some tables from
the main dbc .

tables created from sql-select is of course not index and no primary index?

Yes, it is possible to add index manually into a table, but do you think the
next time that table will be replaced with a new update with the same
filename, do you think the index will still work?

I have not tested that yet.

/patrick

Post by Cindy Winegarden
Hi Patrick,
I'm a little confused. Free tables can have indexes just the same as those
that are part of a DBC, although they cannot have a "Primary" index. They
can, however, have any number of Candidate indexes which will prevent
duplicates the same as a Primary index would. Please see the Index command
in Help.
As you remove and add (the Zap and Append) records to your free table the
FoxPro data engine will keep itself updated automatically. However, if
you're working with a lot of records it may be faster to Delete Tag All,
Zap, Append the data and then Index On, than to keep the index while you
add the data - you can experiment with this.
lcOldSafety = Set("Safety")
Set Safety Off && So you won't get the confirmation message
Use MyTable Exclusive
Delete Tag All
Zap
Append From SomeData
Index On SomeField Tag MyTag1
Index On AnotherField Tag MyTag2
Set Safety &lcOldSafety
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Patrick A Tura
I have a need to link a freetable to a remote view to get the information
we need. I have observed that the downloads of the data from the server
is a bit slow. Based on my experience with these type of problems is due
to lack of index. As we need to keep this information be updated everyday
we need to keep this sql query to run on scheduling. I observed I got no
indexes on these freetables. I can probably integrate this freetable into
a database container but maybe I have to use ZAP and APPEND method in
order to keep this table updated with the latest information. But is
there a clean way to do this or simply make an index to a freetable?

Cindy Winegarden

2005-02-06 20:01:52 UTC

Hi Patrick,

Sounds like what you want to do is:

*-- Cursors are like temporary tables on your hard drive
*-- and VFP closes/destroys them automatically when the app ends
Select Whatever From Wherever ;
Into Cursor MyCursor

*-- MyFreeTable exists and is open and has index on MyField
*-- Index is preserved and updated automatically
Select MyFreeTable
Zap
Append From DBF("MyCursor")

While you can't have a "Primary" index you can still have indexes on key
fields. The Fox data engine will use whatever indexes are available and
appropriate to optimize queries and also joins.

Can you describe your data and how you're using it a little more? Are you
"linking" your tables to the main DBC by Set Relation, by a Join clause in a
query, or what?
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Patrick A Tura
I think you got my description right. My freetables are created from some
of my sql queries and these tables are only made to be link to some tables
from the main dbc .
tables created from sql-select is of course not index and no primary index?
Yes, it is possible to add index manually into a table, but do you think
the next time that table will be replaced with a new update with the same
filename, do you think the index will still work?

Patrick A Tura

2005-02-07 17:14:44 UTC

Hello Cindy,

I am very thankful for the extra effort you have extended to me.

The following is one of my SQL-SELECT() queries, I am running everyday on
schedule compiled into a small exe.

* BAccounts 2002.10.07

select book.client_id,KTO,fname,gname, book.sec,
book.STARTDATE,max(book.ENDATE,max(book.DATUM),max(book.NEXTDATE),sum(out);
sum(INN),sum(INN)-sum(OUT) as netto,client_status ;
from book, biodata ;
nowait ;
where book.client_id = biodata.client_id ;
and biodata.B = "B" ;
and KTO='Cust';
group by book.client_id ;
having sum(INN)-sum(OUT) <> 0 ;
INTO table BAccounts

The freetable result is "BAccounts". This will later to be joined with
another table called "clients_addresses" where
clients_addresses.client_id=BAccounts.client_id and filter with
clients_addresses.staff_id=?gcstaff_id.

When gcstaff_id is available, the result will tell how many clients assigned
to that staff_id and showing their individual balances as reflected in
sum(inn)-sum(out).

Its functioning, only it is very slow. I have realized that BAccounts has no
index. So far, I am experiementing with the following codes;

lcOldSafety = Set("Safety")
Set Safety Off && So you won't get the confirmation message
Use MyTable Exclusive
Delete Tag All
Zap
Append From SomeData
Set Safety &lcOldSafety

With a copy of BAccounts.dbf now being part of the main.dbc and with primary
index on BAccounts.client_id, its speed up.
Only with this method, I cannot update BAccounts.dbf while it is still open.
I need to open that table exclusively and most often difficult in
a multi-user environment during working hours.

I hope you understand my description.

/patrick

Post by Cindy Winegarden
Hi Patrick,
*-- Cursors are like temporary tables on your hard drive
*-- and VFP closes/destroys them automatically when the app ends
Select Whatever From Wherever ;
Into Cursor MyCursor
*-- MyFreeTable exists and is open and has index on MyField
*-- Index is preserved and updated automatically
Select MyFreeTable
Zap
Append From DBF("MyCursor")
While you can't have a "Primary" index you can still have indexes on key
fields. The Fox data engine will use whatever indexes are available and
appropriate to optimize queries and also joins.
Can you describe your data and how you're using it a little more? Are you
"linking" your tables to the main DBC by Set Relation, by a Join clause in
a query, or what?
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Patrick A Tura
I think you got my description right. My freetables are created from some
of my sql queries and these tables are only made to be link to some
tables from the main dbc .
tables created from sql-select is of course not index and no primary index?
Yes, it is possible to add index manually into a table, but do you think
the next time that table will be replaced with a new update with the same
filename, do you think the index will still work?

Anders Altberg

2005-02-09 13:41:11 UTC

Hi Patrick
Since VFP8 FoxPro follows the ANSI SQL rule that the GROUP BY clause must
list the same fields as the SELECT column list (except the group aggregates,
such as SUM(), MAX(), or COUNT(*). Ignoring this rule leads to random,
misleading results.

-Anders

Post by Patrick A Tura
Hello Cindy,
I am very thankful for the extra effort you have extended to me.
The following is one of my SQL-SELECT() queries, I am running everyday on
schedule compiled into a small exe.
* BAccounts 2002.10.07
select book.client_id,KTO,fname,gname, book.sec,

book.STARTDATE,max(book.ENDATE,max(book.DATUM),max(book.NEXTDATE),sum(out);

Post by Patrick A Tura
sum(INN),sum(INN)-sum(OUT) as netto,client_status ;
from book, biodata ;
nowait ;
where book.client_id = biodata.client_id ;
and biodata.B = "B" ;
and KTO='Cust';
group by book.client_id ;
having sum(INN)-sum(OUT) <> 0 ;
INTO table BAccounts
The freetable result is "BAccounts". This will later to be joined with
another table called "clients_addresses" where
clients_addresses.client_id=BAccounts.client_id and filter with
clients_addresses.staff_id=?gcstaff_id.
When gcstaff_id is available, the result will tell how many clients assigned
to that staff_id and showing their individual balances as reflected in
sum(inn)-sum(out).
Its functioning, only it is very slow. I have realized that BAccounts has no
index. So far, I am experiementing with the following codes;
lcOldSafety = Set("Safety")
Set Safety Off && So you won't get the confirmation message
Use MyTable Exclusive
Delete Tag All
Zap
Append From SomeData
Set Safety &lcOldSafety
With a copy of BAccounts.dbf now being part of the main.dbc and with primary
index on BAccounts.client_id, its speed up.
Only with this method, I cannot update BAccounts.dbf while it is still open.
I need to open that table exclusively and most often difficult in
a multi-user environment during working hours.
I hope you understand my description.
/patrick

Post by Patrick A Tura
I think you got my description right. My freetables are created from some
of my sql queries and these tables are only made to be link to some
tables from the main dbc .
tables created from sql-select is of course not index and no primary index?
Yes, it is possible to add index manually into a table, but do you think
the next time that table will be replaced with a new update with the same
filename, do you think the index will still work?

Patrick A Tura

2005-02-15 04:11:48 UTC

Anders,

Thanks a lot for the time and effort for giving me other way of formulation.

I learn new things from you.

/patrick

Post by Cindy Winegarden
Hi Patrick
Since VFP8 FoxPro follows the ANSI SQL rule that the GROUP BY clause must
list the same fields as the SELECT column list (except the group aggregates,
such as SUM(), MAX(), or COUNT(*). Ignoring this rule leads to random,
misleading results.
-Anders

book.STARTDATE,max(book.ENDATE,max(book.DATUM),max(book.NEXTDATE),sum(out);

assigned

Post by Patrick A Tura
to that staff_id and showing their individual balances as reflected in
sum(inn)-sum(out).
Its functioning, only it is very slow. I have realized that BAccounts has

Post by Patrick A Tura
index. So far, I am experiementing with the following codes;
lcOldSafety = Set("Safety")
Set Safety Off && So you won't get the confirmation message
Use MyTable Exclusive
Delete Tag All
Zap
Append From SomeData
Set Safety &lcOldSafety
With a copy of BAccounts.dbf now being part of the main.dbc and with

primary

Post by Patrick A Tura
index on BAccounts.client_id, its speed up.
Only with this method, I cannot update BAccounts.dbf while it is still

open.

Post by Patrick A Tura
I need to open that table exclusively and most often difficult in
a multi-user environment during working hours.
I hope you understand my description.
/patrick

you

Post by Cindy Winegarden
"linking" your tables to the main DBC by Set Relation, by a Join clause

Post by Cindy Winegarden
a query, or what?
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Patrick A Tura
I think you got my description right. My freetables are created from

some

Post by Patrick A Tura
of my sql queries and these tables are only made to be link to some
tables from the main dbc .
tables created from sql-select is of course not index and no primary index?
Yes, it is possible to add index manually into a table, but do you

think

Post by Patrick A Tura
the next time that table will be replaced with a new update with the

same

Post by Patrick A Tura
filename, do you think the index will still work?

Anders Altberg

2005-02-06 23:34:04 UTC

As long as the table isn't overwritten or deleted, the index remains and is
updated along with the data
You can empty a table with
USE Freetable IN 0
ZAP IN Freetable
followed by
INSERT INTO Freetable SELECT * FROM myview
-Anders

Post by Patrick A Tura
Thanks a lot for the tips.
I think you got my description right. My freetables are created from some of
my sql queries and these tables are only made to be link to some tables from
the main dbc .
tables created from sql-select is of course not index and no primary index?
Yes, it is possible to add index manually into a table, but do you think the
next time that table will be replaced with a new update with the same
filename, do you think the index will still work?
I have not tested that yet.
/patrick

Post by Patrick A Tura
I have a need to link a freetable to a remote view to get the information
we need. I have observed that the downloads of the data from the server
is a bit slow. Based on my experience with these type of problems is due
to lack of index. As we need to keep this information be updated everyday
we need to keep this sql query to run on scheduling. I observed I got no
indexes on these freetables. I can probably integrate this freetable into
a database container but maybe I have to use ZAP and APPEND method in
order to keep this table updated with the latest information. But is
there a clean way to do this or simply make an index to a freetable?

Patrick A Tura

2005-02-15 04:28:51 UTC

That is what I am worried, a freetable that is overwritten every now and
then even with the same filename, the index will not work.

Yes, you are right index will work with this formulation.

- USE Freetable IN 0
- ZAP IN Freetable
- INSERT INTO Freetable SELECT * FROM myview

as there is no overwritten that will take place except the data update.

That is exactly what I did but your formulation is really concentrated.
Totally new way of thinking for me.

Thanks a lot.

/patrick

Post by Anders Altberg
As long as the table isn't overwritten or deleted, the index remains and is
updated along with the data
You can empty a table with
USE Freetable IN 0
ZAP IN Freetable
followed by
INSERT INTO Freetable SELECT * FROM myview
-Anders

Post by Patrick A Tura
Thanks a lot for the tips.
I think you got my description right. My freetables are created from some

Post by Patrick A Tura
my sql queries and these tables are only made to be link to some tables

from

Post by Patrick A Tura
the main dbc .
tables created from sql-select is of course not index and no primary

index?

Post by Patrick A Tura
Yes, it is possible to add index manually into a table, but do you think

the

Post by Patrick A Tura
next time that table will be replaced with a new update with the same
filename, do you think the index will still work?
I have not tested that yet.
/patrick

Post by Cindy Winegarden
Hi Patrick,
I'm a little confused. Free tables can have indexes just the same as

those

Post by Cindy Winegarden
that are part of a DBC, although they cannot have a "Primary" index.

They

Post by Cindy Winegarden
can, however, have any number of Candidate indexes which will prevent
duplicates the same as a Primary index would. Please see the Index

command

Post by Cindy Winegarden
in Help.
As you remove and add (the Zap and Append) records to your free table

the

Post by Cindy Winegarden
FoxPro data engine will keep itself updated automatically. However, if
you're working with a lot of records it may be faster to Delete Tag All,
Zap, Append the data and then Index On, than to keep the index while you
add the data - you can experiment with this.
lcOldSafety = Set("Safety")
Set Safety Off && So you won't get the confirmation message
Use MyTable Exclusive
Delete Tag All
Zap
Append From SomeData
Index On SomeField Tag MyTag1
Index On AnotherField Tag MyTag2
Set Safety &lcOldSafety
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP

Post by Patrick A Tura
I have a need to link a freetable to a remote view to get the

information

Post by Patrick A Tura
we need. I have observed that the downloads of the data from the server
is a bit slow. Based on my experience with these type of problems is

due

Post by Patrick A Tura
to lack of index. As we need to keep this information be updated

everyday

Post by Patrick A Tura
we need to keep this sql query to run on scheduling. I observed I got

Post by Patrick A Tura
indexes on these freetables. I can probably integrate this freetable

into