Re: Incremental search optimization #mssql
Optimisation of this sort of program can be like herding Cats. Get it lightning fast for 1 set of data, and another customer with a different data profile will suddenly have awful performance. Maybe DirectSQL, maybe Link Joins instead of Query, maybe moving links into a subtask called in RP (if possible). There is no right theoretical answer, it depends on the data.
Also worth noting that XPA doesn’t tell MSSQL what Indexs to use, it just adds ORDER BY to the statement and lets MSSQL decide. For that reason, I would only bother with the Unique Index being a physical key, the rest can be defined as Virtual Keys in Magic. You may or may not have those keys in MSSQL, it doesn’t matter from an XPA perspective.
The quick and dirty answer (or at least the one that doesn’t make your head hurt) is to run Server Profiler/Database Engine Tunning Advisor while using the task on the data in question, then if the suggestions look like they would give decent improvement, apply them in MSSQL. XPA has no need to know about these changes. We don’t do this very often, but sometimes its worth it, it can make a BIG difference even to a perfect formed data structure. Be aware that this can lead to performance loss in areas that update this data, so don’t do it repeatedly, but as a one off it’s a reasonable thing to do.
Also worth noting that XPA Help says not to use Incremental Locate in MSSQL – Tough luck, we need to.
From: firstname.lastname@example.org <email@example.com> On Behalf Of Adrian Wick via groups.io
Sent: 23 May 2021 15:50
Subject: [magicu-l] Incremental search optimization #mssql #xpa3.3
Hello all wizzards of Magic,