Need a memory resident db with a working index system lazarus / 16-Databases ---------------------------------------------------------------------- liewald · 2011-10-19 07:31 Lazarus 0.9.30.1 r30911 FPC 2.4.2 x86_64-linux-qt does anyone know of a memory db system that has a working index system at the moment . Tbufdataset seems to be broken in as much as it stops storing data as soon as an index is added, memdataset doesn't support this , the jvcl port doesn't work in linux, ZMSQL is not accepting direct input via fieldbyname.value and sqllite is a bit slow! I'm working on very large files and this makes the difference between minutes and days! thanks Dave ---------------------------------------------------------------------- BigChimp · 2011-10-19 07:54 Liewald, Sorry, I don't. If the bufdataset problem doesn't have a bugtracker issue, could you raise one? Also, the current FPC trunk (2.7.1) version seems to have some improvements (based on a quick search through the bugtracker); you might want to see if that version is usable for you. Patches welcome for ZMSQL FieldByName.Value  (Preferably in thread http://lazarus.freepascal.org/index.php/topic,13821.0.html) ZMSQL is partly based on bufdataset. If ZMSQL fixes things for you by any chance, it may make sense to port the changes in zmbufdataset.pas back to bufdataset. This is what the ZMSQL designer, tatamata, also wants. ---------------------------------------------------------------------- TheBlackSheep · 2011-10-19 08:33 Hi Liewald Apologies if the JVCL port isn't working in Linux - I thought it did - which version of Linux are you using and I'll see if I can test it - if you've already tested it what didn't work? Are you using a 64 bit version of FPC (and therefore probably a 64 bit Linux distro)? - I tend to use the CodeTyphon distributions which, to a certain extent, standardise the environment between Windows/Linux and althought they've recently extended the distro's they "support" beyond just Ubuntu, I haven't used this version on Linux recently. TheBlackSheep ---------------------------------------------------------------------- tatamata · 2011-10-19 17:15 Quote from: liewald on October 19, 2011, 09:31:55 amZMSQL is not accepting direct input via fieldbyname.value Well, how do you mean that fieldbyname does not work? ZMQueryDataset1.FieldByName('FieldName').Value:='test' works as expected.... ---------------------------------------------------------------------- tatamata · 2011-10-19 17:24 Quote from: TheBlackSheep on October 19, 2011, 10:33:10 amHi Liewald Apologies if the JVCL port isn't working in Linux - I thought it did - which version of Linux are you using and I'll see if I can test it - if you've already tested it what didn't work? Are you using a 64 bit version of FPC (and therefore probably a 64 bit Linux distro)? - I tend to use the CodeTyphon distributions which, to a certain extent, standardise the environment between Windows/Linux and althought they've recently extended the distro's they "support" beyond just Ubuntu, I haven't used this version on Linux recently. TheBlackSheep Me too could not install JVCL, on Puppy Linux... ---------------------------------------------------------------------- liewald · 2011-10-20 09:19 I'm using 64bit opensuse 11.2 - 4 of a number of machines. problem is persistent across the different installations.  I've posted the TBufdataset bug and I'm waiting for a resolution QuoteWell, how do you mean that fieldbyname does not work? ZMQueryDataset1.FieldByName('FieldName').Value:='test' works as expected.... Sorry it did not work in my program. Since the ZMQuerydataset is based on TBUfdataset I think it may well be the same problem as I have bug reported. If you add an index the data assignment fails! ---------------------------------------------------------------------- tatamata · 2011-10-20 15:46 Ok., I haven't tested with indexes... ---------------------------------------------------------------------- JuhaManninen · 2011-10-20 18:45 Quote from: liewald on October 19, 2011, 09:31:55 amdoes anyone know of a memory db system that has a working index system at the moment . Tbufdataset seems to be broken in as much as it stops storing data as soon as an index is added, memdataset doesn't support this , the jvcl port doesn't work in linux, ZMSQL is not accepting direct input via fieldbyname.value and sqllite is a bit slow! I'm working on very large files and this makes the difference between minutes and days! What requirements do you have for it? Do you store much numeric data or mostly strings? Do you need SQL? Do you want to use data-aware controls to show data. I actually have an interesting in-memory DB code, made with Delphi already few years ago. It is optimized for strings, all data is stored as string. If the data is numeric, it must be converted before calculations. It supports 2 index types: a fast sorted index and even faster hash- index for lookups. It can work together with VirtualTreeView component to show its data but it can work also by itself. My requirement was to load a huge amount (> 1 000 000 records) of textual data into memory, and have it indexed by different fields for quick searches. I tested many existing Delphi memory DB's. They all choked and halted already after reading in 20 000 records, at least when being indexed. So I had to make my own. Sorted index would be slow to update when adding data into the table. I solved it by marking the index "dirty" when data is added and then doing a QuickSort when it is used for the first time. The hash-index can be updated while adding data. One more optimization is a string-pool which uses the reference count feature of strings to reuse them for identical strings in the table. There is a derived component for hierarchical tree-structured data (which is quite innovative). The only problem now is that I still haven't ported it to Lazarus. The porting itself should not be a problem (there is nothing Windows or Delphi specific), but the documentation is poor and there are no examples. I will try to do something with it next week when I have a holiday from school. (today was the last exam, about PHP programming . Regards Juha ---------------------------------------------------------------------- liewald · 2011-10-21 08:49 I'm using the mem database basically as a lookup table, so it contains strings. Basically I have 2 files one which contains data to be filtered against the contents of the second file. I load the filter fields in the second file into a memdatabase and then run through the first file and test whether the corresponding fields exist in the mem db. If the do the line record is written to an error file otherwise it is written to an output file. A fairly simple app the catch is the size of the text files. The exclusion file is only a few MB but the file to be filtered  is normally hundreds of MB to GB's in size. As each linerecord requires a lookup to determine it's validity the MEMDB is critical to the process and can grossly impact the app. I should add that I can read the record into either a memdataset or a bufdataset without any problem(64 bit Linux). it's the indexing thats the issue. Dave ---------------------------------------------------------------------- liewald · 2011-11-02 09:35 Any Ideas anyone. This is a real show stopper for me! ---------------------------------------------------------------------- BigChimp · 2011-11-02 11:52 Don't know if it's been done with FreePascal, but you might try using sqlite in in memory mode: http://www.sqlite.org/inmemorydb.html ---------------------------------------------------------------------- liewald · 2011-11-02 13:14 I'm using SQLite at the moment but it is incredibly slow compared with tbufdataset as it worked in older versions. the lookup filter takes > 26 Hrs compared with the 20 mins it took with a working tbufdataset! ---------------------------------------------------------------------- BigChimp · 2011-11-02 14:21 Dave, Ok, didn't know you were using SQLite in-memory. That's a HUGE difference! Sorry, no more ideas except you might want try to fix the bufdataset problem if you can find the cause and post the patch to the bugtracker... Or possibly use a stringlist but that might be just my newb-ness showing... ---------------------------------------------------------------------- liewald · 2011-11-02 15:25 looks Like I'm going to have to . the issue is really the lookup using an index otherwise I could us any basic structure. the lookupset usually has about  100 - 300kK values and the dataset can have between 2.5  and 37.5Mg records, so  doing a few million lookups on a 100- 300k Dataset without indexes takes a bit of time LOL ---------------------------------------------------------------------- tatamata · 2011-11-02 17:11 I think that TDbf can be used in in-memory mode, but I didn't try... ---------------------------------------------------------------------- mica · 2011-11-02 17:21 http://blog.synopse.info/post/2011/01/22/Synopse-Big-Table-1.12a But Delphi only,i dont know if its hard to port (hmm or kbmMemTable?) ---------------------------------------------------------------------- BlueIcaro · 2011-11-02 18:12 Did you try to compile it, using the Delphi mode directive? /BlueIcaro ---------------------------------------------------------------------- garlar27 · 2011-11-02 20:36 Quote from: liewald on November 02, 2011, 02:14:25 pmI'm using SQLite at the moment but it is incredibly slow compared with tbufdataset as it worked in older versions. the lookup filter takes > 26 Hrs compared with the 20 mins it took with a working tbufdataset! Using it as a memory DB like BigChimp said? if so it might be due to the caching of large memory use. SQLite uses atomic recording to ensure that data has been properly saved on disk, and it cost some milliseconds for HD access. You can change this configuration to see if the recording is faster. I've also noticed that using TDataset methods (like Filter, Append, Edit, Post, and Delete) to process data is snail sloooooow!!! You can see how your nails grow waiting the process to end!! It's better to use SQL statements to Select, Insert, Update or Delete data. And if you need to execute many statements at the same time (inserts, updates, or deletes) it's better to enclose them in a BEGIN; ..... COMMIT; to speed up the process. You can try this while tmemdataset is fixed... ---------------------------------------------------------------------- liewald · 2011-11-03 09:28 Already Have ---------------------------------------------------------------------- garlar27 · 2011-11-03 19:27 Ouch!!!  I was doing some tests..... Inserting more than 2800 rows using TZquery.ExecSQL took 6 minutes  The same script using TZConnection.ExecuteDirect took 593 milliseconds (30 milliseconds more than using SQLite Manager). I hope this can help you. If not I'm running out of ideas... ---------------------------------------------------------------------- BigChimp · 2011-11-04 13:08 Dave, You could post your question on the FPC mailing list; there's other people there who might be able to help you.... ---------------------------------------------------------------------- garlar27 · 2011-11-04 13:30 Oops!!  The delay I mentioned it's not caused by the ExecuteSQL but by my code : Code: [Select]AQuery.SQL.Text := SQ; // I FORGOT THAT THIS IS VERY SLOOOOOOOW  IT TAKES +8 MINUTES TO PROCESS 2800 SQL STATEMENTS. AQuery.ExecuteSQL; Sorry ---------------------------------------------------------------------- TheBlackSheep · 2011-11-07 10:23 Don't know why the JVCL version SIGSEV errors in 64 bit mode - only just managed to finds a working 64bit system to try it and am still looking at it but it doesn't look good at the minute - I thought it likely to be pointers or field sizes which would possibly be different sizes between 32 bit/64 bit but although a simple test program compiles, it SIGSEV's as soon as you try and talk to the component (so it's not obvious at the minute where it's going awry).   Any pointers/help here would be appreciated. As the JVCL version I knocked together is based on the old RxMemoryDataset (originally) you could try that option - it's installed by default in the CodeTyphon Lazarus 64bit version and appears to work ok.  It doesn't have persistence but you could add the code I've mentioned previously on this forum to do that if you wanted.  Also filtering doesn't work the normal way (via a filter string and setting to filtered) so you'd need a callback routine (again mentioned on the same thread) although again you could add in the code manually from "my" version to do it the normal way.  The original thread to discuss this was; http://www.lazarus.freepascal.org/index.php/topic,13297.msg70041.html tatamata - regarding puppy linux  - as you know there are a myriad of puplets that exist out there and they're constantly get changed/updated (a good thing but it's a bit of a "moving feast") - I do use it for support purposes but decided in the end it wasn't really practical to develop Lazarus applications in it as for most practical purposes (as a general statement) it doesn't have all the libraries pre-installed since it removes them to keep the system small and fast.  By the time you've added all the bits missing and installed FPC/Lazarus (can't imagine CodeTyphon working on here without a lot of hard work) you may as well have installed a full desktop distro.  However, if you have a working Lazarus compressed sfs file and let me know which distro you're using I will try and take a look (providing it doesn't require a lot of work to get setup). TheBlackSheep ---------------------------------------------------------------------- tatamata · 2012-01-13 18:52 OK. I was just playing around with zmsql and indexes (inherited from TBufDataset). To conclude, it seems that indexes work just fine, at least in sense of sorting. When you create dataset, there is default index, which you can always restore by Dataset.IndexName:='DEFAULT_ORDER'; If you want to add another index, you have to set MaxIndexesCount property to some value other than 0. For example: Dataset1.MaxIndexesCount:=1000; Then you can change index by two different ways: A) using AddIndex method and IndexName property Dataset1.AddIndex('BlaBla1','Product,Component',[ixUnique]); Dataset1.IndexName:='BlaBla1'; B) using IndexFieldNames property Dataset1.IndexFieldNames:='Product,Component'; In both cases data assignment after applying index works OK. Dataset1.Edit; Dataset1.FieldByName('ProductQuantity').AsFloat:='79.8'; Dataset1.Post; ---------------------------------------------------------------------- tatamata · 2012-01-19 08:11 And, in zmsql, you can use SortDataset (const pFieldName:String):Boolean method to sort dataset by any field. ZMQueryDataset will create required index on the fly.