Indexes in memory datasets (TBufDataSet, Client Dataset)? lazarus / 16-Databases ---------------------------------------------------------------------- tatamata · 2014-01-28 11:31 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 · 2014-01-28 12:36 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. ---------------------------------------------------------------------- tatamata · 2014-01-28 13:12 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 · 2014-01-28 14:58 QuoteWhat is difference (in this respect) between indexes created simply by setting field names in IndexFieldNames IndexFieldNames are on-the-fly created indexes. QuoteIs 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. ---------------------------------------------------------------------- tatamata · 2014-01-28 21:42 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 · 2014-01-28 21:44 default order should be the order the items where inserted in the table, no index is used. ---------------------------------------------------------------------- tatamata · 2014-01-29 14:20 taaz, thanks, I think everything is clear now... ---------------------------------------------------------------------- tatamata · 2014-02-15 11:40 oh, it seems that ranges (SetRange method) is not existing in TBufDataset? So, there is no way for indexed filtering? ---------------------------------------------------------------------- LacaK · 2014-02-15 21:02 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 · 2014-02-15 21:31 Quote from: LacaK on February 15, 2014, 10:02:08 pmAFAIK 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 · 2014-02-17 06:18 Quote from: tatamata on February 15, 2014, 10:31:01 pmQuote from: LacaK on February 15, 2014, 10:02:08 pmAFAIK 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 · 2014-02-17 11:38 Hi! How to delete an Index from TBufDataset? ---------------------------------------------------------------------- tatamata · 2014-02-17 13:42 Quote from: hrayon on February 17, 2014, 12:38:54 pmHi! How to delete an Index from TBufDataset? I think, you just set IndexName:='DEFAULT_ORDER' ---------------------------------------------------------------------- tatamata · 2014-02-17 15:17 Quote from: LacaK on February 17, 2014, 07:18:40 amQuote from: tatamata on February 15, 2014, 10:31:01 pmQuote from: LacaK on February 15, 2014, 10:02:08 pmAFAIK 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 · 2014-02-17 15:56 Quote from: tatamata on February 17, 2014, 04:17:27 pmas 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.