[MSSQL] View in AWS RDS traag mogelijk andere oplossing?

[TiMMieJ]

Mijn vraag:
Momenteel loop ik na een migratie van mijn lokale MS SQL server naar een AWS RDS tegen een performance probleem aan. Aangezien ik vooral beheerder ben en minder ontwikkelaar heb ik me tot nu toe gericht op het verhogen van de IOPS, Server memory etc.. Dit bracht zeker verbetering maar nog niet waar ik wil zijn. Ik ben dus ook de data en queries die gedaan werden gaan analyseren. Hier kwam ik tegen dat met name een query tegen een view veel problemen oplevert. Eerst zaten er veelste veel records in de tabel (10.8 miljoen en maar 226k zijn actief) dus heb ik dit opgeschoond nu door een extra filter waarde te updaten. Verwijderen kan helaas niet direct in de DB omdat de applicatie dit niet leuk vind. Ik ben ook bezig om deze te laten verwijderen maar dit kan nog niet.

Ik was me dan ook aan het focussen op de volgende view:

SELECT DISTINCT rtrim(ltrim(isnull(attr1289, ''))) AS key_attr, attr1187, attr1276, attr1160, attr1283, attr1256, attr1249, attr1214, attr1241, attr1289, activestatus
FROM hsi.rmObjectInstance1003
WHERE attr1289 IS NOT NULL AND activestatus = 0
UNION
SELECT DISTINCT RTRIM(LTRIM(ISNULL(attr1187, ''))) AS key_attr, attr1187, attr1276, attr1160, attr1283, attr1256, attr1249, attr1214, attr1241, attr1289, activestatus
FROM hsi.rmobjectinstance1003
WHERE (attr1187 IS NOT NULL) AND (activestatus = 0)

Het doel van de view is om op een naam of een ID nummer te kunnen zoeken in een enkele query. Active status is hier het bovengenoemde filter. Momenteel duurt een query op deze views circa 7 seconde waardoor een gebruiker zo'n 10 seconde zit te wachten. In de oude on-prem omgeving hadden we dit niet. De SQL Query die uitgevoerd word op de view is de volgende:

SELECT * FROM hsi.rmobjectinstanceExternalParty WHERE key_attr LIKE 'variable%' and activeStatus = 0

Relevante software en hardware die ik gebruik:
AWS RDS
io1 storage met 32k IOPS
r5.2Xlarge instance
MS SQL 2019 Server Standard

Wat ik al gevonden of geprobeerd heb:
Een index op de view had wat kunnen betekenen maar helaas ondersteund een indexed view geen UNION functie. Andere optie kan mogelijk nog zijn de view er helemaal uit te halen en het zoeken te splitsen over 2 velden en dan direct op de tabel te queryen. Dit betekend dat ik van 7 naar 3/4 seconde ga. Maar misschien mis ik nog compleet iets in de omgeving wat een winst op kan leveren.

[luukvr]

Een gewone view is niets anders dan sql alias. Wil je performance winnen moet je dus naar de totale query kijken. Dat heb je gedaan en dan haal je 0,75 seconden. Wil je meer performance winnen dan zou je deze query kunnen posten, maar waarschijnlijk ligt het aan je indexes, ook draai je heel wat functies die je over 10 miljoen records worden uitgevoerd. 2 indexed views met bijv. een schone attr1187 en attr1289 en die 2 views union-en zou misschien een betere oplossing zijn, let wel op dat je met zo'n indexed views het invoeren/aanpassen van records een stuk langzamer kan maken.

[Merethil]

Hoe heb je de database gemigreerd van on-premise naar RDS? Als je DMS (Database Migration Service) hebt gebruikt dan slaat hij standaard (bijna) al je indexen op de tabellen over. Vergelijk dat dus voor de zekerheid eens tussen de huidige RDS en de oude on-premise.

Zit er een index op die twee velden in je where in tabel hsi.rmObjectInstance1003?

[TiMMieJ]

Merethil schreef op vrijdag 5 mei 2023 @ 22:31:
Hoe heb je de database gemigreerd van on-premise naar RDS? Als je DMS (Database Migration Service) hebt gebruikt dan slaat hij standaard (bijna) al je indexen op de tabellen over. Vergelijk dat dus voor de zekerheid eens tussen de huidige RDS en de oude on-premise.

Zit er een index op die twee velden in je where in tabel hsi.rmObjectInstance1003?

De migratie is gedaan met een Offline backup and restore via een S3 bucket. Daarna heb ik de SQL Maintenance jobs geimporteerd en ook laten draaien om inderdaad alle indexes te optimaliseren. Helaas zitten er geen indexen op de gequeryde waarde's in de basistabel hsi.rmobjectinstance1003.

luukvr schreef op vrijdag 5 mei 2023 @ 16:20:
Een gewone view is niets anders dan sql alias. Wil je performance winnen moet je dus naar de totale query kijken. Dat heb je gedaan en dan haal je 0,75 seconden. Wil je meer performance winnen dan zou je deze query kunnen posten, maar waarschijnlijk ligt het aan je indexes, ook draai je heel wat functies die je over 10 miljoen records worden uitgevoerd. 2 indexed views met bijv. een schone attr1187 en attr1289 en die 2 views union-en zou misschien een betere oplossing zijn, let wel op dat je met zo'n indexed views het invoeren/aanpassen van records een stuk langzamer kan maken.

Ik ben ook aan het kijken om ze gewoon los te trekken en 2 losse velden te gebruiken om op te zoeken. Dan kan ik de view buiten spel zetten en enkel queryen op wat nodig is namelijk een partijnaam en een partij id veld.

[ Voor 37% gewijzigd door TiMMieJ op 08-05-2023 12:53 ]

[Woy]

Je zult eerst naar je query-plan moeten kijken, aan de hand van die gegevens kun je je query of anders infrastructure tonen ( b.v. meer memory, meer IOPS etc ).

offtopic:
De namen attr1187, attr1276, attr1160, attr1283, attr1256, attr1249, attr1214, attr1241, attr1289 en hsi.rmobjectinstance1003 doen mij overigens ook vermoeden dat het niet zo'n heel fijn datamodel is

[MSteverink]

Je kunt

code:

LTRIM(RTRIM(

vervangen door

code:

TRIM(

.

En die hele UNION kan weg als je

code:

WHERE (activestatus=0 AND (attr1187 IS NOT NULL OR attr1289 IS NOT NULL))

doet.

Het maakt de view in elk geval leesbaarder. Ik vermoed dat het vervangen van die UNION hem ook sneller maakt.

[ Voor 18% gewijzigd door MSteverink op 11-05-2023 09:32 ]

[dusty]

Je kunt ook de distinct verwijderen uit de view; en in plaats daarvan de distinct plaatsen in de query die de view gebruikt.

[Voutloos]

Query obfuscation.

Haal de wollige aanpak weg en er blijft enkel een triviale SELECT een, paar, cols FROM t WHERE (colA LIKE .. OR colB LIKE ..) AND colC=1 over.

Als je niet dat als vertrekpunt wil hebben, ben je enkel bezig met job security. En wel op zo’n manier dat je geen leuke job hebt

[ Voor 27% gewijzigd door Voutloos op 10-05-2023 22:51 ]

[MSteverink]

dusty schreef op woensdag 10 mei 2023 @ 22:24:
Je kunt ook de distinct verwijderen uit de view; en in plaats daarvan de distinct plaatsen in de query die de view gebruikt.

Maar dat geeft niet noodzakelijk dezelfde resultaatset. Dat geeft het wel.

[ Voor 4% gewijzigd door MSteverink op 11-05-2023 08:40 ]

[TiMMieJ]

Super bedankt allen ik ga hier even mee stoeien en kom er op terug.