Ordering text

[Terence Heaford]

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 at 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 at lists.runrev.com <mailto:use-livecode at lists.runrev.com><mailto:use-livecode at lists.runrev.com <mailto:use-livecode at 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 at lists.runrev.com <mailto:use-livecode at 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>