Into Table or Into Cursor

Is one "really" faster than the other?

I got into the habit of always using

...INTO TABLE TableName...

instead of

...INTO CURSOR CursorName...

for all SQL queries because I had problems with indexing, record
numbers, etc. and reliability in the older versions.

I've done no real testing, but one seems to work as fast as the other
in VFP9, even with large query results. Is this because cpu speed,
hard drive speed, or does Foxpro really buffer the result anyway and
write it to the hard drive when it gets the chance? Or is it my
imagination that one is as fast as the other?

Steve Meyerson

Olaf
Sun Jan 14 11:22:45 CST 2007

Hi Steve,

INTO Table surely is written to disk, at least
whhn you close the new table with USE. Before
that vfp could buffer it, which is of course de-
termined by the buffering setting for workarea 0,
all new cursors or tables. But as a table could be
visible to other clients, I think it will be written
to disk immediately anyway.

INTO Cursor stays in memory, and is not
saved to disk unless it get's too large.

If you want long field names etc. INTO TABLE
also needs the DATABASE clause. If you use a local
database for that, it might work. But if you
want the result only temporarily, you'd need
to DROP the table, perhaps even DELETE the
DATABASE. Much overhead involved.

Indexing works reliable for me since VFP6
for cursors, VFP6 just needed the USE AGAIN
trick to create a readwrite cursor.

But altering Cursors is not recommended (see help
on CREATE CURSOR). Nevertheless, there are
ways to create the cursor as needed in the first place,
or create a new one and append the data of the old
one. That is not taking more time than ALTER
TABLE, as ALTER TABLE does also not really
alter the existing file, but will write out a new one.

Views, SPT, CursorAdapter, all these result in
cursors, so I think it's a waste to not use them and
their ease of vanishing when not needed anymore
plus their nature of in-memory tables.

Bye, Olaf.

Man-wai
Sun Jan 14 20:10:03 CST 2007

> I got into the habit of always using
> ...INTO TABLE TableName...
> instead of
> ...INTO CURSOR CursorName...
> for all SQL queries because I had problems with indexing, record
> numbers, etc. and reliability in the older versions.

One advantage of using a cursor is that Foxpro will automatically delete
the files of a cursor automatically when you close the cursor. if you
use a table, you will have to do it yourself. With disk caching, there
is not much difference between the two.

You could build indexes for a cursor. Foxpro would delete them when you
close the cursor.

In older version of Foxpro, you need to:

USE DBF("cursor_alias") AGAIN SHARED ALIAS new_alias

to make a cursor writable.

--
iTech Consulting Co., Ltd.
Specialized in providing ePOS solutions
Website: http://www.itech.com.hk (IE only)
Tel: (852) 2325 3883 Fax: (852)2325 8288

Gene
Sun Jan 14 20:59:13 CST 2007

"Olaf Doschke"
<b2xhZi5kb3NjaGtlQHNldG1pY3MuZGU.strconv.14@t-online.de> wrote:

[snip]

>But altering Cursors is not recommended (see help
>on CREATE CURSOR). Nevertheless, there are

This caught my eye. For VFP 9, the caution is under ALTER TABLE
(Remarks, par. 9). The text is:

"ALTER TABLE might not produce consistent results when used with
Visual FoxPro cursors created by the CREATE CURSOR command. In
particular, you can create a Visual FoxPro cursor with features, such
as long field names, that are normally available only with tables that
are part of a database container. ALTER TABLE saves a temporary copy
of the cursor, so the rules that apply to free tables also apply, and
any features requiring database support are lost or changed in an
unpredictable manner. Therefore, you should generally avoid using
ALTER TABLE with Visual FoxPro cursors unless you have tested and
understood the outcome."

It does sound like a kludgy area.

[snip]

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Olaf
Mon Jan 15 08:05:51 CST 2007

>For VFP 9, the caution is under ALTER TABLE
Yes, it's there for other versions too. I was wrong about
CREATE CURSOR.

Should have known that, cause there was another thread,
where that mattered too.

Bye, Olaf.