Re: [WebDNA] A new popuated field to a DB with 700.000 records
This WebDNA talk-list message is from 2009
It keeps the original formatting.
numero = 104102
interpreted = N
texte = Thanks Chris, Basically, this code is more than a system that "chunks" database transactions, but that aspect can be referenced from it. The idea is a lot like what ken and others were getting at only adding spawn to divide the process bandwidth. - D =================== d.brooke - mobile www.euca.us =================== On Dec 3, 2009, at 3:16 AM, christophe.billiottet@webdna.us wrote: > here it is: > > ----start code > snippet------------------------------------------------- > [!]-------------------------------------------------------------- > ** Hot Merge Example by Donovan ** > ** Note: This snippet is within a [listfiles..] context that > looks in a "hotfolder/" directory and is fired every 10 > min. by a trigger. This snippet is also after integrity > checks on the uploaded merge file. > ** Note: [tNumRecs_Merge] value is from a [numfound] on the > merge file after the integrity checks. > > -------------------------------------------------------------[/!] > [!] ** Merge 500 records at a time ** [/!] > [text]tNumLoops=[math]floor([tNumRecs_Merge]/500)[/math][/text] > > [!] ** Set some vars ** [/!] > [text multi=T]hmt_index=1[!] > [/!]&tKeyField=MERGE_KEY_FIELD[!] > [/!]&tDestDB_Key=DEST_KEY_FIELD[!] > [/!]&tDestDBPath=your/db/destination.db[!] > [/!]&tNumSecs=10[!] > ^ Edit the above more or less depending on size of > merge. > [/!][/text] > > [!] ** need to set some text vars because some WebDNA is not allowed > in spawn ** [/!] > > [text]tFilename=[filename][/text] > > [!] ** spawn a new process for each block of 500 ** [/!] > [loop start=1&end=[math][tNumLoops]+1[/math]&advance=1] > [spawn] > [search db=hotfolder/[tFilename][!] > [/!]&ne[tKeyField]datarq=find_all[!] > [/!]&startat=[hmt_index][!] > [/!]&max=500] > > [founditems] > [replace db=[tDestDBPath][!] > [/!]&eq[tDestDB_Key]datarq=[interpret][[tKeyField]][/interpret]][!] > [/!]&DestFirstField=[url][Merge1Field][/url][!] > [/!]&DestSecondField=[url][Merge2Field][/url][!] > etc.. > [/!][/replace] > [/founditems] > [/search] > [/spawn] > > [!] ** Wait <[tNumSecs]> seconds to start the next block ** [/!] > [waitforfile file=non_existent_file.txt[!] > [/!]&timeout=[tNumSecs]][/waitforfile] > > [!] ** set index to next 500 block ** [/!] > [text]hmt_index=[math][index]*500[/math][/text] > [/loop] > ----end code------------------------------------------------------- > > > The basic idea was that I didn't really care how long the > merge took, but rather, I wanted to make sure the processor > wasn't overloaded. My idea was to use SPAWN and a waiting technique > using WAITFORFILE to > "spread out" the task. This turned out to work really well I think. > > I used 'top -u apache' to monitor the process on a merge with > 10,000 records, and I didn't see *any* noticeable heightened > processor usage using this code. > > Just thought I'd pass this experiment along to the list! > > Donovan > > > disclaimer: :) the above code was snipped out of a live working > system, > but to make it legible and universal, I rewrote a bit of it above > so there could be some syntax errors from the rewrite. > > > On Dec 3, 2009, at 2:24, Donovan Brooke wrote: > >> Palle, I wrote a merge system a while back that I may have posted >> to the list. .. Not sure. The system automatically breaks the >> tasks into chunks, only you don't have to baby sit it. >> >> Maybe check both archives.... >> >> Donovan >> >> =================== >> d.brooke - mobile >> www.euca.us >> =================== >> >> On Dec 2, 2009, at 3:15 PM, Palle Bo Nielsen
> > wrote: >> >>> Dan, >>> >>> I understand where you are getting but it would be preferable to >>> have the IDs immediately. >>> >>> I will let my questions live for 24 hours or so and then move to >>> plan B (in chunks) unless the better idea pops up from the list or >>> my own pillow tonight. >>> >>> Palle >>> >>> >>> >>> On 02/12/2009, at 22.06, Dan Strong wrote: >>> >>>>> 1) Yes, but is not sequential. >>>> >>>> Ok, next question: do the new, unique sequential IDs need to be >>>> in there immediately? If not, perhaps add the new uniques as >>>> the .db is otherwise being hit? >>>> >>>> -Dan Strong >>>> http://www.DanStrong.com >>>> >>>> Palle Bo Nielsen wrote: >>>>> Dan, >>>>> 1) Yes, but is not sequential. >>>>> 2) Yes, same result - on it's knees. >>>>> 3) This is my next plan, but it would be nice to get it done in >>>>> one process. >>>>> Palle >>>>> On 02/12/2009, at 21.56, Dan Strong wrote: >>>>>> 1) Is there any other unique identifier in each record? >>>>>> 2) Have you tried [replacefounditems]? >>>>>> 3) Maybe "chunk it" to only do, say, 500 at a time? >>>>>> >>>>>> -Dan Strong >>>>>> http://www.DanStrong.com >>>>>> >>>>>> Palle Bo Nielsen wrote: >>>>>>> Hi all, >>>>>>> I got a DB which has 11 fields and more that 700.000 lines of >>>>>>> data. >>>>>>> Now I need to add a new field and I need to populate this >>>>>>> field with an index from the number 1 to the most recent >>>>>>> number which for example is 700.000. >>>>>>> The number 1 is the first lines of data within this BD and the >>>>>>> 700.000 is the most recent added line of data. >>>>>>> If have done some test and sand-boxing and every time WebDNA >>>>>>> goes into it's knees. >>>>>>> I need some good advise from you all to figure out the best >>>>>>> solution. >>>>>>> I look forward to your suggestions. >>>>>>> **** this one is rough and breaks WebDNA **** >>>>>>> [search db=l4.db&nel4_statusdata=dummy&asl4_serialsort=1] >>>>>>> [numFound][foundItems] >>>>>>> [replace db=l4.db&eql4_skudata=[l4_sku]]l4_index=[index][/ >>>>>>> replace] >>>>>>> [/foundItems][/search] >>>>>>> **** snip **** >>>>>>> Palle--------------------------------------------------------- >>>>>>> This message is sent to you because you are subscribed to >>>>>>> the mailing list . >>>>>>> To unsubscribe, E-mail to: >>>>>>> archives: http://mail.webdna.us/list/talk@webdna.us >>>>>>> old archives: http://dev.webdna.us/TalkListArchive/ >>>>>>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >>>>>> --------------------------------------------------------- >>>>>> This message is sent to you because you are subscribed to >>>>>> the mailing list . >>>>>> To unsubscribe, E-mail to: >>>>>> archives: http://mail.webdna.us/list/talk@webdna.us >>>>>> old archives: http://dev.webdna.us/TalkListArchive/ >>>>>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >>>>>> >>>>> --------------------------------------------------------- >>>>> This message is sent to you because you are subscribed to >>>>> the mailing list . >>>>> To unsubscribe, E-mail to: >>>>> archives: http://mail.webdna.us/list/talk@webdna.us >>>>> old archives: http://dev.webdna.us/TalkListArchive/ >>>>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >>>> --------------------------------------------------------- >>>> This message is sent to you because you are subscribed to >>>> the mailing list . >>>> To unsubscribe, E-mail to: >>>> archives: http://mail.webdna.us/list/talk@webdna.us >>>> old archives: http://dev.webdna.us/TalkListArchive/ >>>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >>>> >>> >>> --------------------------------------------------------- >>> This message is sent to you because you are subscribed to >>> the mailing list . >>> To unsubscribe, E-mail to: >>> archives: http://mail.webdna.us/list/talk@webdna.us >>> old archives: http://dev.webdna.us/TalkListArchive/ >>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >> --------------------------------------------------------- >> This message is sent to you because you are subscribed to >> the mailing list . >> To unsubscribe, E-mail to: >> archives: http://mail.webdna.us/list/talk@webdna.us >> old archives: http://dev.webdna.us/TalkListArchive/ >> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 > > --------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > archives: http://mail.webdna.us/list/talk@webdna.us > old archives: http://dev.webdna.us/TalkListArchive/ > Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288
Associated Messages, from the most recent to the oldest:
Thanks Chris, Basically, this code is more than a system that "chunks" database transactions, but that aspect can be referenced from it. The idea is a lot like what ken and others were getting at only adding spawn to divide the process bandwidth. - D =================== d.brooke - mobile www.euca.us =================== On Dec 3, 2009, at 3:16 AM, christophe.billiottet@webdna.us wrote: > here it is: > > ----start code > snippet------------------------------------------------- > [!]-------------------------------------------------------------- > ** Hot Merge Example by Donovan ** > ** Note: This snippet is within a [listfiles..] context that > looks in a "hotfolder/" directory and is fired every 10 > min. by a trigger. This snippet is also after integrity > checks on the uploaded merge file. > ** Note: [tNumRecs_Merge] value is from a [numfound] on the > merge file after the integrity checks. > > -------------------------------------------------------------[/!] > [!] ** Merge 500 records at a time ** [/!] > [text]tNumLoops=[math]floor([tNumRecs_Merge]/500)[/math][/text] > > [!] ** Set some vars ** [/!] > [text multi=T]hmt_index=1[!] > [/!]&tKeyField=MERGE_KEY_FIELD[!] > [/!]&tDestDB_Key=DEST_KEY_FIELD[!] > [/!]&tDestDBPath=your/db/destination.db[!] > [/!]&tNumSecs=10[!] > ^ Edit the above more or less depending on size of > merge. > [/!][/text] > > [!] ** need to set some text vars because some WebDNA is not allowed > in spawn ** [/!] > > [text]tFilename=[filename][/text] > > [!] ** spawn a new process for each block of 500 ** [/!] > [loop start=1&end=[math][tNumLoops]+1[/math]&advance=1] > [spawn] > [search db=hotfolder/[tFilename][!] > [/!]&ne[tKeyField]datarq=find_all[!] > [/!]&startat=[hmt_index][!] > [/!]&max=500] > > [founditems] > [replace db=[tDestDBPath][!] > [/!]&eq[tDestDB_Key]datarq=[interpret][[tKeyField]][/interpret]][!] > [/!]&DestFirstField=[url][Merge1Field][/url][!] > [/!]&DestSecondField=[url][Merge2Field][/url][!] > etc.. > [/!][/replace] > [/founditems] > [/search] > [/spawn] > > [!] ** Wait <[tNumSecs]> seconds to start the next block ** [/!] > [waitforfile file=non_existent_file.txt[!] > [/!]&timeout=[tNumSecs]][/waitforfile] > > [!] ** set index to next 500 block ** [/!] > [text]hmt_index=[math][index]*500[/math][/text] > [/loop] > ----end code------------------------------------------------------- > > > The basic idea was that I didn't really care how long the > merge took, but rather, I wanted to make sure the processor > wasn't overloaded. My idea was to use SPAWN and a waiting technique > using WAITFORFILE to > "spread out" the task. This turned out to work really well I think. > > I used 'top -u apache' to monitor the process on a merge with > 10,000 records, and I didn't see *any* noticeable heightened > processor usage using this code. > > Just thought I'd pass this experiment along to the list! > > Donovan > > > disclaimer: :) the above code was snipped out of a live working > system, > but to make it legible and universal, I rewrote a bit of it above > so there could be some syntax errors from the rewrite. > > > On Dec 3, 2009, at 2:24, Donovan Brooke wrote: > >> Palle, I wrote a merge system a while back that I may have posted >> to the list. .. Not sure. The system automatically breaks the >> tasks into chunks, only you don't have to baby sit it. >> >> Maybe check both archives.... >> >> Donovan >> >> =================== >> d.brooke - mobile >> www.euca.us >> =================== >> >> On Dec 2, 2009, at 3:15 PM, Palle Bo Nielsen > > wrote: >> >>> Dan, >>> >>> I understand where you are getting but it would be preferable to >>> have the IDs immediately. >>> >>> I will let my questions live for 24 hours or so and then move to >>> plan B (in chunks) unless the better idea pops up from the list or >>> my own pillow tonight. >>> >>> Palle >>> >>> >>> >>> On 02/12/2009, at 22.06, Dan Strong wrote: >>> >>>>> 1) Yes, but is not sequential. >>>> >>>> Ok, next question: do the new, unique sequential IDs need to be >>>> in there immediately? If not, perhaps add the new uniques as >>>> the .db is otherwise being hit? >>>> >>>> -Dan Strong >>>> http://www.DanStrong.com >>>> >>>> Palle Bo Nielsen wrote: >>>>> Dan, >>>>> 1) Yes, but is not sequential. >>>>> 2) Yes, same result - on it's knees. >>>>> 3) This is my next plan, but it would be nice to get it done in >>>>> one process. >>>>> Palle >>>>> On 02/12/2009, at 21.56, Dan Strong wrote: >>>>>> 1) Is there any other unique identifier in each record? >>>>>> 2) Have you tried [replacefounditems]? >>>>>> 3) Maybe "chunk it" to only do, say, 500 at a time? >>>>>> >>>>>> -Dan Strong >>>>>> http://www.DanStrong.com >>>>>> >>>>>> Palle Bo Nielsen wrote: >>>>>>> Hi all, >>>>>>> I got a DB which has 11 fields and more that 700.000 lines of >>>>>>> data. >>>>>>> Now I need to add a new field and I need to populate this >>>>>>> field with an index from the number 1 to the most recent >>>>>>> number which for example is 700.000. >>>>>>> The number 1 is the first lines of data within this BD and the >>>>>>> 700.000 is the most recent added line of data. >>>>>>> If have done some test and sand-boxing and every time WebDNA >>>>>>> goes into it's knees. >>>>>>> I need some good advise from you all to figure out the best >>>>>>> solution. >>>>>>> I look forward to your suggestions. >>>>>>> **** this one is rough and breaks WebDNA **** >>>>>>> [search db=l4.db&nel4_statusdata=dummy&asl4_serialsort=1] >>>>>>> [numFound][founditems] >>>>>>> [replace db=l4.db&eql4_skudata=[l4_sku]]l4_index=[index][/ >>>>>>> replace] >>>>>>> [/foundItems][/search] >>>>>>> **** snip **** >>>>>>> Palle--------------------------------------------------------- >>>>>>> This message is sent to you because you are subscribed to >>>>>>> the mailing list . >>>>>>> To unsubscribe, E-mail to: >>>>>>> archives: http://mail.webdna.us/list/talk@webdna.us >>>>>>> old archives: http://dev.webdna.us/TalkListArchive/ >>>>>>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >>>>>> --------------------------------------------------------- >>>>>> This message is sent to you because you are subscribed to >>>>>> the mailing list . >>>>>> To unsubscribe, E-mail to: >>>>>> archives: http://mail.webdna.us/list/talk@webdna.us >>>>>> old archives: http://dev.webdna.us/TalkListArchive/ >>>>>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >>>>>> >>>>> --------------------------------------------------------- >>>>> This message is sent to you because you are subscribed to >>>>> the mailing list . >>>>> To unsubscribe, E-mail to: >>>>> archives: http://mail.webdna.us/list/talk@webdna.us >>>>> old archives: http://dev.webdna.us/TalkListArchive/ >>>>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >>>> --------------------------------------------------------- >>>> This message is sent to you because you are subscribed to >>>> the mailing list . >>>> To unsubscribe, E-mail to: >>>> archives: http://mail.webdna.us/list/talk@webdna.us >>>> old archives: http://dev.webdna.us/TalkListArchive/ >>>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >>>> >>> >>> --------------------------------------------------------- >>> This message is sent to you because you are subscribed to >>> the mailing list . >>> To unsubscribe, E-mail to: >>> archives: http://mail.webdna.us/list/talk@webdna.us >>> old archives: http://dev.webdna.us/TalkListArchive/ >>> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 >> --------------------------------------------------------- >> This message is sent to you because you are subscribed to >> the mailing list . >> To unsubscribe, E-mail to: >> archives: http://mail.webdna.us/list/talk@webdna.us >> old archives: http://dev.webdna.us/TalkListArchive/ >> Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288 > > --------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > archives: http://mail.webdna.us/list/talk@webdna.us > old archives: http://dev.webdna.us/TalkListArchive/ > Bug Reporting: http://forum.webdna.us/eucabb.html?page=topics&category=288
Donovan
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
Emailer port change (1997)
RE: automatic reload of frameset (1997)
Logging purchases (1997)
Help with Repost Data msg from form (1997)
change the number format (1997)
Form Weirdness (2002)
Problems searching from a FORM (1997)
[OT] 'Email this story to a friend' (2003)
Order not created error (1997)
RE: 2.01 upgrade problems (1997)
Database Strategy - more... (1998)
Check Boxes (2000)
unique ascending numbers (2003)
WebCatalog/Mac 2.1b2 New Features (1997)
all db's in one folder and protected (1998)
404s - Apache vs. WebDNA (2008)
Help name our technology! (1997)
Keep away (1997)
[WebDNA] Search on a database (2012)
autosensing lanague selection (1997)