Fastest and efficient way to determine combination of items

[MDCT]

Hello again, guys and gals..

I'm having problem that I need smarter people to help me.

Basically, I want to find the fastest and efficient way for the script to process the data.

Let's say I have 8 items in a clothing store's computer.

A=Hats

B=Shirts

C=Pants

D=Skirts

E=Jeans

F=Shorts

G=Jacket

H=Shoes

And here's the .ini:

[Day01]

Order=Mr. X

Brands=Lion,,Dog,Cat,Cow,,,Aligator

It means that Mr. X orders:

Hats with brand Lion, no Shirts, Pants with brand Dog, Skirts with brand Cat, Jeans with brand Cow, no Shorts, no Jacket, and Shoes with brand Aligator.

The question is what is the fastest or efficient way for the script to process this information?

I could simply put it in an array using StringSplit and let it check whether the array is empty or not, but it means it would need 8 loops each time to process. I want to cut the process so the loop is minimized.

I was thinking to make it in a binary combination.

http://www.mathsisfun.com/binary-combinations.html

When the data is saved, the script will check what items are ordered and what not (loop for checking in here is okay), then it will make a code. So, the script will know what to process, without doing any unnecessary loop.

Ex: let's say I have only 2 items, so the combination will be 4.

A B Code

0 0 1

0 1 2

1 0 3

1 1 4

If I got order for item A only, the code is 3. If only item B, the code is 2. If both A and B, the code is 4. Thus the script set an additional key in the .ini for the code. And the script could process efficiently by looking that code. But with 8 items, it would be 256 combinations. And should the items be added, it would be too much.

Is there any other way to do this efficiently or better?

Any help would be appreciated.

Thank you.

[JohnOne]

I'm not sure where you get the idea that there would need to be 8 loops of the array.

Here is a basic example to look at.

Test.ini

[items]
A=Hats
B=Shirts
C=Pants
D=Skirts
E=Jeans
F=Shorts
G=Jacket
H=Shoes

[Day01]
Order=Mr. X
Brands=Lion,,Dog,Cat,Cow,,,Aligator

$s_ini = @ScriptDir & '\Test.ini'

$a_2D_Items = IniReadSection($s_ini, 'items')

$s_Customer = IniRead($s_ini, 'Day01', 'Order', 'Unknown')

$s_Brands = IniRead($s_ini, 'Day01', 'Brands', 'Unknown')

$a_Brands = StringSplit($s_Brands,',')

For $i = 1 To 8
    ConsoleWrite($s_Customer & " ordered ")
    If Not $a_Brands[$i] Then
        ConsoleWrite('0 ' & $a_2D_Items[$i][1] & @LF)
        ContinueLoop
    EndIf
    ConsoleWrite($a_2D_Items[$i][1] & ' with ' & $a_Brands[$i] & ' Brand' & @LF)
Next

[kylomas]

MDCT,

Have you considered an SQLite solution?

kylomas

edit:

The following is an example of SQLite usage. It has nothing to do with your problem, it is simply an example of SQLite syntax, usage and speed, at a very simple level.

expandcollapse popup

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
; *** End added by AutoIt3Wrapper ***

#include <sqlite.au3>

#AutoIt3Wrapper_Add_Constants=n

local $Parts_fl = @scriptdir & '\Parts.txt'
local $Parts_DB = @scriptdir & '\Parts.DB3'

;--------------------------------------------------------------------------------------------------------
; generate test file of 5,000 comma delimited items, 3 entries (cols) per line (row) if $refresh is true
;--------------------------------------------------------------------------------------------------------

local $refresh = true

if $refresh then filedelete($Parts_fl)

if not fileexists($Parts_fl) then

    local $hfl = fileopen($Parts_fl,2)
    if $hfl = -1 then
        ConsoleWrite('File open failed' & @LF)
        Exit
    endif

    local $str_out

    for $1 = 1 to 5000
        $str_out &= stringformat('%05i,M%05s,V%05s\n',$1,$1 & '-' & random(1,999,1),$1 & '-' & random(1,999,1))
    Next

    filewrite($hfl,$str_out)
    fileclose($hfl)
    $hfl = 0

endif

;---------------------------------------------------------------------------------------
; initialize SQLite and open Parts DB
;---------------------------------------------------------------------------------------

local $sqlstrt = _SQLite_Startup(), $st = timerinit()

if @error then
    ConsoleWrite('error loading sqlite.dll' & @LF)
    Exit
EndIf

local $hmemDB = _sqlite_open($Parts_DB)

if @error then
    ConsoleWrite('Unable to open DB' & @LF)
    _Exit()
EndIf

if $refresh then _reload()

func _reload()

    ; drop "parts" table if it exists, re-define and reload it

    if _sqlite_exec(-1,'drop table if exists parts;') <> $sqlite_ok then
        ConsoleWrite('Drop table failed' & @LF)
        _exit()
    Else
        ConsoleWrite('Parts table dropped for refresh' & @LF)
    endif

    if _sqlite_exec(-1,'create table parts (SKU, Model, Version);') <> $sqlite_ok then
        ConsoleWrite('Create Table Failed' & @LF)
        _exit()
    endif

    local $fl_array
    _filereadtoarray($Parts_fl,$fl_array)

    switch @error
        case 1
            ConsoleWrite('Input file failed to open' & @LF)
            _exit()
        case 2
            ConsoleWrite('Unable to split file' & @LF)
            _exit()
    EndSwitch

    local $aLine, $sql

    ProgressOn('Loading Parts Table','Please Wait')

    _SQLite_Exec(-1, "begin immediate;")

    for $1 = 1 to $fl_array[0]
        progressset(($1/$fl_array[0])*100)
        $aLine = stringsplit($fl_array[$1],',')
        $sql = 'insert into parts values ('
        for $2 = 1 to $aLine[0]
            $sql &= '"' & $aLine[$2] & '",'
        next
        $sql = stringtrimright($sql,1)
        $sql &= ');'

        if _sqlite_exec(-1,$sql) <> $sqlite_ok Then
            ConsoleWrite('Table insert failed STMT = ' & $sql & @LF)
            _exit()
        endif
    next

    _SQLite_Exec(-1, "commit;")

    progressoff()

    ConsoleWrite('Table loaded with ' & ubound($fl_array)- 1 & ' records in ' & round(timerdiff($st)/1000,3) & ' seconds' & @LF)

endfunc

;---------------------------------------------------------------------------------------
; display SKU query dialaog
;---------------------------------------------------------------------------------------

local $gui010 = guicreate('SKU Query Mini-APP Using SQLITE',300,170)
local $aSize  = wingetclientsize($gui010)
                guictrlcreatelabel('Enter SKU for Query',40,20,150,20)
                guictrlsetfont(-1,10,600)
                GUICtrlSetColor(-1,0xaa0000)
local $inp010 = guictrlcreateinput('',190,20,40,20)
local $lbl010 = guictrlcreatelabel('',70,60,250,50)
                guictrlsetfont(-1,10,600,-1,'Courier New')
                guictrlsetcolor(-1,0x000099)
local $btn010 = guictrlcreatebutton('Submit Query',10,$aSize[1]-30,$aSize[0]-20,20)
                guictrlsetfont(-1,9,600)
local $dmy010 = GUICtrlCreateDummy()
                guisetstate()

local $aAccelKeys[1][2] = [["{ENTER}", $dmy010]]
GUISetAccelerators($aAccelKeys)

local $aRow, $ret

while 1
    switch guigetmsg()
        case $gui_event_close
            _exit()
        case $btn010, $dmy010
            _disp()
    endswitch
wend

func _disp()

    $ret = _SQLite_QuerySingleRow(-1,'select SKU,Model,Version from parts where SKU = "' & stringformat('%05s',guictrlread($inp010)) & '";', $aRow)
    if $ret = $sqlite_ok then
        guictrlsetdata($lbl010,stringformat('%-10s%5s\n%-10s%5s\n%-10s%5s','SKU',$aRow[0],'Model',$aRow[1],'Version',$aRow[2]))
    Else
        guictrlsetdata($lbl010,guictrlread($inp010) & ' Not Found')
    endif
    guictrlsetstate($inp010,$gui_focus)

endfunc

func _exit()
    _SQLite_Close()
    _SQLite_Shutdown()
    exit
endfunc

Edited April 8, 2013 by kylomas

[kylomas]

MDCT,

Consider this:

Mr A orders stuff on 3/3/2103

Mr B orders stuff on 3/3/2013

Mr A orders additional stuff on 3/3/2013

Mr B orders more of the same stuff he ordered earlier 0n 3/3/2013

Mr A orders stuff on 3/4/2013

For an INI file to handle this each section ("order") needs to be unique. So what you are calling "[Day01]" really needs to be something like "[YYYMMDD-Name-SEQNO]", or, "[YYYYMMDDHHMMSS]".

If you tried to do something like:

[Day01]

Order=Mr. X

Brands=Lion,,Dog,Cat,Cow,,,Aligator

Order=Mr. Y

Brands=Lion,,,,,,,Aligator

"Order" and "Brand" for Mr. Y would overlay the same values for Mr.X (keys must be unique).

Speed of processing is not your major issue, you might want to give more thought to overall design.

kylomas

Edited April 8, 2013 by kylomas

[Mechaflash]

I second using SQLITE as opposed to loading data into an ini file. It'll be much easier to maintain and less headaches to code. Then you can also setup inventory and client tracking methods.

Edited April 8, 2013 by Mechaflash

[MDCT]

Thanks for trying to help, guys. Really appreciate it.

I'm not sure where you get the idea that there would need to be 8 loops of the array.

Here is a basic example to look at.

Hi, JohnOne.

I'm sure I was not clear with my explanation, never really good at explaining things. I will try to explain again, hope it will give better view.

From your example:

The FOR is for looping 8 times with IF to check whether the item is ordered or not.

What I'm looking for is a way to process only the ordered ones so the script won't do any unnecessary loop for IFs to process the unordered ones.

My only solution is by using binary combination. Basically it works by setting predetermined code for all available combination so the script will process immediately without using IFs...but by using CASE.

Test.ini:

[Day01]

Order=Mr. X

$s_ini = @ScriptDir & '\Test.ini'

$answer = InputBox("Question", "What to buy? If only Hat, please erase 'Dog'. If only Shirt, please erase 'Lion'", "Lion,Dog", "", - 1, -1)

IniWrite($s_ini, "Day01", "Brands", $answer)
$a_Brands = StringSplit($answer,',')
$Code=0
For $i = 1 To 2
If $a_Brands[$i] Then
$Code+=$i
EndIf
Next
;~ ConsoleWrite($code)
IniWrite($s_ini, "Day01", "BrandsCode", $code)

;Before this, the script can be slow with lots of loop if necessary.
;After this is a different application to view the data and it has to be fast.
;----------------------------------

;~ $s_ini = @ScriptDir & '\Test.ini'
$s_Brands_code = IniRead($s_ini, 'Day01', 'BrandsCode', 'Unknown')

Switch $s_Brands_code
case 1
$Brands=IniRead($s_ini, 'Day01', 'Brands','Unknown')
$Brands=StringSplit($Brands,',')
ConsoleWrite("Mr. X bought Hat with brand:" & $Brands[1] & @LF)
case 2
$Brands=IniRead($s_ini, 'Day01', 'Brands','Unknown')
$Brands=StringSplit($Brands,',')
ConsoleWrite("Mr. X bought Shirt with brand:" & $Brands[2] & @LF)
case 3
$Brands=IniRead($s_ini, 'Day01', 'Brands','Unknown')
$Brands=StringSplit($Brands,',')
ConsoleWrite("Mr. X bought Hat with brand:" & $Brands[1]&" and also Shirt with brand:"& $Brands[2] & @LF)
EndSwitch

As you can see the script is much faster as it doesn't have to loop to check, but it has more writing.

So, I'm looking for other solution if possible.

Thank you.

MDCT,

Have you considered an SQLite solution?

The following is an example of SQLite usage. It has nothing to do with your problem, it is simply an example of SQLite syntax, usage and speed, at a very simple level.

Hi kylomas, I have never tried SQLite before, you are right, it looks simple. But, I think the solution that I'm looking for can be used when creating applications not just database, so it can be a learning experience. If I cannot find solution of what I'm looking for, maybe SQLite is the way to go.

Thank you for the info.

Edit:

Whoops, I was still preparing and writing the reply. Didn't know you guys were replying too.

Hmm.. Yes, it seems SQLite is a better solution for my current problem. The thing that interest me is what Mechaflash said: "It'll be much easier to maintain and less headaches to code. Then you can also setup inventory and client tracking methods." So in the future, I do not have any problem with my flawed program design. Thanks guys for helping out. Especially kylomas.

Edited April 8, 2013 by MDCT

[kylomas]

MDCT,

Did you read post #4?

kylomas

[MDCT]

MDCT,

Did you read post #4?

kylomas

Yes, just replied to it by using edit, a bit slow. Thanks alot, kylomas.

[kylomas]

MDCT,

Excellent, and, I understand that you are learning (as are we all)...you will find excellent support on this forum!

kylomas

[Spiff59]

give more thought to overall design.

I think that's the most pertinent statement in this thread so far.

Define precisely what data needs to be retained, in what format, for how long, handlie all possible conditions, and then decide upon your best path.

Employing SQLite is adding a huge amount of overhead, sapping far more system resources, executing vastly more instructions, and destroying any portability when compared to a simple in-line coding solution. Do you intend to maintain massive amounts of data? Will you be searching the data using different keys? I'm not sure I'd jump one way or the other until you'd better defined your requirements as kylomas advised.

[MDCT]

Employing SQLite is adding a huge amount of overhead, sapping far more system resources, executing vastly more instructions, and destroying any portability when compared to a simple in-line coding solution. Do you intend to maintain massive amounts of data? Will you be searching the data using different keys?

Actually I'm helping my friend who owns a small shop. Now he has variety of 8 items. He's not sure whether when or if the varieties will be added or removed it depends on the sales. He's planning to use pentium 2 for this purpose. And that's why I wanted to find the efficient and fastest way for the script to process the data.

Now the things that you said regarding SQLite what worry me the most. If SQLite is heavier on system RAM and CPU, I don't think SQLite is the way to go. However, later in the evening, I will make a few tests on my friend's computer. If indeed SQLite is too heavy for his system, I will use the IF loop like what JohnOne wrote, as I have no better solution than binary combination which needs too much writings for each of the combinations of 8 items.

Thank you for the info, Spiff59.

[kylomas]

MDCT,

The code in post #3 should run on any platform with AutoIT installed.

If you go with a INI solution you still have the issues I outlined in post #4.

Either way, you know where we are.

Good Luck,

kylomas

[MaximusNeo701]

Actually I'm helping my friend who owns a small shop. Now he has variety of 8 items. He's not sure whether when or if the varieties will be added or removed it depends on the sales. He's planning to use pentium 2 for this purpose. And that's why I wanted to find the efficient and fastest way for the script to process the data.

Now the things that you said regarding SQLite what worry me the most. If SQLite is heavier on system RAM and CPU, I don't think SQLite is the way to go. However, later in the evening, I will make a few tests on my friend's computer. If indeed SQLite is too heavy for his system, I will use the IF loop like what JohnOne wrote, as I have no better solution than binary combination which needs too much writings for each of the combinations of 8 items.

Thank you for the info, Spiff59.

SQLite is not heavy on system resources, and could be good for long term but a Pentium 2 maybe not enough especially considering the OS is most likely very old as well. But you could forgo using SQLLite and just store data in a flat file in a sql like setup. I am not sure if Autoit supports this but some languages let you query a flat file if you open it as a stream and you could easily do your operations that way.

Also is there a reason you are set on an ini file? Forward thinking I would consider using a a flatfile in a sql like way, make it a CSV with terms store in quotes.

"Date","OrderNumber","CutomerName","Hats", "Shirts", "wallets", etc

"2013-4-09", "Mr. X", "1", "Lion, Zebra, Baseball", "AE, Diesel", "", .....

This setup allows you to process line by line until you hit he date you want, then you can parse the record when you hit the date you want. Also the read operation compared to arithmetic operations is slow so reading in and entire record as 1 line vs reading in the multi-line setup is a big difference, read operations are always slower. The Quotes allow you to store many comma separated values in each field. So in the example record 3 brands where purchased that were all hats separated, by commas, and using a string split operation you can get this info. Also you can always ad more brands at any point in time just by entering them into a sale, and they will be spit back out later when you look up the sale without change to the processing of the software. Provided the front end for entering and reading sales is setup appropriately. This is very similar to how SQLLite is going to store the data anyways.

My advice store it as plain text, do not binary encode it. I worked with a vehicle recall data base with 100,000's of records, large records by the way, and we loaded it into memory cause it would only taking up 50 megabytes of memory. Plus you would keep having to decoding and recoding it, why do this unless you are trying to encrypt it? Now I will say you are going to need a backup system for your ini or flat file setup which will not be as easy or elegant as a SQLLite solution.

Also in the future don't treat loops as a bad thing, programmers like recursion because its easy to maintain and code. Doing it in 1 loop may seem faster but with the more complex arithmetic it may not always be. I will say a binary conversion like you have setup will most likely never be the fastest way or all Databases would work like this by default. Also you need to consider that most likely you won't ever want all of that data at once, you will want to add a record every time there is an order and be able to retrieve certain records based on parameters for user input. The output you are going to want to be able to step through every single record and find all of the ones you matching those parameters and out put them, or dump them into a report file, so having that ability to step through every line and check 1 parameter in the line is to your benefit.

EDIT: The rest of this talks about when you would use your idea and is more in depth on processing speed so its not necessary that you read but some may find it interesting

As a programmer I can elaborate on the binary combination setup you have come up with, and it is smart, only if you have to deal with limitations in bandwidth in a super high processing power system (clusters). Storage is cheap, processing power is not!!! In the real world we store records in plain text like this and it uses very little space and is very fast to read through. But doing operations on data especially when you get into the millions of records category is not so good. Most work I have done where we automate data processing we will take in the data in these coded ways like a sql record coming back with numbers representing strings in another table and convert them to strings and store the strings in the database. We convert the data 1 time when it comes in; then when people query the data potentially 1000's of times later its not reprocessed each time just returned, though usually our operations would occur at a linear rate and not exponential so it wasn't the end of the world if we did leave them in these cryptic systems and had it decoded at run time. Clean code is the best and refactoring into reusable functions you can use through recursion is what you want.

Now since you came up with this binary combination I can tell you what it would actually be used on but I wouldn't bother reading it unless you are really nerdy; besides some sort of very specific compression algorithm. In a cluster environment sending data between nodes is the slowest part of the operation, network bandwidth is low, latency, verification, checksum, and much more. If you did it wrong and tried to send data back and forth between nodes my program could actually finish its entire execution while yours is still sending out data. So what we try to do is break the problem down where the nodes can count off their numbers really quick and create their own workload without one master node sending out data. Now most people are not aware of this but an actual bool is not a bit in most languages, so even trying to create an array of bools was still a big no no. So you want to use a bit string, and have each server do it's own workload, then submit it's results and if you are smart, submit in a way that would also not require communication between nodes. But if you had to the goal was to minimize the amount of data sent. This was mainly due to latency more than bandwidth, these nodes were gigabit connected nodes but still the latency between them was the main slow down. The project we were working on was finding all of the prime numbers from 0 to 9 * 10 ^ 12. Extremely large data sets can benefit from a binary combination when memory is an issue with huge sets and reading a full byte vs a bit does add a factor of N to your processing time. Its mostly used to find ways to compress lots of information down for data transmission, not storage. Possibly also encryption if you would consider your system a key.

Edited April 9, 2013 by MaximusNeo701

[jchd]

@MDCT,

It is important that people get useful help, pertinent advises and true facts on a help forum like this one. Hence I feel the need to post a rebutal to every untrue statements made by Spiff59.

• SQLite has been especially built as an embedded database engine, i.e. as an application data storage engine.
• SQLite name comes from SQL and light (or lite). That means it's ... well, lite! A Pentium II is overkill to run it.
• SQLite is one of the most portable piece of software ever written and has been known to run on almost every hardware/software platform combination you can dream of.
• There are good reasons why SQLite is by far the most used DB engine ever created.
• SQLite3 database files are also 100% OS independant: take the DB off your Androïd smartphone, copy it to your Mac, merge it with data extracted from a GPS on an IBM mainframe, send the resulting DB to a Linux box or a stock PC, copy it to your iPhone. All of this without any form of conversion.
• SQLite can efficiently manage very small databases as well as huge monsters (> 100 Tb).
• SQLite databases can be entirely protected by strong encryption if needed.
• SQLite is easy to use with AutoIt.
• I've already proven several times on this forum that an SQLite-based solution can often outperform "handcrafted flat files" solutions on all counts: speed, reliability, portability, flexibility and growth.

The storage format in your first post doesn't hold water.

It is to be expected that a business will succeed and grow over time. Thus the needs of your friend will also grow, hopefully.

That your friend only stocks 8 items for now (no sizes, no colors?) doesn't mean he won't start offering many more tomorrow, like a range of accessories. At the very least a decent back-office application needs to store items catalog with references list prices and inventory, customers details, individual sales with full detail (e.g. rebates, shipping cost), payments, returns, defective items, etc.

As others have said, think twice before investing time in coding something that may turn obsolete too fast. If you want to actually help your friend, determine the right balance between an overly complex design and a too simple naïve approach that won't grow.

[kylomas]

MaximusNeo701,

Cogent argument, an excellent middle ground if the OP does not want to use SQLite (not advised for exactly the reasons that JCHD sited).

do not binary encode it.

The OP is referring to setting binary switches to use as comparison. Not compression or encoding of any sort.

kylomas

[Spiff59]

It is important that people get useful help, pertinent advises and true facts on a help forum like this one. Hence I feel the need to post a rebutal to every untrue statements made by Spiff59.

Where did that come from?

You think I'd issued some global condemnation of relational databases.

I made my bread-and-butter for a few decades off of the most-used DB engine ever created (IBM DB2) and understand the strengths they offer.

Sometimes you don't need a flamethrower to kill a gnat.

What I would think were important would be for one given MVP status not to conduct unwarranted personal attacks on other users.

[jchd]

Employing SQLite is adding a huge amount of overhead, sapping far more system resources, executing vastly more instructions, and destroying any portability when compared to a simple in-line coding solution.

Every point about SQLite specifically quoted here is simply untrue. My rebutal is nowhere close to a personal attack, I simply firmly point out plain wrong statements.

You certainly aren't a gnat and I didn't use any kind of flamethrower.

BTW, DB2 (which I happen to have used a lot too) is obviously not the most-used DB engine. SQLite is and by far. If you restrict your statements to mainframes, then yes DB2 should come out ahead.

[Mechaflash]

With the added knowledge that his friend has a store that this is going to be used for, deploying a true database will be the safest route for expansion in the future. He could get by with ini files or even using excel sheets as a DB, but if the store begins to expand (which it should... if it ain't expanding it's shrinking...), he's going to want a system that can be better managed.

[Spiff59]

Every point about SQLite specifically quoted here is simply untrue.

I don't know how to debate what appear to be irrational stances.

That unnecessarily installing a DB does not create bloat.

Or, that adding an OS-specific DLL, registry entries, and an ODBC/OLEDB driver or COM wrapper does not impair portability.

Maybe I'm a bit sensitive, I took you labelling my post as unhelpful, irrelevent, and entirely fallacious, as being somewhat personal.

[MaximusNeo701]

MaximusNeo701,

Cogent argument, an excellent middle ground if the OP does not want to use SQLite (not advised for exactly the reasons that JCHD sited).

The OP is referring to setting binary switches to use as comparison. Not compression or encoding of any sort.

kylomas

Ah makes more sense now that I think about it, since he did already show how he is going to store the data in the ini. I still think the over for that kind of setup will far out weight the use of simpler logic and loops though for any number of records he is talking about. Also much easier to maintain code as well and add new items I would think and code maintenance is where it usually bites people.

Also JCHD, I am aware that a pentium 2 could run SQL Lite but you really think its over kill?

Also I agree sql is very easy to use with autoit if you know how to write queries and recordsets. Which is another option you could just use a recordset and save it to a file and reopen it, though you run the risk of data being lost if the script crashes if error handling is not done correctly.