Still hitting head against wall lazarus / 16-Databases ---------------------------------------------------------------------- liewald · 2012-07-02 13:25 Does anyone have any idea if it is possible in any way to create an in memory database with a working index. NOTHING I try seems to work. ZMSQL in so full of bugs I cant get it to work TBufdataset seems to have been fixed in FPC 2.7.1 but I cant get it to install SQLite. works but is increadably slow I'm dying here folks!!!! ---------------------------------------------------------------------- Blestan · 2012-07-02 13:41 How many rows you have in this dataset? i cannot imagine that sqlite is slow .... give some hints about your goals ---------------------------------------------------------------------- BigChimp · 2012-07-02 13:42 Do you think Bufdataset has been fixed in 2.7 or are you sure? Going from the rest of your post, I suppose you mean you saw it's been fixed in the bugtracker but couldn't test because you couldn't install fpc 2.7. Recent versions of FPC trunk (2.7.1.) failed to compile Lazarus trunk... you may have better luck with a bit older revision. You could use e.g. ludob's and my fpcup tool (see 3rd party announcements for details) to do a parallel installation of FPC trunk+Lazarus in c:\development\ (on Windows) or ~/lazarus and ~/fpc. Note: you'll have to specify the --fpcurl parameter as fpcup defaults to fixes 2.6/2.6.1; see fpcup --help for details. Works on Linux and Windows. As for zmsql - sorry to be a bit crude here but if there are bugs there can't you fix them and submit them to tatamata? Of course I realize fixing other people's code wasn't what you set out to do, but perhaps it turns out to be the quickest way. Just my 2 cents... ---------------------------------------------------------------------- BigChimp · 2012-07-02 13:43 @Blestan: you might want to search on Liewald's posts re sqlite in memory... I think they were large datasets... and IIRC nobody could figure out why sqlite was slow... ---------------------------------------------------------------------- liewald · 2012-07-02 14:01 Lots of rows  > 3Mb in some cases. Human genomes are big! the new datasets are getting bigger latest datasets are 3.5 GB per individual ---------------------------------------------------------------------- Blestan · 2012-07-02 14:04 for 3,5GB maybe you should use mysql with memory db on a dedicated machine ... ---------------------------------------------------------------------- liewald · 2012-07-02 14:05 Looks like they found then bug and fixed it in 2.7.1 but just cant get 2.7.1 to work and no 2.6 patch was released.  just waited 3 days for a short prog to do 400,000  lines out of 2.5 million each line with being processed with just 3 lookups. Hell!!!! ---------------------------------------------------------------------- Blestan · 2012-07-02 14:08 did you optimized the SQL? ---------------------------------------------------------------------- liewald · 2012-07-02 14:09 I'm not at 3.5 GB yet but will be soon.Mysql is an option  But still need to connect to it! ---------------------------------------------------------------------- liewald · 2012-07-02 14:11 no sql with Tbufdatset. just locate. Worth pointing out that this worked perfectly in older fpc but then collapsed and died on one of the upgraded. a 20 min job turned into 4 days due to indexing problems. Will insytall a local copy on mysql and tect but not hopefull as passing commands to an engine is not as quick as tbufdatset was, as passing millions of sql statements rather than working on a local buffered dataset is SLOOOOOOOOOOOW! ---------------------------------------------------------------------- Blestan · 2012-07-02 14:16 Massive db servers are optimized for this kind of stuff ... but avoid sending command to the server ... write SQL that will do the hole job .... make  use of views and temp tables when using aggregate data ... if you give me an idea what kind of lookups you do i can help you with the SQL ---------------------------------------------------------------------- BigChimp · 2012-07-02 14:23 Quote from: liewald on July 02, 2012, 04:11:53 pmWill insytall a local copy on mysql and tect but not hopefull I'd also try getting an FPC 2.7.1 version in (much easier if you don't need Lazarus - FPC revision 21757 compiles fine... it's just Lazarus that won't work well... at least in my setup). Either use fpcup or get it using subversion yourself... fpcup is the easiest way though. ---------------------------------------------------------------------- eny · 2012-07-02 14:32 Quote from: liewald on July 02, 2012, 03:25:08 pmSQLite. works but is increadably slowGot SQLite working in memory and is extremely fast (all actions) with SQL. Tried with 3.1M records and results are there instantly. I'm not using a wrapper class but direct access to SQLite functions (own layer for the sqlite ddl). ---------------------------------------------------------------------- liewald · 2012-07-02 14:32 Nah dont work like that. wish it did. here's pseudocode of what needs to be done. 1. Read in and store reference set one ( 500,000 lines); 2. Read in and store reference set two ( 400,000) lines); 3. Read in and store Reference set three ( 200,000) lines; 4. Process results file  readln from results file Parse and extract values. do lookups and extract additional values from 3 lookups assemble result string; write out. you could say read it all in to a database and do it there but the analysis files (22 of them) are all in excess of 2,000,000 lines and it's quicker to read parse lookup and write. believe me I've tried everything. I got this process down to 3msec and it worked fine using sql it took 40msec. OK if you are doing one or 2 but Gigs of transactions! one text file I had to process was 900GB.... what I need is very quick in memory lookups. nothing more that that. every millisec added adds hours or days to the processing. ---------------------------------------------------------------------- liewald · 2012-07-02 14:34 it's not the size of the database it's thenumber of transactions. try hitting sqlite with 3 GB of transactions and see what happens ---------------------------------------------------------------------- eny · 2012-07-02 14:50 Quote from: liewald on July 02, 2012, 04:32:39 pmNah dont work like that. wish it did. here's pseudocode of what needs to be done. What step of the process takes so long? And what is done in this 3msec? You could work with in memory hash tables. Quote from: liewald on July 02, 2012, 04:34:23 pmit's not the size of the database it's thenumber of transactions. try hitting sqlite with 3 GB of transactions and see what happens  There are few relational databases that run 3Gig transactions in less than 3 msecs so in your case indeed that might not be the solution. [update on hashlist] Quick test on XP: 3M hash entries. 2M lookups take a little less than 1 second. ---------------------------------------------------------------------- liewald · 2012-07-02 15:17 the full process of read lookup and write used to take 3msec. it's the lookups that are killing it now because indexes cannot be established all calls to lookup are linear searches. kills the performance. ---------------------------------------------------------------------- ludob · 2012-07-02 15:23 Have you tried using hash lists or any other specialised list? I have written a double sorted string list which is a good compromise between insert speed and lookup speed. A small test program shows that such a list populated with 500000 different random string values (2-17 characters) will do 100000 lookups per second on a not so fast laptop. Loading the list with 500000 unique values from an array of 1 million random values takes 9 seconds. This includes the test to see if the value was already in the list before storing it. Let me know if you are interested in this unit. ---------------------------------------------------------------------- liewald · 2012-07-02 15:28 worth trying. ---------------------------------------------------------------------- eny · 2012-07-02 15:29 Quote from: liewald on July 02, 2012, 05:17:30 pmthe full process of read lookup and write used to take 3msec. it's the lookups that are killing it now because indexes cannot be established all calls to lookup are linear searches. kills the performance. OK, clear. Setting up an in mem db with the approx. 1M records takes a couple of seconds obviously. But lookups are in the microsecond range. I can do approx. 40K lookups in a second with SQLite i.e. 0,025 msec per lookup!! ---------------------------------------------------------------------- ludob · 2012-07-02 15:50 Attached is the test program I used. TDSStringList (unit doublesortedlist) is used much like a TStringList with the difference that all strings are sorted when added. With every string you can associate a pointer that can point to anything: a record, an object, .... TDSStringList destroys all strings when destroyed but you need to provide a RemoveCallback procedure or  RemoveCallbackMethod method if you want TDSStringList to destroy the attached object for you. TDSStringList.FindEntry (const s: string; var pentry: PDSStringListEntry ): boolean; is used to locate a string. It returns true if the string is found. pentry.value is the string found and pentry. p is the pointer associated with the string. If the string is not found, pentry will point to the string that is just before the string searched. Useful for fuzzy searches. The string sort order is the normal ansi sort order and can be overridden by setting the CompareCallback property with your own string compare function. ---------------------------------------------------------------------- eny · 2012-07-02 16:00 Or use the out of the box TFPHashList or TFPHashObjectList (in contnrs). ---------------------------------------------------------------------- ludob · 2012-07-02 16:53 Quote from: eny on July 02, 2012, 06:00:58 pmOr use the out of the box TFPHashList or TFPHashObjectList (in contnrs). Modified my little test program to use TFPHashList and it is roughly 10-13 times faster than TDSStringList. So if you just need the lookup feature then TFPHashList is the better choice with more than 1M lookups per second. ---------------------------------------------------------------------- avra · 2012-07-03 08:11 Quote from: liewald on July 02, 2012, 04:05:22 pmLooks like they found then bug and fixed it in 2.7.1 but just cant get 2.7.1 to work and no 2.6 patch was released.CodeTyphon v2.70 works out of the box with Lazarus 1.1 and FPC 2.7.1 from 19.6.2012 and SVN 37429 CT. ---------------------------------------------------------------------- liewald · 2012-07-03 08:39 Ok I used a TFPStringHashTable to hold the lookup values and populated it using virtually the same routines as the bufdataset. Process the 21 files and created output file in about  45seconds... WOW love saving millisecs thanks Ludob for the heads up. would still like to get the bufdatset working for some other stuff though! ---------------------------------------------------------------------- BigChimp · 2012-07-03 08:54 Quote from: liewaldwould still like to get the bufdatset working for some other stuff though! Hello? Are you receiving me? BTW, if it works with FPC 2.7, you could ask for a backport to 2.6.1 (if it isn't in the pipeline already), e.g. via raising a bugtracker issue. Marcov would probably do the backporting... Quote from: BigChimp on July 02, 2012, 04:23:23 pmQuote from: liewald on July 02, 2012, 04:11:53 pmWill insytall a local copy on mysql and tect but not hopefull I'd also try getting an FPC 2.7.1 version in (much easier if you don't need Lazarus - FPC revision 21757 compiles fine... it's just Lazarus that won't work well... at least in my setup). Either use fpcup or get it using subversion yourself... fpcup is the easiest way though. Quote from: BigChimp on July 02, 2012, 03:42:29 pmYou could use e.g. ludob's and my fpcup tool (see 3rd party announcements for details) to do a parallel installation of FPC trunk+Lazarus in c:\development\ (on Windows) or ~/lazarus and ~/fpc. Note: you'll have to specify the --fpcurl parameter as fpcup defaults to fixes 2.6/2.6.1; see fpcup --help for details. Works on Linux and Windows. ---------------------------------------------------------------------- liewald · 2012-07-03 08:59 Receiving loud and clear..... Busy running about 40 analyses I have cued to be followed by 150 3d brain reconstructions from MRI Scans and 80 other MRI analyses. Also building my new 24 processor thread 96 GB linux processing server. Lifes a bitch aint it Dave ---------------------------------------------------------------------- BigChimp · 2012-07-03 09:17 As long as you're having fun ---------------------------------------------------------------------- liewald · 2012-07-03 09:24 Fun is relative much rather be playing about doing 3D art..... saving humanity takes so much bloody time! ---------------------------------------------------------------------- eny · 2012-07-03 10:10 Quote from: liewald on July 03, 2012, 10:39:42 amOk I used a TFPStringHashTable to hold the lookup values and populated it using virtually the same routines as the bufdataset. Process the 21 files and created output file in about  45seconds... WOW love saving millisecs thanks Ludob for the heads up.Using hash classes, wow, I wish I would have thought of that... ---------------------------------------------------------------------- BigChimp · 2012-07-04 08:49 That settles it: I'm never going to answer posts again unless FPC+Lazarus version is specified. Bug report 20514 indicates the indexing bug fix was already backported to 2.6.1. OP was still on 2.6.0... perhaps expecting that to be patched?!?? Edit: 2.6=>2.6.0 for clarification ---------------------------------------------------------------------- liewald · 2012-07-04 09:21 Damn Missed that! Doesn't look as if there's a version of 2.6.1 for 64 bit linux yet.... Or I'm being and idiot and cant find it ---------------------------------------------------------------------- Leledumbo · 2012-07-04 13:07 QuoteDoesn't look as if there's a version of 2.6.1 for 64 bit linux yet.... Or I'm being and idiot and cant find itThere's always one for all platforms... in the form of source code Just download the source and compile yourself ---------------------------------------------------------------------- liewald · 2012-07-05 13:39 Nope Cant find one anywhere! I'm an idiot! ---------------------------------------------------------------------- Leledumbo · 2012-07-05 13:59 Here you go: ftp://ftp.freepascal.org/pub/fpc/snapshot/v26/source/fpc.zip You're right, no 2.6.1 source in the daily snapshot. ---------------------------------------------------------------------- marcov · 2012-07-05 18:49 Quote from: Leledumbo on July 05, 2012, 03:59:44 pm You're right, no 2.6.1 source in the daily snapshot. There is always tomorrow ...... ---------------------------------------------------------------------- liewald · 2012-07-09 09:01 Thanks Got it