Vragen over Indexen SQL Tabel

[RobertJB66]

In een SQL database worden veel statistische data van meetsystemen opgeslagen.
Iedere 5 minuten worden o.a. de volgende gegevens opgeslagen.

KeyID, Datum, Tijd, Jaar, Maand, Dag, Locatie, LijnNr, Batch, Product, Gemm, StDev, Aantal, etc

In het front-end gedeelte kan deze data d.m.v. Query's gefilterd worden.
Hierbij zijn alleen Jaar, Maand, Dag, Locatie, LijnNr, Batch, Product zaken die gebruikt worden om op te filteren.

Nu heeft op dit moment de tabel geen Index en duurt het soms best lang voor dat de resultaten van een Query zijn uitgevoerd.

Wat is nu de beste manier om een Index te maken?
Moet ik een index maken per deel, dus idx_Jaar, idx_maand, idx_Dag etc. of is het één index met al deze zaken er in.

Over een dag kan één meetsysteem voor meerdere uren data sturen, iedere 5 minuten waarvan Gemm, StDev, Aantal, etc allemaal uniek kunnen zijn, maar de Jaar, Maand, Dag, Locatie, LijnNr, Batch, Product zijn allemaal gelijk.

Wat is nu de juiste aanpak om ervoor te zorgen dat bij het uitvoeren van de Query de data snel wordt weergegeven.

Zonder index lijkt het erop dat dat er door de gehele tabel wordt gescand op zoek naar de regels welke voldoen aan de query.

 

[peter59]

Misschien met zoiets?

https://powerquery.how/table-buffer/

Om deze inhoud te bekijken, hebben we jouw toestemming nodig om cookies van derden te gebruiken.
Voor meer gedetailleerde informatie, zie onze cookiespagina.

Accepteer cookies van derden

 

[RobertJB66]

Peter dank je wel.
Het front end is echter een Access deel dat via ODBC verbinding maakt.
Bij mij weten heeft Access geen powerquery zaken in zich.

 

[peter59]

Access geen powerquery zaken in zich

Acces heeft idd geen power query zaken in zich maar kan wel samenwerken met Acces.
Tevens kan power query ook heel goed om gaan met een ODBC verbinding.

Om deze inhoud te bekijken, hebben we jouw toestemming nodig om cookies van derden te gebruiken.
Voor meer gedetailleerde informatie, zie onze cookiespagina.

Accepteer cookies van derden

 

[RobertJB66]

Na wat extra onderzoek hierbij mijn vraag nog wat nauwkeuriger.

Is het verstandig om een enkele kolom index te maken of is het beter om een multi kolom index te maken?

De code in Access om de query uit te voeren ziet er als hier onderuit.

    Set qdf = db.QueryDefs("EECQ_PTQ") ' Pass Through Query 2
        qdf.SQL = "SELECT Top 500 * FROM EECQ_View WHERE " & ptFilter & " ORDER By PRID Desc"
    Set qdf = Nothing

De Query die komt kunnen als hieronder zijn weergegeven:

1. ptFilter = "" (de query is dan leeg geen criteria)
2. ptFilter = "Jaar = 2025 And Maand = 10"
3. ptFilter = "Locatie = Edam And Jaar = 2025 And Lijn_Nr = Lijn-01"

 

[AHulpje]

Maar waarom zowel datum als jaar, maand en dag? Alleen datum is voldoende.

 

[RobertJB66]

Maar waarom zowel datum als jaar, maand en dag? Alleen datum is voldoende.

Sorry mijn fout "Datum" is geen onderdeel.

 

[NoellaG]

Hi, aangezien het een pass-through query is moeten de indexen op SQL server worden aangemaakt. Het beste dat je kan doen is de query via de SSMS laten uitvoeren met de optie "Include Actual Query Plan"
In het plan kan je dan alle stappen bekijken en zien waar een index kan helpen. In veel gevallen geeft de Management Studio dan zelfs aan welke index er mist.

Heb je trouwens alle velden van de tabel nodig? Select * is zowiezo een trage optie.
Op basis van je filters en order by zou ik zeggen: index op Locatie + Jaar afhankelijk van welke filters je meest gebruikt. Activeer ook de Query Store en bekijk welke query het meest uitgevoerd wordt. Bedenk erbij dat elke verschillende filter een apart query-plan op de SQL server creëert

Bericht automatisch samengevoegd: 19 okt 2025

Trouwens, je kan beter de criteria als parameters doorgeven (bv. in een SQL procedure)aan de SQL engine, anders maakt hij per criterium waarde een nieuw query plan, en dat wil je echt niet

 

[RobertJB66]

Ik ben verder met het indexen maar loop nu tegen het volgende probleem.

De onderstaande code geregeerd het daar onderstaande resultaat.

SELECT * FROM [dbo].[EECQ_View1]
  Where Maand = 9
  Order by PRID desc

Als ik nu de onderstaande code laat uitvoeren dan krijg ik een foutmelding dat een Duplicaat Sleutel is gevonden in etc.

CREATE UNIQUE CLUSTERED INDEX IDX_EECQ_View1_Maand
  ON [dbo].[EECQ_View1](Maand);
  GO

Is hier wel een INDEX voor te maken?
Zoals is te zien zijn er meerdere kolommen met duplicaten en deze zouden allemaal een INDEX moeten hebben. IS dit überhaupt wel mogelijk of streef ik iets na dat niets oplevert.

Mijn doel is het filteren te versnellen

 

[xps351]

Is hier wel een INDEX voor te maken?

Lijkt me wel, maar geen UNIEKE index.

 

[RobertJB66]

Zo te zien kan op een View alleen een Unieke Clustered index gedaan worden!

 

[NoellaG]

Indexed views worden net als tabellen opgeslagen, en je kan daar verschillende indexen op aanmaken. Alleen zijn er een paar regels. De eerste index moet een clustered index zijn omdat deze index (ook op tabellen) in feite een tabel/view is die geordend op schijf wordt opgeslagen. Daarna kan je andere indexen aanmaken. In praktijk gebruiken we indexed views om bv. indexen op gegroepeerde velden te leggen.
Andere regels waaraan moet voldaan zijn om een indexed view te creëren vind je op MS_Indexed_views

Bericht automatisch samengevoegd: Vandaag om 18:44

Nog een paar bemerkingen:
- je kan geen unieke index creëren op het veld maand omdat dit geen unieke waarden bevat
- als je een index legt op het veld maand in de onderliggende tabel, zal die index ook gebruikt worden in queries gebaseerd op een view die de tabel gebruiken

Bericht automatisch samengevoegd: Vandaag om 18:51

en hier nog een query waarmee je voor een database de missende indexen kan opvragen:

SELECT --TOP 50
dm_mid.database_id AS DatabaseID, dm_migs.avg_user_impact as UserImpactPCT,
dm_migs.user_seeks+dm_migs.user_scans as User_uses,
dm_migs.avg_system_impact as SystemImpactPCT,
(dm_migs.system_seeks+dm_migs.system_scans) as System_uses,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_name]'

+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID() and dm_migs.avg_user_impact > 80 and dm_migs.user_seeks+dm_migs.user_scans > 50
ORDER BY UserImpactPCT DESC
GO