So optimiere ich T-SQL UI-Abfragen

Ich habe UI Form, die zeigt, um Benutzer verschiedene aggregierte Informationen (Tatsache, Plan etc. – 6 verschiedene T-SQL-Abfragen laufen parallel). Die Ausführung von reinen SQL-Abfragen dauert bis zu 3 Sekunden. Ich verwende gespeicherte Prozeduren mit Parametern, aber es gibt kein Problem – Aufruf von SPs nimmt absolut die gleiche time. Hier verwende ich Beispiel für einen Tisch und eine Abfrage, weitere 5 Abfragen und Tabellen haben die gleiche Struktur. Ich benutze MS SQL server 2012, es ist möglich, bis 2014 zu aktualisieren, wenn irgendein Optimierungsgrund.

Jetzt versuche ich, alle möglichen Möglichkeiten zu finden, es zu verbessern. Und es sollte nur SQL-pathe sein.

Aggregierte Tabellenstruktur:

create table dbo.plan_Total( VersionId int not null, WarehouseId int not null, ChannelUnitId int not null, ProductId] int not null, Month date not null, Volume float not null, constraint PK_Total primary key clustered (VersionId asc, WarehouseId asc, ChannelUnitId asc, ProductId asc, Month asc)) on PRIMARY 

SP-Abfrage-Struktur:

  ALTER PROCEDURE dbo.plan_GetTotals @versionId INT, @geoIds ID_LIST READONLY, -- lists from UI filters @productIds ID_LIST READONLY, @channelUnitIds ID_LIST READONLY AS begin SELECT Id INTO #geos FROM @geoIds SELECT Id INTO #products FROM @productIds SELECT Id INTO #channels FROM @channelUnitIds CREATE CLUSTERED INDEX IDX_Geos ON #geos(Id) CREATE CLUSTERED INDEX IDX_Products ON #products(Id) CREATE CLUSTERED INDEX IDX_ChannelUnits ON #channels(Id) SELECT Month, SUM(Volume) AS Volume FROM plan_Total t JOIN #geos g ON t.WarehouseId = g.Id JOIN #products p ON t.ProductId = p.Id JOIN #channels cu ON t.ChannelUnitId = cu.Id WHERE VersionId = @versionId GROUP BY Month ORDER BY Month -- no any performance impact END 

Ca. Ausführungszeit 600-800 ms. time von anderen fragt fast das gleiche. Wie kann ich die Ausführungszeit drastisch senken? Ist es möglich?

Was ich schon gemacht habe:

 - Try columnstore indexes (clustered is not good because foreign key problem); - Disable of non-clustered columnstore index is not solution, because in some tables need to update data online (user can change information); - Rebuild all current indexes; - Can't gather all tables in one. 

Hier ist eigentlich Plan Link:

Tatsächlicher Ausführungsplan – für diesen Plan füge ich echte Tabellen in Joins anstelle von temp Tabellen hinzu.

BR, danke für jede Hilfe!

Hast du gedacht, dass du nicht ankommst, Kanal, Produkt etc. zu verbinden?

Mindestens Kanäle – wenn du nicht 10.000 hast, kannst du sie einfach "on demand" oder "on application start" laden und sie zwischenspeichern. Dies ist ein Client-Seiten-dictionary-Lookup.

Auch Monat, SUM (Volumen) ….. betrachten Sie das Vorberechnen, was eine materialisierte view macht. Berechnen dieses auf Nachfrage ist nicht, was Reporting tun sollte und geht gegen Data Warehousing Best Practices.

Alle Ihre Lösungen werden das nicht ändern – sie beziehen sich nicht auf das eigentliche Problem: zu viel Verarbeitung in der Abfrage.

Sehen Sie, ob diese Weise besser funktioniert

  • Erstellen Sie den TABLE Typ, um einen PRIMARY KEY
  • RECOMPILE Option RECOMPILE : force compiler an, um die Kardinalität der TABLE variables RECOMPILE
  • OPTIMIZE FOR UNKNOWN Option OPTIMIZE FOR UNKNOWN : Verhindern Sie das Parametrieren für @versionId

 CREATE TYPE dbo.ID_LIST AS TABLE ( Id INT PRIMARY KEY ); GO CREATE PROCEDURE dbo.plan_GetTotals @versionId INT, @geoIds ID_LIST READONLY, @productIds ID_LIST READONLY, @channelUnitIds ID_LIST READONLY AS SELECT Month, SUM(Volume) AS Volume FROM plan_Total AS t INNER JOIN @geoIds AS g ON g.Id=t.WarehouseId INNER JOIN @productIds AS p ON p.Id=t.ProductId INNER JOIN @channelUnitIds AS c ON c.Id=t.ChannelUnitId WHERE t.VersionId=@versionId GROUP BY Month ORDER BY Month OPTION(RECOMPILE, OPTIMIZE FOR UNKNOWN); GO 

Ok, hier zeige ich nur, was ich finden kann und wie ich die Geschwindigkeit der Mu-Abfrage erhöht habe.

list der Addins:

  1. Der beste path ist, Clustered columnstore index hinzuzufügen. Dazu müssen Sie FKs löschen, aber Sie können zB Auslöser verwenden. Dies erhöht die Abfrage bis zu 3-4 mal.

  2. Wie kann man sehen, ich benutze Temp-Tabellen in Abfrage-Joins. Ich habe einen Join gewechselt (egal was) zu IN Operand wie dieser "und t.productid in (select id aus @productids)" es erhöhte die Abfrage reine Geschwindigkeit zweimal.

Diese beiden haben die meisten Auswirkungen auf die Abfrage. Unten möchte ich die abschließende abfrage zeigen:

 select [month], sum(volume) as volume from #geos g left join dbo.plan_Total t on t.warehouseid = g.id join #channels cu on t.channelunitid = cu.id where versionid = @versionid and t.productid in (select id from @productids) group by [month] order by [Month] 

Mit diesen Änderungen verringere ich die Abfrageausführungszeit von 0,8 auf 0,2 ms.