Confused about RECCOUNT 4

[jjjt]

Hi,

I have written a small prg using Fox 7 where I use the following SELECT statement:

SELECT *;
FROM product_transaction_header;
WHERE product_transaction_header.pth_unit_qty_id = 0;
INTO CURSOR cur_trans_w_out_puq

I then do

RECCOUNT("cur_trans_w_out_puq")

to find out how many records are in the cursor, expecting to get 234, which is the number of transactions with a 'pth_unit_qty_id' of zero. However, RECCOUNT actually returns 647 which is the total number of records in the 'product_transaction_header' table.

If I browse the cursor, the only records I can see are the 234 that I am expecting.

I have used RECCOUNT before, apparently without any problems, but now I am worrying that there is something fundamental missing from my understanding. I've looked in the Help and elsewhere on this site but I can't find anything to explain the results I am getting.

Thanks in advance for any advice,

Jake

 

[Mike Lewis]

Jake,

Try either of the following:

- Add NOFILTER to the SELECT statement, or:

- Use _TALLY in place of RECCOUNT().

The reason is to do with the way that VFP optimises this particular kind of SELECT. Essentially, it doesn't really create a cursor; it merely applies a filter to the original table, under a different alias. Adding NOFILTER forces it not to do this.

You will probably find that the second of the above options is overall faster than the first.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[csr]

Velly intlesting ...


Don

 

[skoko]

Can you use:

GO BOTTOM
?RECNO()

 

[jjjt]

No, I tried using

GO BOTTOM
?RECNO()

and you get the same result i.e. you get a count of all of the records in "product_transaction_header". I think that the same logic that Mike has noted will apply when using GO BOTTOM

Jake

 

[dbMark]

Mike, I had assumed that the cursor was always a separate table from the original. Wow. Learn something new every day, don't we?

MS oblique explanation:

When using the CURSOR clause in the Destination parameter, you can now use NOFILTER to create a cursor that can be used in subsequent queries. In previous versions of Visual FoxPro, you needed to include an extra constant or expression as a filter. For example, adding a logical true as a filter expression created a query that could be used in subsequent queries:
SELECT *, .T. FROM customers INTO CURSOR myquery
However, including NOFILTER can reduce query performance because a temporary table is created on disk. The temporary table is deleted from disk when the cursor is closed.

 

[bobmpalmer]

Hmm very interesting Mr Lewis have a start from me. That is very useful.

Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[Mike Lewis]

dbMark, Bob, others ...

Well, I always thought this behaviour was better known. Glad I've been able to enlighten some of you.

Note that the filtering which I described doesn't always happen. Basically, VFP creates a filter (as opposed to a new cursor) in the following conditions:

- The query does not include calculated fields, and

- The query does not include a join, and

- The WHERE clause (if any) is fully Rushmore-optimisable.

It is in those cases that you can force the creation of a cursor by adding NOFILTER. But, as the quote from dbMark noted, this can reduce performance.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[jjjt]

Thanks, Mike, you've just answered what was going to be my next question - even more reason for the star I managed to give you before the site just crashed on me - not sure if it's a problem my end or Tek Tips'.

Jake

 

[KoenPiller]

Hi Jake,

are you sure you did not reopen your [col blue]product_transaction_header.dbf [/col]in between? Despite the remarks made by Mike I dont seem to be able to reproduce your situation. My reccount() gives eaxact numbers to the total of the cursor. Please review carefully.

Koen

 

[medic]

I have used VFP3, VFP5, VFP6 and VFP8. I have never encountered such record count problem. Could it be a VFP7 issue?

 

[DSummZZZ]

Could it be a VFP7 issue?

I have used all those versions, and 2.x as well. It happens in all of them. So be aware. (Beware?)
The key here is the type of query executed. As Mike mentioned, there are certain filter conditions, where conditions and even UDF conditions where it may or may not happen. You will even get bogus numbers using reccount() on tables where there are deleted records.
The safest thing to do when using a cursor, is to use _TALLY. That system variable keeps track of all records processed by a particular COUNT, DELETE, PACK, RECALL, INDEX, etc. process, as well as queries.
Even if you use a table instead of a query, you will still get an accurate count.

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[EzLogic]

This will work:

After your sql statement:

select cur_trans_w_out_puq
?reccount()



Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[medic]

That's very strange.
I could not reproduce that RECCOUNT() problem (just like KoenPiller), no matter how I tried. Could it be that somewhere in your code selects the source table as the current work area?

 

[jjjt]

The full code in the prg is:

*Open table
USE "O:\Data\Orderwise\Product_transaction_header.dbf" IN 0 SHARED AGAIN

* Some transaction headers do not have purchase unit quantity ids. If the product is linked to a single
* puq, then we can fill this in. If it is not, then we can't
SELECT *;
FROM product_transaction_header;
WHERE product_transaction_header.pth_unit_qty_id = 0;
INTO CURSOR cur_trans_w_out_puq

z_recs_in_cursor = RECCOUNT("cur_trans_w_out_puq")


'z_recs_in_cursor' gets set to the number of records in the 'product_transaction_header' records and not the number of records in the cursor.

Ali, I have always thought that since you specify the cursor name when you use RECCOUNT, it would not matter if another table was selected at the time. However, I did try explicitly selecting 'cur_trans_w_out_puq' before posting the question and I get exactly the same result i.e. the number of records in the 'product_transaction_header'.

My feeling is that Mike and Dave are spot on on this - my query matches all the criteria that Mike specifies:

- The query does not include calculated fields, and

- The query does not include a join, and

- The WHERE clause (if any) is fully Rushmore-optimisable.

This would also explain why I have not noticed this behaviour before. I use RECCOUNT() all over the place but almost every query I do will include a join. If I understand Mike correctly, RECCOUNT() on a cursor created by a query with a join would return the correct number of records.

Jake

 

[jjjt]

Just as a point of interest, as Mike has suggested, adding NOFILTER will force VFP not to use a filter to get the results of the SELECT statement. Similarly, if you are creating a READWRITE cursor, this will preclude the use of a filter.

Jake

 

[Mike Lewis]

Medic and KoenPillar,

Possibly the reason that you can't reproduce this behaviour is that you don't have an index on the field being tested in the WHERE clause. If that's the case, the query is not Rushmore-optimisable, and therefore VFP is creating a real cursor rather than a filter.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[medic]

You're right, Mike. It was the index.
And it happens when all the following conditions are met:
[tt]
1) The field specified in the WHERE clause is an index key
2) The index on the field is not in combination with other fields
3) The indexed field is the only WHERE clause entry
[/tt]
Now I realized why I haven't encountered this problem -- I don't use the SELECT command in such condition.
Instead, I've used SEEK, because I knew it is a lot faster than creating a new cursor. And I wasn't aware that Rushmore technology modifies the SELECT behaviour by not creating a cursor in such condition.
Another star for you, Mike.

 

[KoenPiller]

Mike,

very very good, although I agree with Medic, in such a case to use SEEK is definitely smarter than SELECT.
A star to you.

Koen