I agree that is why I have concluded to do the calc in LC.
However, I can’t store the balance one time in SQL as the balance value changes
depending on which records are selected and the sort order(which may be by
different columns).
So it is not just a schema issue, it started out as a LC
So this is a database schema issue. Typically what you will want to do with
things like balances is to store the balance in a column as each value in the
equation changes. You are asking your SQL server to do all the calculations for
all your records all at once. If SLQ only has to calculate
This actually works but is very slow (> 800ms)
put merge("SELECT *,SUM (amount) OVER (ORDER BY recID) AS balance FROM
myAccountName") into tSQL
I have settled on my earlier suggestion
Earlier suggestion results in 20ms performance.
-
I believe it’s sorted
As more cobwebs and dust fall away . . .
First impose a (temporary?) index column, idx, ordering the transactions, with
a step of 1.
And then you do something like
UPDATE theTable SET tBal=tBal(idx -1) + charge WHERE idx > MIN(idx);
___
Not enough info but something like:
SELECT SUM(OLD -NEW) as balance FROM some_table WHERE need_to_calc IS true
order by balance; (or order by whatever column. Not sure balance is what you
are trying to sort by.)
Bob S
> On Mar 29, 2020, at 11:54 AM, doc hawk via use-livecode
> wrote:
>
>
I haven’t fully followed the queries that led to this, but would something like
SELECT SUM(OLD -NEW) FROM some_table WHERE need_to_calc IS true;
get you anywhere?
I’m also thinking of a lag within the list, to assign them all at once, but the
commands coming to memory are from statistical
You took the SQL right out of my query. :-)
Bob S
On Mar 29, 2020, at 7:32 AM, Rick Harrison via use-livecode
mailto:use-livecode@lists.runrev.com>> wrote:
Hi Terry,
Try having your database retrieval perform the sort and not LiveCode.
Use the SQLite: ORDER BY clause.
See if that gives you
Hi Terry,
Try having your database retrieval perform the sort and not LiveCode.
Use the SQLite: ORDER BY clause.
See if that gives you a faster result.
Rick
> On Mar 29, 2020, at 7:06 AM, Terence Heaford via use-livecode
> wrote:
>
> I have approx. 7000 lines of tab delimited data.
>
>
I believe it’s sorted (forgive the pun)
As the data is stored in an SQLite db I have used the db to sum the values then
worked from the top. Like below.
This results in approx. 20ms for each sort direction.
I have looked into a running total calc using SQLite but the solutions I found
seemed
Is there a key that you can use to sort? May be faster to build it and then
use the sort command at the end.
Thanks,
Brian
On Mar 29, 2020, 7:16 AM -0400, Tore Nilsen via use-livecode
, wrote:
> I believe the reason for the delay comes from the fact that when sorted
> descending, you put tRec
I believe the reason for the delay comes from the fact that when sorted
descending, you put tRec & CR before tCellData. Tp put something before a
container is slower than putting something after a container.
Regards
Tore Nilsen
> 29. mar. 2020 kl. 13:06 skrev Terence Heaford via use-livecode
I have approx. 7000 lines of tab delimited data.
When I execute the code snippet below if the sort order is descending it takes
approx 120 milliseconds otherwise it takes 20 milliseconds to process.
Can someone explain why and perhaps come up with a faster solution.
Thanks
Terry
——
12 matches
Mail list logo