Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 To: Russell Hill
  Where is Russell Hill?
 Sydney
 Australia
 Russell Hill
 Tags
Subject: RE: NOFILTER?
Thread ID: 310763 Message ID: 310890 # Views: 39 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: General VFP Topics
Date: Monday, June 27, 2011 2:23:20 AM         
   


> Hi all,
>
> I'm running some SQL SELECTs with GROUP BY where I also want to sum the COUNTs and SUMs and INSERT a summation record at the bottom of the result cursor.
>
> This is easy enough and the need for READWRITE is intuitively obvious.
>
> I notice that many people use READWRITE NOFILTER but is this (a) required or (b) useful or (c) just habit?
>
> What does READWRITE NOFILTER do for me in this situation that READWRITE alone would not?
>
> Is the decision to use READWRITE or READWRITE NOFILTER one that should be made on a case by case basis or is there an across-the-board best practice with this decision?
>
> TIA.
>
> Russell.

Ahh... the issue of NOFILTER usage or not. :-)

I have made an article last time about that but it goes down with junblogs server, LOL! I may recreate it again on blogspot later. In the meantime, allow me to share again my understanding of that issue here.

I cannot think of a situation where I will need both READWRITE + NOFILTER clause. I will either use NOFILTER or READWRITE.

1. When do we need READWRITE? When we want the cursor to allow later changes. Once I use READWRITE clause, NOFILTER clause automatically goes down the drain.


2. When do we need NOFILTER? When a true cursor (not a filtered result) is needed.

So it is dependent on that need when to use or not that NOFILTER clause, not on any other factor.

What are those cases where a true cursor will arise even if NOFILTER clause is not used?

a. If READWRITE clause is used

b. If SQL SELECT involves two or more tables

c. When GROUP BY clause is used

d. When calculated columns are done like DISTINCT, TOP, PERCENT and Aggregate functions are used.

e. When a result of SQL SELECT does not result to fully optimizable cursor.

f. When additional field(s) are inserted on an SQL SELECT coming from a single table like:

SELECT *, .F. as whatever from tablea INTO CURSOR junk && logical field whatever is inserted



With the above cases, NOFILTER is not necessary because the result of SQL SELECT will always not be just a filtered result of the source. Result will always return a true cursor so usage of NOFILTER there is actually just a redundant. Will it harm the SQL SELECT if you put NOFILTER clause despite that those generate true cursors? Nope!


When is NOFILTER clause really absolutely needed?

a. When SQL SELECT is performed on a single table on its original fields (no additional field is inserted).

b. When you require a subsequent queries for that cursor.


The only setback with using a NOFILTER clause is since it is creating a true cursor, then it is a little bit slower because that necessitates data transfer as opposed to having just a filtered result.

RECAP: On letters a to f far above, NOFILTER is not necessary as the result will always give a true cursor. On letters a to b on immediate above, it is needed to be placed otherwise you will have just a filtered result.

I hope this helps it made clearer.

Jun Tangunan

ENTIRE THREAD

NOFILTER? Posted by Russell Hill @ 6/24/2011 7:57:00 AM
RE: NOFILTER? Posted by Borislav Borissov @ 6/24/2011 8:18:00 AM
RE: NOFILTER? Posted by Russell Hill @ 6/24/2011 8:29:57 AM
RE: NOFILTER? Posted by Borislav Borissov @ 6/24/2011 8:37:07 AM
RE: NOFILTER? Posted by Russell Hill @ 6/24/2011 11:33:38 AM
RE: NOFILTER? Posted by Stefan Wuebbe @ 6/24/2011 12:26:54 PM
RE: NOFILTER? Posted by Russell Hill @ 6/26/2011 12:36:06 AM
RE: NOFILTER? Posted by Mike Yearwood @ 6/25/2011 7:02:04 PM
RE: NOFILTER? Posted by Tamar Granor @ 6/25/2011 7:18:27 PM
RE: NOFILTER? Posted by Mike Yearwood @ 6/25/2011 7:45:47 PM
RE: NOFILTER? Posted by Tom Saddul @ 6/27/2011 5:49:34 AM
RE: NOFILTER? Posted by Samir H. @ 6/27/2011 6:05:59 AM
RE: NOFILTER? Posted by Tom Saddul @ 6/27/2011 1:19:56 PM
RE: NOFILTER? Posted by Russell Hill @ 6/26/2011 12:38:34 AM
RE: NOFILTER? Posted by Tom Saddul @ 6/26/2011 3:40:20 AM
RE: NOFILTER? Posted by Russell Hill @ 6/26/2011 1:46:36 PM
RE: NOFILTER? Posted by Samir H. @ 6/26/2011 2:35:17 PM
RE: NOFILTER? Posted by Russell Hill @ 6/26/2011 11:10:49 PM
RE: NOFILTER? Posted by Jun Tangunan @ 6/27/2011 2:23:20 AM
RE: NOFILTER? Posted by Mike Yearwood @ 6/27/2011 4:06:05 AM
RE: NOFILTER? Posted by Jun Tangunan @ 6/27/2011 5:24:15 AM
RE: NOFILTER? Posted by Mike Yearwood @ 6/27/2011 9:49:47 PM
RE: NOFILTER? Posted by Mike Yearwood @ 6/27/2011 9:53:19 PM
RE: NOFILTER? Posted by Jun Tangunan @ 6/28/2011 1:31:56 AM
RE: NOFILTER? Posted by Mike Yearwood @ 6/28/2011 2:17:07 AM
RE: NOFILTER? Posted by Jun Tangunan @ 6/28/2011 2:57:26 AM
RE: NOFILTER? Posted by Mike Yearwood @ 6/28/2011 3:43:17 PM