Incremental search optimization #mssql


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


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,

today i am turning to you about the old sin i have never fixed.

i have a table with 40k-50k records and incremental search is horrible. If i turn off index optimization, everything works, but
the records are not group up. So can someone give me a tip on how to build an index for the table. Here is some more info
about the table itself.

Columns:
1. counter (A10)
2. description (A40)
3. date1
4. date2
5. date3
6. ...

Indexes:
1. counter (unique) (its the only unique key in this table)
2. descritpion (nonunique) (this field is not used in any other index!)
3. date1 + description (nonunique)
4. ...

The problem is incremental search on column "description". So i guess the problem is the 2nd index. Should it also have a counter as a 2nd segment of the index?
Like description + counter? Should 1st index be clustered index? I have made 11 differend tables with differend variations and the best combination is:
1. counter (unique) + clustered
2. description + counter

And this is only for the table itself to start working the way it should. I also have 8 link queries in that window and 2 subforms ... And, if i read correctly, it also matters the number of
records that are shown in the table, because the array on the table is 0, it means that it fetches the number of records * 5 ... So if i have 30 records online, does that mean that it
fetches 150 main recrods and since i have 8 link queries, this means 150 * 8 = 1200? And to put the cherry on the top :) i use descending order in task->range/locate->expressions for
range and locate

Any info is much appreciated!

Regards,
A


Adrian Wick
 

Hello all wizzards of Magic,

today i am turning to you about the old sin i have never fixed.

i have a table with 40k-50k records and incremental search is horrible. If i turn off index optimization, everything works, but
the records are not group up. So can someone give me a tip on how to build an index for the table. Here is some more info
about the table itself.

Columns:
1. counter (A10)
2. description (A40)
3. date1
4. date2
5. date3
6. ...

Indexes:
1. counter (unique) (its the only unique key in this table)
2. descritpion (nonunique) (this field is not used in any other index!)
3. date1 + description (nonunique)
4. ...

The problem is incremental search on column "description". So i guess the problem is the 2nd index. Should it also have a counter as a 2nd segment of the index?
Like description + counter? Should 1st index be clustered index? I have made 11 differend tables with differend variations and the best combination is:
1. counter (unique) + clustered
2. description + counter

And this is only for the table itself to start working the way it should. I also have 8 link queries in that window and 2 subforms ... And, if i read correctly, it also matters the number of
records that are shown in the table, because the array on the table is 0, it means that it fetches the number of records * 5 ... So if i have 30 records online, does that mean that it
fetches 150 main recrods and since i have 8 link queries, this means 150 * 8 = 1200? And to put the cherry on the top :) i use descending order in task->range/locate->expressions for
range and locate

Any info is much appreciated!

Regards,
A