Opgelost Filter resultaten traag

[RobertJB66]

Forum leden,

Ik heb een access app die data via een ODBC verbinding gelinked is aan een SQl database.
Op mijn eigen systeem loopt het netjes snel maar als ik het op een ander PC zet dan neemt het updaten van de data a.d.h. van een filter behoorlijk lang.

Dit is de code die ik gebruik voor het uitvoeren van de update.

Me.[EECQ_Sub].Form.Filter = sFilter
Me.[EECQ_Sub].Form.FilterOn = True

Het uitvoeren van dit filter neemt ca. 6 seconde in beslag.
Ter indicatie de tabel in de SQL database bevat ca. 50.000 records.
mijn eigen SQL heeft maar ca. 9.000 records.

Heeft iemand een idee hoe ik het uitvoeren van de Query en het resultaat kan versnellen.
Ik verwacht dat de tabel van de database zal groeien naar ca. 150.000 records.

Ter indicatie de systeem monitor laat maar ca. 30% CPU belasting zien van Access

 

[OctaFish]

Oftwel: op systeem A met 9.000 records loopt het redelijk snel, maar op het andere systeem met 50.000 records een stuk langzamer? En is die 6 seconde filtering voor de kleine recordset of voor de grote? Dan zou, in de laatste variant, de filtering dus in de korte set minder dan een seconde duren, wat inderdaad netjes is. In het andere geval (9k records en 6 sec) zou het filteren van de grote set zo'n kleine minuut duren? Of zie ik het nu helemaal verkeerd?

 

[RobertJB66]

Sorry dat ik het niet duidelijk genoeg heb weer gegeven.

6 sec is voor de 50.000 records.
De tijd van de ca. 9.000 records is minder dan 1 seconde.

 

[OctaFish]

Dan klopt het toch? Of verwacht je van een 7 keer zo grote dataset dat die net zo snel is? De vraag is natuurlijk: heb je de complete dataset wel nodig, of is het slimmer (en sneller) om eerst een deel te laden, en eventueel later een ander deel.

 

[RobertJB66]

Het zou voor mij werken om met een deel van de dataset te werken, maar wel de gehele data set behouden.
Hoe zou ik dit dan aan moeten pakken als ik b.v. allen de recentste 10.000 records zou willen gebruiken?

 

[NoellaG]

Ik ben al meer dan 20 jaar SQL server DBA en heb al heel wat tuning gedaan. Om te beginnen: ODBC is traag. Dus je wil zo weinig mogelijk gegevens over het netwerk halen en de filtering door het snelste systeem laten doen = SQL server. 150000 records zijn echt niet veel voor een SQL server, maar je wil die niet allemaal over ODBC trekken. Schrijf je queries dus via pass through queries, of nog beter: gebruik geparametriseerde SQL procedures. Als je gewone Access queries gebruikt dan wordt de complete dataset over het net getrokken en de filtering door Access gedaan.
Volgend punt: het maakt heel veel uit hoe je de query schrijft. Basis dingen om op te letten zoals: gebruik geen select *, geen berekeningen in de WHERE clause in het linkerdeel van de vergelijking
Zorg dat je de juiste indexen hebt en schrijf je query zo dat deze indexen ook gebruikt worden. Je kan de query uittesten in de management studio en de optie "Show actual plan aanzetten". Dan zie je in het query plan welke indexen er gebruikt worden en wordt zelfs aangegeven of er missing indexes zijn.
De top 10.000 is teveel, geen enkele user kan 10.000 recs in 1 keer overkijken. Neem bv ivoor de meest recente 100:
select top (100) field1, field2, ... from Mytable order by fielddate desc .
Je kan windowing functies gebruiken om daarna telkens de volgende 100 recs op te halen.

Als je totalen of berekeningen met die recs wil doen, laat dit door SQL server berekenen.

Probeer je queries te parametriseren, anders moet SQL server steeds een nieuw plan opstellen, en dat kost tijd.

 

[RobertJB66]

Dank je wel voor de reactie.

Ik heb mijn zaken gecontroleerd en ik maak gebruik van Pass Through Queries (PTQ).
Het kan natuurlijk zijn dat ik het niet juist aanpak!

Kan je mij aangeven hoe ik het dan aanpak dat ik een beperkt aantal records filter o.b.v. het onderstaande voorbeeld?

sFilter = "Locatie = "Locatie 4" And Jaar = 2024 And Maand = 2 And Dag = 14 And Lijn_Nr = "TST05"""

Me.[EECQ_Sub].Form.Filter = sFilter
Me.[EECQ_Sub].Form.FilterOn = True

Waar geef ik dan die Select top(x) in?

 

[OctaFish]

Dat is simpel:

 Select Top 100 {veld1] etc.  From tabl

En het getal verander je dan naar behoeven.

 

[NoellaG]

als je met form filters werkt dan filter je de access recordset die al over het netwerk is getrokken. Je werkt daar in Access en moet natuurlijk de SQL syntax van Access gebruiken. Als je alleen de gegevens die je nodig hebt over het netwerk wil halen moet je een top/WHERE clause in de recordsource plaatsen.

 

[RobertJB66]

als je met form filters werkt dan filter je de access recordset die al over het netwerk is getrokken. Je werkt daar in Access en moet natuurlijk de SQL syntax van Access gebruiken. Als je alleen de gegevens die je nodig hebt over het netwerk wil halen moet je een top/WHERE clause in de recordsource plaatsen.

Dit had ik mij zelf ook gisteravond bedacht. Dank je wel voor de opmerking.

 

[RobertJB66]

Sorry dat ik het vraag maar kunnen jullie mij wat meer op weg helpen.

Nu is de code:

sFilter = "Locatie = "Locatie 4" And Jaar = 2024 And Maand = 2 And Dag = 14 And Lijn_Nr = "TST05""
    Me.[EECQ_Sub].Form.Filter = sFilter
    Me.[EECQ_Sub].Form.FilterOn = True

Ik kan de data ophalen via de onderstaande code. Ik weet dat het niet verstandig is om de * te gebruiken. Ik kan hiervoor verwacht, ik de kolommen ingeven die ik wil hebben als niet alle kolommen nodig zijn.

Set rs = CurrentDb.OpenRecordset("SELECT * FROM EECQ WHERE " & sFilter)

Hoe krijg ik dan vervolgens de gefilterde data weergegeven in

Me.[EECQ_Sub].Form

?

Het Form EECQ-Sub heeft als Record Source nu de EECQ query wat een Pass Through Query is.
Moet ik de EECQ-Sub nu verwijzen naar de rs?

 

[OctaFish]

Een (sub)formulier heeft altijd een Recordbron als het niet een niet-afhankelijk formulier is. Dat kan een (opgeslagen) query zijn of een tabel. Zo te zien maak je de query m.b.v. VBA, dus ik zou in dat geval een vaste query gebruiken voor het formulier, dat je dus opslaat, en met een QueryDef de SQL code van die query aanpassen.

 

[NoellaG]

Set rs = CurrentDb.OpenRecordset("SELECT * FROM EECQ WHERE " & sFilter)

gaat nog steeds alle records van EECQ over het netwerk trekken en daarna in Access filteren. Om dat te vermijden moet je de WHERE instructie in je pass through query steken. Daarom kan je in de passthrough best een SQL procedure met de gewenste parameters oproepen zodat de filtering al op SQL server gebeurt.
De procedure kan je in de management studio schrijven. Hoe je deze best in de Access front end oproept/gebruikt vind je op het net (bv https://stackoverflow.com/questions/63284386/call-sql-stored-procedure-using-procedure-clause ), of er zijn hier genoeg Access developers die het je ongetwijfeld beter kunen uitleggen. Ik ben zelf meer een database mens en geen applicatie developer.

Creatie van SQL server procedure:
SQL stored proc

 

[RobertJB66]

Ik heb van alle queries in Access een pass through query (ptq) gemaakt.

Alle ptq werken als ik ze gewoon uitvoer. Zij geven hetzelfde resultaat als de eerdere access query.

De onderstaande query kan ik gewoon VBA aanroepen, een geeft geen foutmelding.

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Locatie FROM JMDQ_PTQ WHERE " & sFilter)

Echter, de onderstaande ptq geeft mij de melding "Run-time error 3145 Syntax error in WHERE clause"

Set rs = CurrentDb.OpenRecordset("SELECT * FROM EECQ_PTQ WHERE " & sFilter)

Heeft iemand een idee waarom en wat de oplossing is?
Ik wil alle kolommen hebben met allen de data die voldoet aan wat er in het filter staat.

Bij het laden van de applicatie is sFilter

sFilter = ""

voor beide code delen.

 

[NoellaG]

EECQ_PTQ wordt door de SQL server uitgevoerd en alle resultaten worden naar Access doorgestuurd. De rest select * en WHERE worden gewoon door Access uitgevoerd en de syntax moet dus voldoen aan de Access SQL syntax. Als je die uitvoert bij het laden dan krijg je een fout omdat er na de WHERE iets moet volgen. Dus ik zou bij het laden zeggen WHERE 1 = 1, dan heb je ook alle records en daarna de filter verder opvullen met AND instructies.
Een tip: maak van die pass-through query gewoon een view op SQL server, dan kan je die als een tabel koppelen in Access. Dat maakt alles in Access veel eenvoudiger.

 

[RobertJB66]

Dank je wel.

1. ik krijg het niet voor elkaar om de 1=1 achter WHERE and de & sFilter juist geschreven te krijgen zodat het werkt

Set rs = CurrentDb.OpenRecordset("SELECT * FROM EECQ_PTQ WHERE " & sFilter)

kan iemand aangeven hoe ik dit juiste moet neerzetten?

2. is er een instructie van hoe van de PTQ een view te maken op SQL server?

 

[NoellaG]

"SELECT * FROM EECQ_PTQ WHERE 1 = 1 " & sFilter
De filter begint dan met ' AND '

 

[RobertJB66]

Perfect een stapje verder.

If sFilter <> "" Then
        fFilter = " And " & sFilter
    End If

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM EECQ_PTQ WHERE 1=1 " & fFilter)
    Me.[EECQ-RapportBottum].Form.FilterOn = True

De laatste regel laat mij echter alleen nog geen update resultaat zien. Moet hier nog iets bij komen eerder stond er:

Me.[EECQ-RapportBottum].Form.Filter = sFilter

of moet dit erbij blijven, maar dan iets of hoe krijg ik het te verwijzen naar de "rs"?

Ik heb een View gemaakt van de SQL statement in SQL server. Dit is nu dus gelukt.
De View heet in SQL dbo.EECQ_View. hoe spreek ik deze View nu aan vanuit VBA en zorg ervoor dat het resultaat dan juist wordt weergegeven in mijn Form?

 

[NoellaG]

De view kan gewoon gelinked worden, net als een tabel. Op het moment dat je de view gaat linken wordt er gevraagd wat de unieke key is . Hier kan je het primary key veld aangeven, of een combinatie van velden die voor deze view uniek is. Of je de gegevens al dan niet kan updaten via de view hangt af van het feit of de view op de SQL server updateble is (dat kan je uittesten in de SSMS) en of er een goede primary key is aangeduid. Eens de view gelinked is kan je die in VBA benaderen als je met een tabel doet.
Besef wel: eenmaal je recordsets met VBA benadert wordt eerst de complete dataset over het net getrokken en gebeuren alle verdere bewerkingen lokaal in Access.
SQL server data benader je trouwens beter met ADO dan met DAO. ADO is in de tijd (zo rond de eeuwwisseling) speciaal ontwikkeld om niet-Access datasets te benaderen.

 

[RobertJB66]

Ik blijf problemen houden met het concept en dat ligt geheel aan mij

Als ik het nu eens klein maak en ik hierop duidelijk instructies kan krijgen dan moet het mij ook met het grotere deel lukken.

Ik heb in SQL MS de onderstaande code gegeven voor het maken van een View:

CREATE VIEW TEST_EECQ_Sub AS
SELECT PRID, Zeit AS DatumTijd, Year([Zeit]) AS Jaar, Month([Zeit]) AS Maand, Day([Zeit]) AS Dag, DATEDIFF(day, Zeit, GETDATE()) AS Leeftijd, CONVERT(VARCHAR(5), Zeit,108) AS Tijd
FROM dbo.Proben_tab;

Als ik SQL MS selecteer SELECT Top1000 rows laat dit het keurig zien.

PRID DatumTijd Jaar Maand Dag Leeftijd Tijd
5218128 2023-07-05 15:18:04.000 2023 7 5 235 15:18
5218129 2023-07-05 15:28:37.000 2023 7 5 235 15:28
5218130 2023-07-05 15:39:30.000 2023 7 5 235 15:39
5218131 2023-07-05 15:55:44.000 2023 7 5 235 15:55

Nu zou ik graag een stuk VBA code in Access gebruiken dat deze View update o.b.v. de gegevens die in de variabele sFilter staat. Als sFilter leeg is moet het gewoon alles of alleen de recentste 100 records laten zien.

sFilter : "Jaar = 2024 And Maand = 2 And Dag = 3"

Nu is het zo als er geen waarde voor b.v. Dag is dan is de inhoud:

sFilter : "Jaar = 2024 And Maand = 2"

Als dit een probleem is dan graag aangeven wat er achter in dit voorbeeld "Dag" moet komen te staan, zodat er niet op Dag gefilterd wordt.

Kan iemand mij een stuk voorbeeld code bezorgen die het zo maakt dat o.b.v. de data in sFilter het resultaat in de View zich aanpast. Ik denk dat ik dan de update van View kan gebruiken om in het formulier dit te gebuiken voor de weergave in Access. Ik denk dat dit is wat NoellaG mij probeert duidelijk te maken. Als ik het dan goed heb, wordt allen beperkte informatie dan doorgegeven en zal het geheel veel sneller werken.