Search a multidimensional array

Bob Sneidar bobsneidar at iotecdigital.com
Fri Apr 20 14:48:35 EDT 2018


Andrew, you don't peruse this list much, do you? :-)

A recent thread has been going on about just this issue. The upshot is that one pass through the array is obviously needed for the sqLite conversion, but after that multiple queries can be done much more efficiently. I have code already written that converts an array to a memory sqLite database. Once you do that, you can index on the columns you query most often. Contact me off list and I will send it to you. 

As an aside, why not just use an sqLite database out of the gate? And to the rest of the list, there should probably be a way to import a file based sqlite database into a memory database and then back again for speed. 

Bob S


> On Apr 20, 2018, at 11:32 , Andrew Bell via use-livecode <use-livecode at lists.runrev.com> wrote:
> 
> Is there a quick way to search a large multidimensional array that I am missing? I'm working on an inventory system and trying to figure out some more efficient methods.
> 
> Currently I'm taking a tab-delimited spreadsheet provided by the client and converting it to an array, but there are currently > 48000 keys in the array so my repeat loop for searching is taking several minutes. I quickly figured out by making the barcode (unique value) the primary key of the array cut down on a simple SKU search, but I'm trying to also search based on other values (like the item description).
> 
> 
> A line of sample data looks like this:
> 66290	PHOTO, Early to Mid 1960's, Womens Hair Style, 27x21"	Blue Background w/ White Vine Edging, Gold Frame	1	$200.00
> 
> 
> An item in the array looks like this:
> tInventoryArray[66290]["barcode"]
> tInventoryArray[66290]["description"]
> tInventoryArray[66290]["details"]
> tInventoryArray[66290]["qty"]
> tInventoryArray[66290]["cost"]
> 
> 
> My slow, albeit working, search code looks like this:
> repeat for each element tThisItem in tInventoryArray
>      if tThisItem["description"] contains tSearchQuery then
>         put tThisItem into tSortedInventoryArray[(the number of elements of tSortedInventoryArray) + 1]
>      end if
> end repeat
> 
> 
> This does work, but is taking almost 2 minutes to search through the 48000+ item database. Can someone point out a flaw in my process? My next experiment is converting this array to a SQLlite database and just throwing SELECT * WHERE commands at it.
> 
> --Andrew Bell





More information about the Use-livecode mailing list