Recent

Author Topic: Indexes in memory datasets (TBufDataSet, Client Dataset)?  (Read 11373 times)

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Indexes in memory datasets (TBufDataSet, Client Dataset)?
« on: January 28, 2014, 12:31:17 pm »
I was wondering, if someone know answer, is there any benefit in using indexes in memory datasets such as TBufDataset in Lazarus or Client Dataset in Delphi, besides setting sorting?
Do indexes benefit, for instance speed of filtering or Locate?

mangakissa

  • Hero Member
  • *****
  • Posts: 943
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #1 on: January 28, 2014, 01:36:55 pm »
Yes. Lookuptables are using indexes for searching with speed.
Look at the other way. A TBufdataset has 30 fields with 5000 records. If you want to locate a record, Tbufdataset engine must search sequentially. If there is a index with one or two fields, the search is much easier, because the file is less greater then the dataset and is always sorted.

TBufdataet.Filter and TClientdataset.Filter are not using an index, but TClientdataset.Setrange in Delphi is.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #2 on: January 28, 2014, 02:12:38 pm »
What is difference (in this respect) between indexes created simply by setting field names in IndexFieldNames property and indexes created in "harder" way? Do they influence speed the same way or there is a difference?

Another question: Imagine you have to sequentially go through all records and determine whether records satisfy some criteria, if yes you update those records.
Is there any benefit in using filter before that, so that condition is set in filter? I mean, if filter does not utilize indexes, then it is just overhead, right?

mangakissa

  • Hero Member
  • *****
  • Posts: 943
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #3 on: January 28, 2014, 03:58:50 pm »
Quote
What is difference (in this respect) between indexes created simply by setting field names in IndexFieldNames
IndexFieldNames are on-the-fly created indexes.
Quote
Is there any benefit in using filter before that, so that condition is set in filter? I mean, if filter does not utilize indexes, then it is just overhead, right?
Setting a filter on a dataset telling to your dataset: `Search all the record sequentially and put a mark in the dataset for browsing the record`.
So, if you first set a filter and read your records sequentially, you read your dataset twice.

Example:
Code: [Select]
SQL.text := "select field1,field2,field3,field4,field5 from table order by field1,field2;
indexfieldnames := 'field1;field2';
filter := 'field1 = 'Lazarus Forum';
locate('field1;field2',vararrayof(['Lazarus Forum','databases']),[]);
Locate is still using an index doing a  search.

Example 2:
Code: [Select]
SQL.text := "select field1,field2,field3,field4,field5 from table order by field1,field2;
indexfieldnames := 'field1;field2';
filter := 'field1 = 'Lazarus Forum';
locate('field3',vararrayof(['index']),[]);
Locate is looking into the dataset doing a  search.

There's no rule written how to use a proper index or filter. But in databases it will increase the speed enormously. If you're working in memory the speed is already high.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #4 on: January 28, 2014, 10:42:07 pm »
Ok, thanks for clarification.
One more question, there is some default index (?) "DEFAULT_ORDER" that is present if no index specified, right? Is this a real index or something else in nature?
If answer is yes, does it mean that memory dataset always has an index active, even if not specified by user?

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #5 on: January 28, 2014, 10:44:59 pm »
default order should be the order the items where inserted in the table, no index is used.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #6 on: January 29, 2014, 03:20:44 pm »
taaz, thanks, I think everything is clear now...

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #7 on: February 15, 2014, 12:40:46 pm »
oh, it seems that ranges (SetRange method) is not existing in TBufDataset?
So, there is no way for indexed filtering?

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #8 on: February 15, 2014, 10:02:08 pm »
AFAIK in TBufDatasets are indexes used only for sorting (ordering) records.
They are not involved in seraching (Locate, Lookup) and also not in Filtering.

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #9 on: February 15, 2014, 10:31:01 pm »
AFAIK in TBufDatasets are indexes used only for sorting (ordering) records.
They are not involved in seraching (Locate, Lookup) and also not in Filtering.
do you plan to change this in near future? ;)

LacaK

  • Hero Member
  • *****
  • Posts: 577
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #10 on: February 17, 2014, 07:18:40 am »
AFAIK in TBufDatasets are indexes used only for sorting (ordering) records.
They are not involved in seraching (Locate, Lookup) and also not in Filtering.
do you plan to change this in near future? ;)
Personally I do not plan do it. It is too complicated task for me. (it requires totaly rework of existing double linked list of records to any other structure like B-tree and then introduce expression parser which will parse filter or locate condition and make decision if there is any useful index which can be used ... it will very complicate things IMO)

hrayon

  • Full Member
  • ***
  • Posts: 102
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #11 on: February 17, 2014, 12:38:54 pm »
Hi!
How to delete an Index from TBufDataset?

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #12 on: February 17, 2014, 02:42:03 pm »
Hi!
How to delete an Index from TBufDataset?
I think, you just set IndexName:='DEFAULT_ORDER'

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #13 on: February 17, 2014, 04:17:27 pm »
AFAIK in TBufDatasets are indexes used only for sorting (ordering) records.
They are not involved in seraching (Locate, Lookup) and also not in Filtering.
do you plan to change this in near future? ;)
Personally I do not plan do it. It is too complicated task for me. (it requires totaly rework of existing double linked list of records to any other structure like B-tree and then introduce expression parser which will parse filter or locate condition and make decision if there is any useful index which can be used ... it will very complicate things IMO)
just curious: what is situation with indexes in TDbf?
as far as I remember, I think there is an option to use TDbf in-memory?

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Indexes in memory datasets (TBufDataSet, Client Dataset)?
« Reply #14 on: February 17, 2014, 04:56:02 pm »
as far as I remember, I think there is an option to use TDbf in-memory?
With (memory) streams, yes. IIRC, indexes etc were still stored to disk in fpc 2.6.x; support for storing index in streams was added in trunk/2.7.x... but you'd have to check documentation (user changes trunk wiki page for fpc/tdbf tutorial page) for that.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified