In the following thread I received some important and valuable information on how I can improve a slow query on MS-SQL-Server 2019:
However, that didn’t do very much.
First I deleted all non-clustered indexes and replaced them with clustered indexex. With the clustered index, some queries seem to be at least a little faster.
First question: It always seemed to me as if MS-SQL had not used the non-clustered index at all because creating a non-clustered index did not improve performance. Is that possible?
I kept getting the tip from you that I should absolutely avoid data conversions when accessing the index.
I tried that in a view. In the following script, the date values are ALL nvarchars.
However, the script runs for 78 minutes! Without the SUBSTRINGs it doesn’t run any faster.
First Execution Plan
Second question: Is there a reason why this script has been running for so long?
The following query is the old version of the same script, just with different tables, but the table structure and the amount of data is roughly the same:
Second Execution Plan
The script from the Second Execution Plan runs for 7 minutes, although here “DATETIME date values” are linked with “NVARCHAR date values”.
I don’t understand why this script runs so fast, although it is obvious that type conversions must take place here when the index is accessed.
The latter query is then integrated as a view in the following script:
Third Execution Plan
This insert runs for 29 minutes. Previously, on MS-SQL 2008, the same insert ran for 10 minutes.
In this script I also removed the data conversions when accessing the index, but that did not bring any performance gain at all.
This script always runs for about 30 minutes, no matter what I do, I can’t get it any faster.
Third question: Do you see a bottleneck in the third execution plan?
After all, you can see in all three execution plans that clustered index accesses take place.