Re: [Ann] xaLib - library to extract data from an array.

[Bob Sneidar via use-livecode]

Heh heh. I wasn't asking you to actually USE convert to sqlite memory in your 
library. I was just saying that for a few xqueries yours a really useful 
library, but for lots of queries against a large array, the performance of 
first converting to an sqlite memory database and then querying that and 
converting back to array would likely be noticibly better performance wise. 

I already wrote code and posted it for converting a numbered array to a sqlite 
memory database so let me know if you didn't get that so you don't have to 
rewrite it all. 

Bob S


> On Apr 18, 2018, at 15:20 , Alex Tweedly via use-livecode 
>  wrote:
> 
> Well, it's only a case of "I expect ..." so we shouldn't trust my intuition 
> until we've benchmarked it a bit :-)
> 
> Having said that - we will still be using script to iterate over the 
> keys/elements of the array, so there is likely to be some cost there - even 
> before we allow sqlite to index some columns.
> 
> I've just started looking at it from the performance point of view, and 
> reduced the time for the simple case in the included test stack by 95% so it 
> doesn't feel so bad now  but I'll need to completely rewrite the main 
> functions/handlers :-(
> 
> I'll try to import the data into an sqlite DB and compare against that too - 
> will report with some numbers tomorrow (and come up with a more reasonable 
> test case.
> 
> Alex.


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: [Ann] xaLib - library to extract data from an array.

[Alex Tweedly via use-livecode]

Well, it's only a case of "I expect ..." so we shouldn't trust my 
intuition until we've benchmarked it a bit :-)


Having said that - we will still be using script to iterate over the 
keys/elements of the array, so there is likely to be some cost there - 
even before we allow sqlite to index some columns.


I've just started looking at it from the performance point of view, and 
reduced the time for the simple case in the included test stack by 95% 
so it doesn't feel so bad now  but I'll need to completely rewrite 
the main functions/handlers :-(


I'll try to import the data into an sqlite DB and compare against that 
too - will report with some numbers tomorrow (and come up with a more 
reasonable test case.


Alex.


On 18/04/2018 18:08, Richard Gaskin via use-livecode wrote:

Alex Tweedly wrote:

> I expect the searches etc. would be faster with SQL / memory database

Both use hashed lookups to find data.  What would be needed to bring 
LC array queries on par with SQLite?





___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: [Ann] xaLib - library to extract data from an array.

[Bob Sneidar via use-livecode]

I was thinking about that. Depends on what you mean by par. If you are not 
indexing array values, then you will have to touch every array element each 
time you query. It's back to one pass sql conversion then, and you would get 
all the benefits of sql in the bargain. The only time an array query api comes 
in handy is if you are only trying to filter an array for a key:value a few 
times. If you have to do this many times, conversion to a memory based sqlite 
database could really pay dividends for a large array, especially if you took 
the time to index the columns that mattered most. 

I'm going to put together a benchmark soon I think. Shouldn't take too much 
time to do so. I have some good SQL data I can punch into an array then create 
a memory based SQL database from. 

Bob S


> On Apr 18, 2018, at 10:08 , Richard Gaskin via use-livecode 
>  wrote:
> 
> Alex Tweedly wrote:
> 
> > I expect the searches etc. would be faster with SQL / memory database
> 
> Both use hashed lookups to find data.  What would be needed to bring LC array 
> queries on par with SQLite?
> 
> -- 
> Richard Gaskin


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: [Ann] xaLib - library to extract data from an array.

[Richard Gaskin via use-livecode]

Alex Tweedly wrote:

> I expect the searches etc. would be faster with SQL / memory database

Both use hashed lookups to find data.  What would be needed to bring LC 
array queries on par with SQLite?


--
 Richard Gaskin
 Fourth World Systems
 Software Design and Development for the Desktop, Mobile, and the Web
 
 ambassa...@fourthworld.comhttp://www.FourthWorld.com

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: [Ann] xaLib - library to extract data from an array.

[Alex Tweedly via use-livecode]

No, no SQL database involved.

The array is kept untouched - although you can use the xaCopy function 
to make a copy of some subset of the data, in general it's going to be 
more efficient (both space and time) to keep the array.


There is no relational anything - it's just intended for wen you are 
using a LC array as a key <--> value data store.


You can do sorting by multiple columns, using xaOrderBy (each column is 
sorted in (reverse) turn, depending on the fact that LC's sort is 
stable).  (note it does the key matching as one phase, then sorts those 
keys as a second phase).


I expect the searches etc. would be faster with SQL / memory database - 
but if you are using the array anyway, this way can be simpler, easier 
and it avoids any sync issues if the array is being changed - as well as 
allowing you to keep only a single copy in memory.


-- Alex.

On 18/04/2018 16:07, Bob Sneidar via use-livecode wrote:

Thanks Alex. Couple questions:

Does this convert to an SQL database? If so, are there commands for ordering?

The advantage to converting to a memory based sqLite database is that there are 
features unique to SQL, like relational queries and sorting by multiple columns 
that you cannot get by simply extracting matching keys. The downside *might* be 
the time it takes to convert, but it's one pass through the array (or arrays), 
which I assume you are having to do anyway.

I use a memory database, but of course you wouldn't have to. Any SQL database 
would work but memory makes it so much faster, so long as the array size is not 
humongous.

Bob S



On Apr 18, 2018, at 07:00 , Alex Tweedly via use-livecode 
 wrote:

So, I was tempted too :-)

But I decide there was no need for the complexity of xquery, and it was better 
to keep it simple, along the lines of Andre's DBLib, or revIgniter's Database 
lIbrary.

Hence : xaLib  - eXtract from an Array Library
which can be found (I hope) at github.com/alextweedly/xalib


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode



___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: [Ann] xaLib - library to extract data from an array.

[Bob Sneidar via use-livecode]

Thanks Alex. Couple questions: 

Does this convert to an SQL database? If so, are there commands for ordering? 

The advantage to converting to a memory based sqLite database is that there are 
features unique to SQL, like relational queries and sorting by multiple columns 
that you cannot get by simply extracting matching keys. The downside *might* be 
the time it takes to convert, but it's one pass through the array (or arrays), 
which I assume you are having to do anyway. 

I use a memory database, but of course you wouldn't have to. Any SQL database 
would work but memory makes it so much faster, so long as the array size is not 
humongous. 

Bob S


> On Apr 18, 2018, at 07:00 , Alex Tweedly via use-livecode 
>  wrote:
> 
> So, I was tempted too :-)
> 
> But I decide there was no need for the complexity of xquery, and it was 
> better to keep it simple, along the lines of Andre's DBLib, or revIgniter's 
> Database lIbrary.
> 
> Hence : xaLib  - eXtract from an Array Library
>which can be found (I hope) at github.com/alextweedly/xalib


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

[Ann] xaLib - library to extract data from an array.

[Alex Tweedly via use-livecode]

Bob Sneidar and Richard Gaskin said :


> Essentially it's for finding things in an array. You might say I could
  > do the same thing by iterating through all the elements of an array,
  > but what if I wanted to do multiple queries? It's easier for me to
  > write SQL queries than it is to code repeat loops.

I hear ya'.  I find myself tempted way more than I have time for to
write an xquery-like library for working with arrays.  We could sure use
one.

So, I was tempted too :-)

But I decide there was no need for the complexity of xquery, and it was 
better to keep it simple, along the lines of Andre's DBLib, or 
revIgniter's Database lIbrary.


Hence : xaLib  - eXtract from an Array Library
   which can be found (I hope) at github.com/alextweedly/xalib

(I may not have done everything right on github - please let me know if 
I need to fix anything).


There a script-only library, and a very simple test/demo stack as well 
as some basic documentation.


Summary:
the data is held in a LC array. You
 - (optionally) specify the matching criteria (by a series of calls to 
xaWhere)
 - (optionally) specify the ordering needed (by a series of calls to 
xaOrderBy)

 - (optionally) name that query for later re-use (xaNameQuery)
 - then run the query by calling xaQuery, which returns the set of keys 
matching your needs.
 - (optionally) make a copy of the 'interesting' data (but usually 
better not to do this).


Any suggestions / comments / contributions welcomed.

-- Alex.
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode