|
Post by Carl Gundel on Jun 30, 2022 10:02:14 GMT -5
Also UBOUND() is pretty common function Except that your program already know the size of the array when it is DIM'd. I guess this function might become useful in the future though depending on other features that get added.
|
|
|
Post by tsh73 on Jun 30, 2022 10:50:37 GMT -5
Yes, it'll get very useful it we are to pass arrays to subs/functions.
|
|
|
Post by Rod on Jul 1, 2022 13:45:24 GMT -5
Or if we selected a subset.
|
|
|
Post by tsh73 on Jul 1, 2022 16:15:30 GMT -5
Subset of array? I think it is called "slice" in some languages Fortran? Python (may be Numpy library)? Really interesting Since I not used much any of such languages I have no idea what good could came out of it. Then again, having new possibility, new uses bound to be discovered.
|
|
|
Post by knoware on Jul 2, 2022 16:21:51 GMT -5
I decided to compare array operations to SQLite I loaded 10000 items, found 1000 items; It took 122 ms to load the array and 2183 ms to load the SQL table. To find 1000 random items took the Array 13623 ms vs 2199 ms with SQL.
I added a couple other searches in SQL 1. Find all items that have a 5 somewhere in the value took 4 ms. 2. Find all items that have a value ending in 3 took 5 ms.
Loads are slower in SQL All finds are faster in SQL. Normally the SQL file is always up to date so there would be no load for SQL.
This program is in RunBasic. Since Liberty uses the same SQL functions, the results in Liberty should be the same.
global #mem dim anum(10000) dim aval$(10000)
dim tval$(10000) dim findVal$(1000) for i = 1 to 1000 ' load array with items to find findVal$(i) = "Val";int(rnd(i) * 10000) next i ' ----------------- Load test values ---------------------- for i = 1 to 10000 fval$ = "Val";int(rnd(i) * 10000) tval$(i) = fval$ next i
sqliteconnect #mem, ":memory:" mem$ = "CREATE TABLE ary(snum,sval)" #mem execute(mem$)
t1 = time$("ms") t1$ = time$() print "================= ARRAY ============================" print "Begin Load array time:";t1$;" ms:";t1 for i = 1 to 10000 anum(i) = i aval$(i) = tval$(i) next i t2 = time$("ms") t2$ = time$() print "End Load array time:";t2$;" ms run:";t2 - t1
print "------------ FIND ARRAY ITEMS ---------------------------------" num2find = 1000 t1 = time$("ms") t1$ = time$() print "Find ";num2find;" random items time:";t1$;" ms:";t1
for i = 1 to num2find fval$ = findVal$(i) for j = 1 to 10000 if aval$(j) = fval$ then exit for next j next i t2 = time$("ms") t2$ = time$() print "End Find ";num2find;" random items time:";t2$;" ms run:";t2 - t1
print print "================= SQL ============================" t1 = time$("ms") t1$ = time$() a$ = "" print "Begin Load SQL time:";t1$;" ms:";t1 for i = 1 to 10000 a$ = a$ + s$;"(";i;",'";tval$(i);"')" s$ = "," next i mem$ = "INSERT INTO ary VALUES";a$ #mem execute(mem$) t2 = time$("ms") t2$ = time$() print "End Load SQL time:";t2$;" ms run:";t2 - t1
print "----------------- FIND SQL ITEMS --------------------------------" t1 = time$("ms") t1$ = time$() print "Find ";num2find;" random items time:";t1$;" ms:";t1 for i = 1 to num2find mem$ = "SELECT * FROM ary WHERE sval = '";findVal$(i);"'" #mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows next i t2 = time$("ms") t2$ = time$() print "End Find ";num2find;" random items time:";t2$;" ms run:";t2 - t1
print print "================= some other sql searches that are possible =============" t1 = time$("ms") t1$ = time$() print "Find all values that has a 5 in the value somewhere:";t1$;" ms:";t1
mem$ = "SELECT count(*) as numFound FROM ary where sval like '%5%'" #mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows
WHILE #mem hasanswer() #row = #mem #nextrow() numFound$ = #row numFound$() print "Found ";numFound$;" values that contain a 5" WEND
t2 = time$("ms") t2$ = time$() print "End Find all values that has a 5 in the value somewhere:";t2$;" ms run:";t2 - t1
' ---- values that end in 3 t1 = time$("ms") t1$ = time$() print "Find all values that end with 3:";t1$;" ms:";t1
mem$ = "SELECT count(*) as numFound FROM ary where sval like '%3'" #mem execute(mem$) rows = #mem ROWCOUNT() 'Get the number of rows
WHILE #mem hasanswer() #row = #mem #nextrow() numFound$ = #row numFound$() print "Found ";numFound$;" values that contain a 5" WEND t2 = time$("ms") t2$ = time$() print "End Find all values end with 3:";t2$;" ms run:";t2 - t1
end
Output from program; ================= ARRAY ============================ Begin Load array time:14:04:37 ms:3834248677205 End Load array time:14:04:37 ms run:122 ------------ FIND ARRAY ITEMS --------------------------------- Find 1000 random items time:14:04:37 ms:3834248677328 End Find 1000 random items time:14:04:50 ms run:13623
================= SQL ============================ Begin Load SQL time:14:04:50 ms:3834248690951 End Load SQL time:14:04:53 ms run:2183 ----------------- FIND SQL ITEMS -------------------------------- Find 1000 random items time:14:04:53 ms:3834248693134 End Find 1000 random items time:14:04:55 ms run:2199
================= some other sql searches that are possible ============= Find all values that has a 5 in the value somewhere:14:04:55 ms:3834248695334 Found 3449 values that contain a 5 End Find all values that has a 5 in the value somewhere:14:04:55 ms run:4 Find all values that end with 3:14:04:55 ms:3834248695338 Found 968 values that contain a 5 End Find all values end with 3:14:04:55 ms run:3
|
|
|
Post by Rod on Jul 3, 2022 2:37:42 GMT -5
Fast indeed. If I didn’t need to learn SQL to achieve that it would seem to be the solution. But we have open() we have sort() adding a native select() and sum() would transform what we can do with an array(). Over and above we have the simpler matrix maths need.. a()=b()*c() etc.
We have been shown SQL solutions before, they are so good I always wonder why we don’t see wider use within this community and work rounds to make it happen. We have work rounds for the blitter, the mmi, and some networking/socket activity but not SQL.
|
|
|
Post by knoware on Jul 3, 2022 4:43:18 GMT -5
If you had a table called tbl with i, a, and b you could SELECT a * b FROM tbl etc
If you wantd to restrict it to a certain i value you could SELECT a * b WHERE i = 10. Or a range where i is 20 to 30. SELECT a * b FROM tbl WHERE i > 19 and <= 30. Or if you have conditions for example only if b is greater than a and a has a 5 in the value somewhere. SELECT a * b FROM tbl WHERE b > a AND a like '%5%' OR only on duplicate b.. SELECT a * b FROM tbl JOIN tbl as tblb ON tblb.b = tbl.b and rowid(tblb) <> rowid(tbl) - sum up all a * b. SELECT sum(a * b) FROM tbl. - average a * b .. SELECT avg(a * b) FROM tbl. - find a * b where a is in a list .. SELECT *,a * b FROM tbl WHERE a IN(123,234,987,678) - find the largest a * b.. SELECT max(a*b) FROM tbl. - find the largest a * b times the smallest a * b.. SELECT max(a * b) * min(a * b) FROM tbl. - find a * b but only if the number a begins with a 3 and b ends in a 4.. SELECT a*b FROM tbl WHERE a LIKE '3%' and b LIKE '%4' - display data sorted by a * b.. SELECT a * b FROM tbl ORDER BY a * b. OR if you wanted to see i,a,b, a*b sorted by a * b reversed.. SELECT *, a * b FROM table ORDER BY a * b desc - Find the slope of a and b for each unique a SELECT *,(COUNT(b) * SUM(a * b) - ( SUM(a) * SUM(b) ) ) / ((COUNT(b) * SUM(a * a))- (SUM(a) * SUM(a))) AS slope FROM tbl GROUP BY a
Typically I have found doing math in SQL is faster than arrays with lots more options. Ya. I know we can always find exceptions both ways.
|
|
|
Post by Rod on Jul 3, 2022 10:12:33 GMT -5
Cool, but it isn’t BASIC. It is powerful and fully featured and undoubtably fast but it is another language.
Certainly Carl has long yearned for SQL functionality. My needs are simpler.
|
|
|
Post by Walt Decker on Jul 3, 2022 17:17:42 GMT -5
No. In LB A() and A are two different critters. A() = B() * C() is matrix multiplication and is not equivalent to A = B * C. However, one could make a distinction by:
MAT A() = B() * C() where C() is a square matrix and B() has the same number of outer dimensions as C() has rows and MAT indicates a matrix operation
TBL(Filename) A() = B() * C() where TBL indicates a table of some kind, probably a file with one field.
|
|
|
Post by knoware on Jul 3, 2022 21:22:03 GMT -5
Lets assume you have an array as follows: dim aa(10) dim bb(10)
----------- ARRAY ---------------- Load the array: For i = 1 to 10 aa(i) = i * 123 bb(i) = i * 456 next i ------ SUM ----- To sum up x = aa() * bb() you do something like this for i = 1 to 10 x = x + aa(i) * bb(i) next i
----------- SQLite --------------- To do the exact same thing in SQLite you create a table. You can think of it as a file. To create an in memory table called ary with two fields called aa and bb do the following; sqliteconnect #mem, ":memory:" mem$ = "CREATE TABLE ary(aa,bb)" #mem execute(mem$) --------------------------------- Load the table called ary. This could be done with one statement. For comarison we load each entry with a for loop. for i = 1 to 10 a = i * 123 b = i * 456 mem$ = "INSERT INTO ary VALUES(";a;",";b;")" #mem execute(mem$) next i The table ary now has 10 entries with the exact same values as the arrays aa() and bb() ------ SUM ----- Sum up all the 10 values of aa times bb mem$ = "SELECT sum(aa * bb) FROM ary" Since we no not have a WHERE clause in the SELECT statement it reads each of the 10 entries in the ary table and sums up aa * bb..
|
|
|
Post by Walt Decker on Jul 4, 2022 17:03:05 GMT -5
Why would anyone want to do that? One can just:DIM A(9, 1)
FOR I = 0 TO 9 A(I, 0) = INT(RND(0) * 10000) A(I, 1) = INT(RND(0) * 10000) NEXT I
Anum.1 = 0
FOR I = 0 TO 9 Anum.1 = Anum.1 + (A(I, 0) * A(I, 1)) NEXT I
PRINT Anum.1
In fact, one could get the value if Anum.1 while the values are loaded into the array which will reduce the code by one loop
|
|
|
Post by Rod on Jul 5, 2022 11:28:25 GMT -5
SQL is fab, but I would really like to get back to discussing how we might improve array() operations.
For me an array would be far more useful if we could load it from file, copy it, perform maths across the entire array, select a new array out of the main array and be able to sum it. All in addition to sorting it. Sorting could be improved by allowing multiple column sorting.
Do I care about local arrays, no. I have lived with global long enough to know it does not matter that much.
So why do I want load from file? It is a pain getting data loaded to an array right now. Also slow.
Struct/Mixing data types, numeric and string. Way too complex, I have been used to segregating the data to all numeric or mixed string and string numeric so don’t see much needs to change.
Sorting is a very powerful tool. We have a great single column sort. It would be greatly enhanced if it could take a list of prioritised columns to sort on. So sort on second name , first name gets us all the smiths in first name order. Powerful and hard to do currently unless you create an index column and fill it. Slow!
Maths, for numeric arrays it would be great to be able to add divide multiply or whatever between one array and the next. Instantly all elements no need for a loop.
Now select, being able to select out a conditional set from an array is the next most powerful thing to sorting if not more powerful. Being able to process a selection or create a new selection is massively powerful. Selecting records between dates for example gets your monthly statement.
And it goes on. So more support on array handling improvement and less on SQl would be good.
|
|
|
Post by Walt Decker on Jul 5, 2022 13:17:27 GMT -5
Local arrays are very advantageous. They save memory data space because they are only within the page space of the sub/function and used within that sub/function (or within the sub/function to which a reference is passed) and destroyed when the sub/function is exited.
That is where DEF STRUCT Prototype would come in handy. One could create an array of the prototype and store or retrieve the entire array at one shot. Also makes it easy to sort on one or more fields in the array regardless of whether the field is numeric or character.
Those matrix operations can be implemented in native LB, but they are slow and almost useless in LB. However, if the loops are compiled, they become much more useful.
This is where local arrays would come in handy.
I have a dll that will do that.
|
|
|
Post by tsh73 on Jul 5, 2022 16:54:17 GMT -5
Changing sorting algorhithm to stable one en.wikipedia.org/wiki/Sorting_algorithm#Stabilitywill allow to get effect of sort by several columns by making successing sorts by different single column. Probably saving/loading arrays to/from string in some binary form could be useful? This way they could be passed to DLLs for faster processing Sorry this does't compute. "You having no use to local arrays" does not leads to "everyone have no use".
|
|
|
Post by Rod on Jul 6, 2022 7:05:59 GMT -5
Ok, local arrays might be useful, I should have said "if breaking the global model makes it all too difficult then please just give us some of this functionality in global arrays."
|
|