Incremental search optimization #mssql
|
Adrian Wick
Hello all wizzards of Magic,
|
|
|
|
Steven Burrows
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.
Steven Burrows
From: main@magicu-l.groups.io <main@magicu-l.groups.io>
On Behalf Of Adrian Wick via groups.io
Sent: 23 May 2021 15:50 To: main@magicu-l.groups.io Subject: [magicu-l] Incremental search optimization #mssql #xpa3.3 Hello all wizzards of Magic,
|
|
|
|
Adrian Wick
Thank you Steven Burrows!
To be honest i would love to completely remove the incremental search ... But sadly we cannot live without it. Its a "must" feature. I turned on logging to see exactly what is sent to the database. Like i mentioned in my first post, changing index to clustered and adding a new index which includes the field i search on + clustered field makes a BIG BIG difference. If i have the same setup, except for clustered index, i see a very small improvement. if any at all. So i think ill just alter the index and add a new one. or two ... I have to look on how many fields we do incremental searches. I also dug a little deeper and found a settings in ini: CacheSizeArrayFetch = 100 which means 100kb Isn't 100kb low for todays network traffic? Did anyone change this setting? I don't see any improvement when changing this to a higher number ... Regard, A
|
|
|