Board index » delphi » TQuery's Filtered property against SQL

TQuery's Filtered property against SQL

Hi,
I have TQuery with TDBGrid linked to table of about 4,000 records.
In SQL I use "WHERE some_Item = some_Value" clausule. Listing in result set
is too slow. Only changing TQuery.Filtered property to "true" (property Filt
er is empty) results in growing speed of listing min. 20 times.

Can somebody tell me why?

Is it correct set "Filtered" property to "true" without filled "Filter" prop
erty?

Sorry for my english.

Thanks,
M.Jurik

 

Re:TQuery's Filtered property against SQL


I ran a test using the parameters that you described and did not discover
any performance benefits within the query itself.

Are you saying that the grid "scrolls" faster?

John

Re:TQuery's Filtered property against SQL


I analysed it again and found that problem is, when RequestLive is set to
TRUE.
I use Paradox table with structure like:

Item1 : smallint
Item2 : smallint
Item3 : smallint
Item4 : alpha[10]
Item5 : alpha[255]
Item6 : alpha[255];
ItemX ...ItemY (about 20 next items, mainly integers)

Primary index use Item1,Item2,Item3,Item4.
Number of records: about 4000

In SQL I have :
select * from mytable
where (Item1 = value1)and(Item2=value2)
order by Item1,Item2,Item3,Item4

I tested it with all records with same value of  Item1 and Item2.
Grid scrolls too slow - by keyboard.
But if I omit "where ..." in SQL or IF I SET FILTERED propery to true,
grid scrolls fast (Filter = '').

Setting Filtered to TRUE changes state of scrollbar - not folow the cursor.

I have Delphi5 PRO, PIII600MHz,256MB RAM.

M.Jurik

"John Easley" <jceas...@earthlink.net> p1e v diskusnm p?spvku
news:3ad5f5bc_1@dnews...

Quote
> I ran a test using the parameters that you described and did not discover
> any performance benefits within the query itself.

> Are you saying that the grid "scrolls" faster?

> John

Re:TQuery's Filtered property against SQL


Quote
>I tested it with all records with same value of  Item1 and Item2.
>Grid scrolls too slow - by keyboard.
>But if I omit "where ..." in SQL or IF I SET FILTERED propery to true,
>grid scrolls fast (Filter = '').

>Setting Filtered to TRUE changes state of scrollbar - not folow the cursor.

When request live is set to true the Query is basically filtering on your data
when you have a where that can not be satisfied by an index.   From your
description of the scrollbar behavior it appears that setting filtered to True
dispenses with the query figuring out the number of records returned which
improves performance.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:TQuery's Filtered property against SQL


I have done some next testing and I have found:

In DBGrids.Pas, TCustomDBGrid.UpdateScrollBar use
FDatalink.DataSet.RecordCount to get
curent record count for repainting scrollbar. And this is done always on
scroling by one row, except of Filtered is set to true. If RequestLive is
true, this function is too slow - it uses DbiGetExactRecordCount.  With
RequestLive set to false the scrolling is fast, but reopening query get much
more time, then with request live set to true. And on big table this is
markant.

So, my solution is to change TCustomDBGrid.UpdateScrollBar. I tried this:

procedure TCustomDBGrid.UpdateScrollBar;
function GetMyRecordCount: Integer;
begin
if  DbiGetRecordCount(TBDEDataset(FDatalink.DataSet).Handle, Result) <>
DBIERR_NONE  then Result := -1;
end;

var
  SIOld, SINew: TScrollInfo;
begin
...{original code}
        SINew.nMax := Integer(DWORD(GetMyRecordCount) + SINew.nPage - 1);
...{original code}
end;

Is it correct? Or exist any other solution?(without setting filtered to
true)

The diference are visible only with big table, big items in table and with
"where" in SQL.

M.Jurik

Re:TQuery's Filtered property against SQL


Quote
>Is it correct? Or exist any other solution?(without setting filtered to
>true)

I don't advise changing the code of existing controls.  Subclass them and
override the behavior in the subclassed control if you really want to change it.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads