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 issue because LC doing
a "put text before” vs a “put text after” has real performance issues.
If you read my solution which is a bit of sqlite (sum) and then do a “put text
after” it sorts out the bottleneck which is actually in LC’s (put text before).
I tried put merge("SELECT *,SUM (amount) OVER (ORDER BY recID) AS balance FROM
myAccountName") into tSQL in response to a suggestion in this thread and so
others do not wander down a dead end posted my result which shows that it has
poor performance. Whether that is as a result of LC’s SQLite implementation or
just SQLite I do not know.
Thanks for your interest and suggestions.
Best Regards
Terry
> On 1 Apr 2020, at 20:46, Bob Sneidar via use-livecode
> <use-livecode@lists.runrev.com> wrote:
>
> 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 when
> insert/update is performed, you wouldn’t notice. Better yet, do the
> calculations in Livecode and store the result in the database column. WAAAAY
> quicker.
>
> Bob S
>
>
> On Apr 1, 2020, at 12:13 PM, Terence Heaford via use-livecode
> <use-livecode@lists.runrev.com
> <mailto:use-livecode@lists.runrev.com><mailto:use-livecode@lists.runrev.com
> <mailto:use-livecode@lists.runrev.com>>> wrote:
>
> 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.
>
> _______________________________________________
> use-livecode mailing list
> use-livecode@lists.runrev.com <mailto: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
> <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
