BUG ? searching in dbgrid with indexed column is not logarithmic ?
lazarus / 16-Databases

----------------------------------------------------------------------
jimbill · 2015-08-30 00:01

Hello Everyone,

I cant speak very well

Lazarus is very good tool.
Congratulations to the developers. And so forth

I would like to announce an error.

If i have a dbgrid connect with datasource connect with ztable (zeos
component)
and I set in the object inspector (of ztable) IndexFieldNames to the
database table column which is indexed
and i have for exemaple 100 000 elments in the table and I want to
search in this table (for one
field) I see a very slow linear search not logaritmic.

Is there implemented indexed searching in dbgrid or not?

seeking in this way: stroke of each letter do searching (for name)

 DBGrid1.DataSource.DataSet.Locate('product',Edit1.Text,[LoPartialKey,
loCaseInsensitive]);


sqlite3
ZEOSDBO-7.1.4-stable

windows 7/32 bit
lazarus 1.4.2  i386-win32-win32/win64


Any help welcome .

Thanks,
Jimbill

----------------------------------------------------------------------
derek.john.evans · 2015-08-30 07:03

Wouldn't that be a TDataSet issue, ie: ZTable?

----------------------------------------------------------------------
rvk · 2015-08-30 09:28

Yes, this has nothing to do with tdbgid but how you're using ztable.

You say you have set IndexFieldNames to the indexed field. Is that
field "product"? If not than that could be your problem. You are using
locate() on field "product" to locate a record so you should have an
index on field "product" in your database. Is there?

----------------------------------------------------------------------
bylaardt · 2015-08-31 01:05

your "order by" from tdataset match with the "dataset.locate" field?

if not, find your product ID with another select and locate with the
result (order by field) of the second select.

----------------------------------------------------------------------
LacaK · 2015-08-31 05:49

I am not a expert for Zeos components, but I guess, that Locate is
performed on local buffered dataset stored on client side. So it has
nothing with index defined on table on server side.
(this index is not useable by Zeos, ZTable)

----------------------------------------------------------------------
miab3 · 2015-08-31 13:27

You are using a local dataset.
To accelerate locate set SortedFields on 'product' or use Query as
others suggest.

Michal

----------------------------------------------------------------------
miab3 · 2015-09-01 15:10

Besides locate and  query select you can use Filter/Filtered
For example:

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
begin
ZTable1.Locate('product','a',[LoPartialKey,loCaseInsensitive])
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
ZTable1.Filtered:=false;
ZTable1.Filter:='product like ''a*''';
ZTable1.Filtered:=true;
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
ZTable1.Filtered:=false;
ZTable1.Filter:='product >= ''a'' and product < ''b''';
ZTable1.Filtered:=true;
end;

Michal

----------------------------------------------------------------------
miab3 · 2015-09-06 19:30

Code: [Select]With ZQuery1 do Begin
       Sql.Clear;
       SQL.Text := 'SELECT product FROM products WHERE product >= :x
LIMIT 10;';
       Params.ParamByName('x').AsString := Edit1.Text;
//       ExecSql;  <-- is not needed
       Open;
    end; 
Michal