SET FILTER TO vs SELECT (SQL) 1

[andreateh]

Some time we need to display set of query result in a grid. Usually we use :

method A

SELECT columns WHERE condition INTO CURSOR cursorname

But i used to :

method B

USE tableA IN SELE(1)
USE tableA AGAIN ALIAS resultset
SET FILTER .F.
...
...
&&Then user type the condition in the textbox.
SELE resulset
SET FILTER TO condition

Let's assume that the criteria or field the user want to filter out is not indexed. Just want to know which one is a better choice and faster?

 

[Mike Lewis]

Andreatech,

First, instead of this:

USE tableA AGAIN ALIAS resultset
SET FILTER .F.

it will be slightly faster to do this:

USE tableA AGAIN ALIAS resultset NODATA

As for your main question, there is not a huge difference in performance between SELELCT and SET FILTER. In my own tests, I have found SET FILTER to be slightly faster, but that might not always be the case.

What does make a very big difference is the presence or otherwise of index tags. If the fields used in the condition are indexed, both SELECT and SET FILTER will be considerably faster than if they are not. The improvement you'll get by using index tags will overshadow any gain you might get by choosing one command rather than the other.

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

 

[andreateh]

Thanks for help.

 

[craigber]

If you do a SELECT, the scroll bars on the grid will work properly. With a FILTER, they have strange behavior.



Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[andreateh]

Mike would like to ask. If my program just want to open a table for updating or add new record only. Should i open it with :

USE table1 nodata

istead of

USE table1

 

[craigber]

NODATA is for use with Views. If you don't use views, it won't gain you anything.

Also, in a message in one of the newsgroups, it was noted that using a filter across a network can sometimes bring an application to it's knees. An SQL SELECT is a better option.

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[Mike Lewis]

Craig,

NODATA is for use with Views. If you don't use views, it won't gain you anything.

Although that's essentially correct, the original question was about opening a table and immediately applying a filter. From a user interface point of view, it is better to show an empty record set before the user has established the filter criterion. NODATA is one way of doing that.

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

 

[hyderzaman1]

I Agreed With craigber

Select is much Faster then SET FILT TO Specially in Grids and List

Cause Select is capturing the data in memory or a table
but FILTER is WORK LIKE THIS

IF RECORD NO'S
1
1000
10000
ARE SELECTED IN FILTER CONDITION THEN THEY TAKE TIME TO JUMP TO RECNO 1 TO 1000 AND 10000

 

[baltman]

I agree that SET FILTER will provide awful performance with larger data sets. I always use a SELECT * FROM MyData WHERE condition

Brian

 

[10055]

SELECT create cursor name Query,
SET FILTER still read the whole and use criteria to filter the record.
If recno() > 1 millions, and at the top you press pageup button, SET FILTER needs about 16 seconds or more for setfocus() because it re-FILTER-ing. With SELECT no problem because it use cursor Query.

Clipperist goes to Fox.

 

[Mike Lewis]

I hate to disagree with so many of you, but the issue is not whether you use SET FILTER or SELECT. The real factor affecting performance is whether the condition in question can be optimised -- in other words, whether there are indexes on the relevant fields.

The difference in performance between SET FILTER and SELECT is negligible compared to the difference that indexes will bring.

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