Home > User Voice > SQL Server - indexes

SQL Server - indexes
3

05.04.2022
17:18
Jarosław Dziekan

Hello,
Lately I was thinking about SQL optimization. So I did this query on production environments of our 4 random clients. The query shows where probably we should be thinking about putting index, because of high usage of those columns. Of course we all know that putting index on WFElements with attribute columns is not a good idea (maybe if you have secondary BPS_Content for only 1 process could be fine), but there are some examples on every environment that are in top 10 most highly recommended for index. And 4 of them are related to SOLR.
You can find my 'research' results here: https://nexpertis-my.sharepoint.com/:f:/g/personal/jaroslaw_dziekan_nexpertis_pl/EsIcZvxgjSJIrtGkGEJpVcABtvWxI57511tiTqDYHpD60A?e=ib6qHx

So my question is: Is there are some plans to create some of these indexes as a standard in Webcon BPS, and if not, is it safe (according to EULA) to create indexes in BPS_Content to improve performance?

Reply  
29.10.2022
21:15
Jarosław Kommisarczyk

Hi!

I found this topic very relevant to our environment. With >30 different apps and more than 1.5 milion instances on single environment, it would be sometimes useful to improve values-lookup when our lookup set is based on DET_Att* or WFDAtt* user fields. I understand that creation of arbitrary indexes on business fields can affect SQL queries execution plans and thus reduce overall performance, but addressing this topic is important.
One idea would be to be able to mark certain fields as "dictionary field". It would mean that WEBCON will automatically mirror the values to an extra table with index imposed on it. Then, when using such values in pickers (for example), one can reference to "indexed" table instead of original WFElements, WFlementDetails and the database will use index to speed up lookup.
Another idea would be to enable SOLR database to be a source of picker (directly or using REST API source to WEBCON SOLR). Then, searching and lookup should be dramatically improved - however the drawback would be delayed update of SOLR content.

Would appreciate the feedback of other valuable Members

Reply  
Solution (0) Helpful (0) Wrong answer (0)
Menu
Czerwone Maki 87
30-392 Krakow, Poland
office@webcon.com
+48 12 443 13 90