MS-SQL-Server 2019 is slower than 2008 for some SQL-Selects

We had MS-SQL-Server 2008 for 10 years and updated to version 2019 last week. We copied the databases with all tables to the new server.

Now there is a SELECT that runs much longer on version 2019 than on version 2008.

For comparisonm on the 2008 version, the SELECT ran for a total of 12 minutes, and on the 2019 version, it ran for 32 minutes. The SELECT is quite extensive and uses a view that also contains a complex SELECT. If I start the SELECT from the view alone, it will already run for 18 minutes.

The indexes are all still available in version 2019, but sometimes it seems to me as if MS-SQL accesses indexes differently than Oracle. When I create an index in Oracle, I always notice a noticeable improvement in performance. This is often not the case with MS-SQL. Could it be that MS-SQL handles an index differently than Oracle?

Here is the select from the view. I don't see how I could optimize it. Incidentally, the first table only contains values >= 2018, so I don't need the where-clause at all.

I used to have rows before 2018 in the table, but then the Select was much slower, although I had placed an index on the date, but MS-SQL wasn't interested at all.

Only when I deleted all data before 2018 was the Select faster. I never understood why the index here on the date did not bring any performance improvement.

This is the entry select that calls the SPECIAL_NOLOCK_VIEW:

insert into F_Umsatz (Tag,CD_Mandant,
SELECT v1.Tag,v1.CD_Mandant,v1.CD_Material,
v1.Materialkosten,v1.Nettoumsatz - v1.Materialkosten 
* v1.Absatzmenge_Einheit AS PE1,v1.Bruttoumsatz / (
CASE WHEN b1.Bruttoumsatz_Monat = 0 
THEN 1 ELSE b1.Bruttoumsatz_Monat END) 
* ISNULL(u1.Umstellkosten, 0) AS Umstellkosten,
(v1.Nettoumsatz - v1.Materialkosten 
* v1.Absatzmenge_Einheit) - v1.Bruttoumsatz / (
CASE WHEN b1.Bruttoumsatz_Monat = 0 
THEN 1 ELSE b1.Bruttoumsatz_Monat END) 
* ISNULL(u1.Umstellkosten, 0) AS PE2, v1.Bruttoumsatz / (
CASE WHEN b1.Bruttoumsatz_Monat = 0 
THEN 1 ELSE b1.Bruttoumsatz_Monat END) 
* ISNULL(lo1.Logistikkosten, 0) AS Logistikkosten,
v1.Einheit, v1.Nettogewicht, CURRENT_TIMESTAMP 
INNER JOIN V_Bruttoumsatz_Monat AS b1 
ON CONVERT(varchar(6), v1.Tag, 112) = b1.Monat 
AND v1.CD_Kunde = b1.CD_Kunde 
LEFT OUTER JOIN F_Umstellkosten AS u1 
ON CONVERT(varchar(6), v1.Tag, 112) = u1.Monat 
AND v1.CD_Kunde = u1.CD_Kunde 
LEFT OUTER JOIN F_Logistikkosten AS lo1 
ON CONVERT(varchar(6), v1.Tag, 112) = lo1.Monat 
and v1.CD_Kunde = lo1.CD_Kunde

And this is the fantastically designed SPECIAL_NOLOCK_VIEW:

       S631_000.VKORG AS CD_Mandant,
       S631_000.MATNR AS CD_Material,
       S631_000.PKUNRG AS CD_Kunde,
       SUBSTRING(J_Kunde.CD_BEZIRK, 1, 1) AS CD_Marke,
       S631_000.AEKZWI3 AS Bruttoauftragseingang,
       S631_000.UMKZWI3 AS Bruttoumsatz,
       S631_000.UMKZWI3 * (1 - (ISNULL(F_Bonus.Bonus_prozent, 0) + ISNULL(F_Skonto.Skonto_Prozent, 0) + ISNULL(F_WKZ.WKZ_Prozent, 0) + ISNULL(F_Sonstiges.Sonstiges_Prozent, 0) + ISNULL(F_Frachten.Frachten_Prozent, 0) + ISNULL(F_Provision.Provision_Prozent, 0)) / 100) AS Nettoumsatz,
       ISNULL(F_Materialkosten.Materialkosten / F_Materialkosten.Preiseinheit, 0) AS Materialkosten,
       S631_000.UMMENGE AS Absatzmenge_Einheit,
       S631_000.BASME AS Einheit,
       S631_000.NTGEW AS Nettogewicht,
       S631_000.WERKS AS CD_Auslieferwerk,
FROM t_sap_S631_000 S631_000
     LEFT OUTER JOIN F_Bonus ON S631_000.SPTAG >= F_Bonus.gueltig_ab
                            AND S631_000.SPTAG <= F_Bonus.gueltig_bis
                            AND S631_000.PKUNRG = F_Bonus.CD_Kunde
     LEFT OUTER JOIN F_Skonto WITH (NOLOCK) ON S631_000.SPTAG >= F_Skonto.gueltig_ab
                                           AND S631_000.SPTAG <= F_Skonto.gueltig_bis
                                           AND S631_000.PKUNRG = F_Skonto.CD_Kunde
     LEFT OUTER JOIN F_WKZ WITH (NOLOCK) ON S631_000.SPTAG >= F_WKZ.gueltig_ab
                                        AND S631_000.SPTAG <= F_WKZ.gueltig_bis
                                        AND S631_000.PKUNRG = F_WKZ.CD_Kunde
     LEFT OUTER JOIN F_Sonstiges WITH (NOLOCK) ON S631_000.SPTAG >= F_Sonstiges.gueltig_ab
                                              AND S631_000.SPTAG <= F_Sonstiges.gueltig_bis
                                              AND S631_000.PKUNRG = F_Sonstiges.CD_Kunde
     LEFT OUTER JOIN F_Frachten WITH (NOLOCK) ON S631_000.SPTAG >= F_Frachten.gueltig_ab
                                             AND S631_000.SPTAG <= F_Frachten.gueltig_bis
                                             AND S631_000.PKUNRG = F_Frachten.CD_Kunde
     LEFT OUTER JOIN F_Provision WITH (NOLOCK) ON S631_000.SPTAG >= F_Provision.gueltig_ab
                                              AND S631_000.SPTAG <= F_Provision.gueltig_bis
                                              AND S631_000.PKUNRG = F_Provision.CD_Kunde
     LEFT OUTER JOIN F_Materialkosten WITH (NOLOCK) ON S631_000.WERKS = F_Materialkosten.werk
                                                   AND S631_000.SPTAG >= F_Materialkosten.gueltigab
                                                   AND S631_000.SPTAG <= F_Materialkosten.gueltigbis
                                                   AND S631_000.MATNR = F_Materialkosten.CD_Material
     LEFT OUTER JOIN J_Kunde ON S631_000.PKUNRG = J_Kunde.CD_Kunde
WHERE (S631_000.SPTAG >= '20180101')

Read more here:

Content Attribution

This content was originally published by SipCat at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: