which is faster: filter or modify SQL

[inageib]

Hi,
I want to ask about which has better performance for dataset components:

set a filter and activate it or close the dataset supply parameter and open it again ?

Thanks

[AlexP]

hello,

If you use the Filter property, data will be filtered locally, i.e. a filter condition will be applied to the data already been loaded to the DataSet. When using the FilterSQL property, a WHERE section will be formed by the entered data and the query will be executed again, this will occur when using parameters in the WHERE section as well. If all the data is loaded to the DataSet, then using a local filter with the help of the Filter property will be faster. If not all the data is loaded to the DataSet, or it is being modified all the time at the server, then the second way will be the right one.

[inageib]

thanks AlexP

[inageib]

I added code like that on onshow for a form contain a dbgrid

Code: Select all

tbl1.Filtered := false;
tbl1.FilterSQL := 'pk_ID = :p1';
tbl1.ParamByName('p1').AsInteger := tbl2.FieldByName('pk_ID').AsInteger;
tbl1.Filtered := true;

the problem when the form open nothing appear until I manually refresh the dataset. does using filter require to refresh manually ?

Thanks

[AlexP]

Hello,

The Filtered property turns on and off the local filter set in the Filter property:

Code: Select all

tbl1.Filtered := false;
tbl1.Filter := 'pk_ID = ' + tbl2.FieldByName('pk_ID').AsString;
tbl1.Filtered := true; // the filter will be applied.

When setting a filter in the FilterSQL property, the filter is applied immediately:

Code: Select all

tbl1.FilterSQL := 'deptno = ' + tbl2.FieldByName('pk_ID').AsString; // the filter will be applied.

Parameters are not supported in these properties.
If you want to use parameters, you should use the AddWhere method:

Code: Select all

tbl1.AddWhere('pk_ID = :pk_ID');
tbl1.ParamByName('pk_ID').AsInteger := tbl2.FieldByName('pk_ID').AsInteger; 
tbl1.Open;

[inageib]

Many tanks AlexP