Page 1 of 1
which is faster: filter or modify SQL
Posted: Mon 05 Nov 2012 02:57
by 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
Re: which is faster: filter or modify SQL
Posted: Mon 05 Nov 2012 12:47
by 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.
Re: which is faster: filter or modify SQL
Posted: Mon 05 Nov 2012 13:23
by inageib
thanks AlexP
Re: which is faster: filter or modify SQL
Posted: Mon 05 Nov 2012 15:43
by 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
Re: which is faster: filter or modify SQL
Posted: Tue 06 Nov 2012 12:47
by 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;
Re: which is faster: filter or modify SQL
Posted: Fri 09 Nov 2012 20:46
by inageib
Many tanks AlexP