Mailing List Archive: 49091 messages
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

Speed required

 [1/9] from: kpeters:otaksoft at: 29-Nov-2007 9:50


I need to transform SQL cursors with up to 10,000 records and it needs to be fast: Example cursor: [ [ 100 "Joe Browne" "Chicago" 55 445-5689 ] [ 101 "Joe Browne" "Chicago" 55 223-2221 ] [ 102 "Joe Browne" "Chicago" 55 489-5555 ] [ 103 "Joe Browne" "Chicago" 55 235-2245 ] ] I need a copy of this cursor, but only containing certain fields from each record; the desired fields should be specified in a block like: [ 1 2 5 ] if I wanted record #, name & phone from each record in the example above. Any speedy suggestions? TIA, Kai

 [2/9] from: tim-johnsons:web at: 29-Nov-2007 9:29


On Thursday 29 November 2007, Kai Peters wrote:
> I need to transform SQL cursors with up to 10,000 records and it needs to > be= fast:
<<quoted lines omitted: 10>>
> [ 1 2 5 ] if I wanted record #, name & phone from each record in the > example above.
Hi Kai: I'm not sure if I fully understand your need, but if you are looking for column values 1, 2 & 5, why not compose your SQL query to those specific columns in that order? as in "select col1,col2,col5 from MyTable"? If I've missed the boat here - sorry! Oh, and what do you mean by 'transform'? regards tim

 [3/9] from: kpeters:otaksoft at: 29-Nov-2007 11:05


Tim ~ I need two sets of data that are guaranteed to be identical (except for the columns I want to lose in the second set) - but querying twice, even if performed one immediately after the other, does not guarantee me identical counts etc. as I am talking to a busy MySQL server. By transform I merely refer to 'losing a set of columns'. Clear as mud? Kai On Thu, 29 Nov 2007 09:29:27 -0900, Tim Johnson wrote:

 [4/9] from: kpeters:otaksoft at: 29-Nov-2007 11:24


> Hi Kai: > I'm not sure if I fully understand your need, but if you are looking for column values 1, 2 & 5, > why not compose your SQL query to those specific columns in that order? as in "select > col1,col2,col5 from MyTable"? If I've missed the boat here - sorry! Oh, and what do you mean by > 'transform'? regards tim
Or - to hopefully make it even more clear: Here's what I have been using: cursor: [ [ 100 "Kai" 49 #764-0929] [ 101 "Zu" 52 #764-0915] [ 102 "Dewi" 16 #312-1772] ] cols: [ 1 2 4 ] remove-columns: function [ cursor [block!] columns [block!]] [ tmp result ] [ result: copy [] foreach record cursor [ tmp: copy [] foreach column columns [ append tmp pick record column ] append/only result tmp ] result ] probe remove-columns cursor cols

 [5/9] from: gregg:pointillistic at: 29-Nov-2007 12:36


Hi Kai, KP> remove-columns: function [ cursor [block!] columns [block!]] [ tmp result ]KP> [ KP> result: copy [] KP> foreach record cursor [ KP> tmp: copy [] KP> foreach column columns [ KP> append tmp pick record column KP> ] KP> append/only result tmp KP> ] KP> result KP> ] And is that not fast enough? The obvious thing to do is preallocate the result block to the correct size (make block! length? cursor) and use INSERT TAIL rather than APPEND. -- Gregg

 [6/9] from: tim-johnsons:web at: 29-Nov-2007 11:04


On Thursday 29 November 2007, Kai Peters wrote:
> > Hi Kai: > > I'm not sure if I fully understand your need, but if you are looking for
<<quoted lines omitted: 13>>
> ] > cols: [ 1 2 4 ]
Aha! 'cursor applies to the entire result set. See comments below
> remove-columns: function [ cursor [block!] columns [block!]] [ tmp result > ]= [ >
;; below could be a time penalty if function in a loop or nested loop
> result: copy [] > foreach record cursor [
;; below could be a time penalty if function in a loop or nested loop
> tmp: copy [] > foreach column columns [
<<quoted lines omitted: 5>>
> ] > probe remove-columns cursor cols
I'd recommend that you 1)try to make a reasonable assumption about amount of memory usage, 2)double that amount :-) 3)create your 'result and 'tmp blocks before iteration starts using 'make instead of 'copy to reserve memory and hopefully prevent resizing - which would be something like a 'realloc() call in the native code 4)pass them as arguments to 'remove-columns 'clear'ing them first.... I don't do a lot of processing of big record sets with rebol, but I would guess that memory allocation and reallocation would be an issue. I hope this adds to what Gregg has provided. Tim

 [7/9] from: kpeters:otaksoft at: 29-Nov-2007 14:38


> And is that not fast enough? The obvious thing to do is preallocate the result block to the > correct size (make block! length? cursor) and use INSERT TAIL rather than APPEND. >
Thanks for your input gentlemen ~ am not saying that it's too slow - but I do know that I can always improve on/speed up my code by asking these questions and looking at how others do things... Kai

 [8/9] from: carl:cybercraft at: 30-Nov-2007 5:42


Hi Kai, Here's a different approach, it duplicating cursor and then removing what's not wanted. Be interested in knowing if it's faster or slower. (Only you will know if deep's required.) ... ------------------------ cursor: [ [100 "Kai" 49 #764-0929] [101 "Zu" 52 #764-0915] [102 "Dewi" 16 #312-1772] ] cols: [1 2 4] remove-columns: function [cursor [block!] columns [block!]][tmp result][ result: copy/deep cursor tmp: copy [] repeat value length? result/1 [ if not find columns value [append tmp value - 1] ] forall result [ foreach column tmp [ remove skip result/1 column ] ] head result ] probe remove-columns cursor cols --------------------- Not much tested, mind! -- Carl Read. On Thursday, 29-Novenber-2007 at 11:24:41 Kai Peters wrote,

 [9/9] from: petr:krenzelok:seznam:cz at: 30-Nov-2007 10:46


absolutly fastest method is remove-each. I start to use it frequently, even if I need to repeat conditions. But I was not successfull in how to do it for your case. remove-each clearly uses not sufficient method of foreach, so your word is not bound to the block (that will be allowed in R3). remove-each column record [column = .....] ... pity index? can't be used... Petr

Notes
  • Quoted lines have been omitted from some messages.
    View the message alone to see the lines that have been omitted