Opgelost Hoe resultaten weergave te versnellen

[RobertJB66]

Ik heb een behoorlijk complexe Query die de data uit 4 afzonderlijke Query's krijgt (EECQVandaagSom).
2 van deze Query's zijn Pass Through Query's (XYZ_PTQ) en halen minimale data op. Dit gaat ook snel.
De andere 2 Query's haalt de data uit het resultaat van een Pass Through Query. Dit is de opbouw van de Query.



Als ik vervolgens in mijn Applicatie (database) een verversing doe dan neemt het verversen van het formulier dat zijn data van (EECQVandaagSom) betrekt veel tijd. Zoveel dat onder in het scherm komt te staan "Calculating".

Het resultaat van deze query zijn maar 5 regels met wel 61 kolommen.

De PTQ voer ik voor het verversen van het formulier uit.

Set qdf = db.QueryDefs("EECQVandaag_PTQ") 'Haal de EEC data van alleen vandaag op
qdf.SQL = "SELECT Top 1000 * FROM EECQ_View WHERE (((Leeftijd)=0) AND ((n)>1))ORDER BY Locatie, Lijn_Nr, Product, PRID; "

Set qdf = db.QueryDefs("TrendNu_PTQ") 'Haal de laatste Trend data op.
qdf.SQL = "SELECT Locatie,Lijn_Nr, Charge As Batch, Product,Range, Tijd,Aantal,Gemm,Tu1p,Ex_NG,NoGap,[Tu2 Ok], [Tu1 Ok],Ok, [To1 Ok], [To2 Ok], Std FROM Trend_View WHERE (((Range) <= 5) And ((Aantal) > 1)) ORDER BY Locatie, Lijn_Nr, Product, PRID, Range;"

Set qdf = db.QueryDefs("TrendVandaag_PTQ")  'Haal de laatste Trend data op
qdf.SQL = "SELECT Locatie, Lijn_Nr, Charge AS Batch, Product, ProductID, Tijd , Eenheid, Tu2, Tu1, REFW, To1, To2, Tarra, Dichtheid, Range, Aantal FROM Trend_View WHERE (Range) <= 3600 ORDER BY Locatie, Lijn_Nr, Charge, Product;"

Voor het verversen van het formulier gebruik ik deze code:

Me.[frmEECDagOverzicht].Form.Filter = dFilter
Me.[frmEECDagOverzicht].Form.FilterOn = True

Hoe zou ik dit kunnen aanpassen zodat dit veel sneller gaat?

 

[OctaFish]

Dus de 1e generatie queries gaan snel (genoeg), maar de samengestelde query is niet vooruit te branden? Het aantal kolommen in een query zou normaal gesproken weinig invloed hoeven te hebben op het resultaat (minder dan het aantal rijen) maar in jouw geval zie ik berekening op berekening met afhankelijke velden, en dat heeft een behoorlijk negatieve invloed op de snelheid, zoals noella al regelmatig heeft uitgelegd.
Zo zijn de velden [Dn] en [DSumX] al berekeningen, waar je dan weer een nieuwe berekening mee maakt. En als je dat dan tig keer doet, dan mag je wel een goed boek achter de hand hebben .

Zelf zou ik in die situatie liever met een hulptabel werken waarin je de (snelle) tussenresultaten opslaat, zodat je in de eindquery alleen ‘eerstelijns’ berekeningen overhoudt.

En dit:

Me.[frmEECDagOverzicht].Form.Filter = dFilter
Me.[frmEECDagOverzicht].Form.FilterOn = True

Is toch hetzelfde als dit:

with Me
     .Filter = dFilter
     .FilterOn = True
End With

Of ben ik nou gek?

 

[RobertJB66]

Dankje wel. Het klopt dat die berekeningen voor de vertraging zorgen.



Als ik deze eruit haal en dan voornamelijk de Dsr: dan scheelt dit veel tijd.

Ik begrijp het niet helemaal van de hulp tabel. Verleg ik dan niet het probleem?
De diverse "Dxyz" zaken zijn de "Som" van het aantal registraties van die dag.
t.b.v. het berekenen van de Standaard deviatie is een wat complexere formule nodig wortel uit "Dsr"

Het formulier heeft een ververs frequentie van iedere 60 seconde. Het is zeg maar een dashboard waarbij de "Dxyz" data normaal gesproken eens per 10 minuten zicht aanpast.

Hoe zou ik dit dan het beste aankunnen pakken?

Hiermee wordt de data in een Tab ververst en niet het hoofd formulier.

Me.[frmEECDagOverzicht].Form.Filter = dFilter
Me.[frmEECDagOverzicht].Form.FilterOn = True

het kan zeker zijn dat er een kortere code hiervoor mogelijk is.H

 

[OctaFish]

Ik begrijp het niet helemaal van de hulp tabel. Verleg ik dan niet het probleem?

Nee, integendeel. Je maakt het jezelf een stuk makkelijker. Ik vermoed dat alle velden die SUM als functie hebben snel zijn; het is tenslotte een Totalenquery. De velden met Expression zijn gebaseerd op de SUM velden, en bestaan dus uit minstens twee velden die allebei elke keer opnieuw berekend worden. En dat is een garantie voor Vertraging (met een hoofdletter).
Door een kopietje te maken van je query, dan al die expressievelden er uitgooien en de query omzetten naar een Tabelmaak query, krijg je een tabel met daarin een stuk of 5 rijen (1e bericht). Met dus alleen die velden die je in je berekening nodig hebt. En die velden bevatten dus waarden, en géén formules. Ergo: die tabel is bloedjesnel.

In je oorspronkelijke query verander je de bron naar de tijdelijke tabel, en je hebt dus dan een query die alleen berekeningen uitvoert op vaste waarden. Mag qua snelheid geen enkel probleem meer zijn.
Wél zou ik dan een procedure/functie maken die de waarden uit die tussentabel op tijd verwijdert als je de query opnieuw draait.

Hiermee wordt de data in een Tab ververst en niet het hoofd formulier.
...
het kan zeker zijn dat er een kortere code hiervoor mogelijk is.H

objecten op een tabblad spreek je op dezelfde manier aan als objecten die 'gewoon' op het formulier staan. Het tabblad object is a.h.w. onzichtbaar. Daar hoef je dus in functies geen enkele rekening mee te houden.
En ja, je hoeft alleen maar naar de twee codes te kijken om te zien welke korter is .

 

[RobertJB66]

Ik voor de Diverse "Dxyz"een eigen Pass Through Query gemaakt.
Hierin worden ook de berekeningen gedaan.
Deze heb ik vervolgens in de totaal Query opgenomen. Hierdoor zijn er geen berekeningen meer in de totaal Query.

Ik zie helaas alleen nog geen verbetering.
Is het misschien zo dat als ik een Requery van het formulier uitvoer dat de totaal Query de taken doorgeeft aan de gelinkte Query's?

De onderstaande code heb ik vervangen door.

Me.[frmEECDagOverzicht].Form.Filter = dFilter
Me.[frmEECDagOverzicht].Form.FilterOn = True

Me.[frmEECDagOverzicht].Form.Requery

Vooraf worden de Pass Through Queries gedraaid en wordt er op dat niveau al gefilterd.

 

[OctaFish]

Ah, ik heb een zwak voor mensen die, ondanks dat ze tips krijgen om iets beter/sneller of korte te doen, tóch vasthouden aan de eigen principes
Dus dan maar weer vragen: waarom niet?

Me.Requery

Of gaat het hier om een subformulier?

Wat je aanpassingen betreft: zijn de losse queries nu wél snel? En heb je de Tabelmaak query optie al getest?

 

[RobertJB66]

Onze berichten hebben elkaar gekruist.
Ik ben er nu mee bezig

 

[RobertJB66]

Ok de test gedaan met de Tabelmaak Query. Ik zie geen verschil met de Pass Through Query die ik hiervoor heb gemaakt. De PTQ is snel als ik deze uitvoer en in de totaal Query vindt geen enkele berekening meer plaats.

Me. requery

Zorgt er niet voor dat formulier in de TAB wordt geupdate wel het hoofd formulier. Het formulier in de TAB is denk ik een subformulier.

Het geheel lijkt nu naar behoren te werken even geen idee waarom het eerder traag leek.
Ga nu even wat verder loggen.

 

[RobertJB66]

Ik ben aan het loggen gegaan en het verversen van het formulier gaat net zo snel als het openen van de totaal query.

Dank je voor de hulp.

 

[OctaFish]

Zorgt er niet voor dat formulier in de TAB wordt geupdate wel het hoofd formulier. Het formulier in de TAB is denk ik een subformulier.

Dat vroeg ik dus ook . Want inderdaad, een subformulier heeft zijn eigen requery nodig. Maar goed dat het is opgelost!

 

[NoellaG]

Toch één vraagje: waarom doe je een deel van de queries in de database, en daarop dan nog queries in de applicatie? Waarom doe je niet alle berekeningen op de database met CTE's en/of temp tables? Veel meer SQL mogelijkheden + tuning mogelijkheden. En dan stuur je alleen de resultaten over het netwerk.

 

[RobertJB66]

De reden waarom ik delen in de applicatie doe is om het voor mijzelf eenvoudiger te houden. Ik heb in SQL een 5 tal vieuws aangemaakt en uit deze views haal ik dan de gegevens die nodig zijn. Om wat extra uitleg te gegeven graag verneem ik of dit verstandig is om dan als een View te maken in SQL.

Aangesloten systemen sturen ca. eens per uur statistieken naar de SQL (EEC). Tevens wordt er elke 5 minuten een snapshot van de actuele statistiek naar SQl gestuurd (Trend).
Ik wil een overzicht weergegeven van 3 regels: Som van de EEC statistieken per systeem en product (Totaal), Laatste EEC en de laatste Trend gegevens (Sample). Ter illustratie wat ik wil weergegeven

 

[NoellaG]

Als je de gegevens in Access alleen moet tonen en niet meer aanpassen is de meest eenvoudige oplossing: maak een rapport van de resultaten in een rapport op SQL server zelf. Hiervoor moet de reporting service geactiveerd zijn, maar dan publiceer je de rapporten rechtstreeks van de database naar het web. De datasource kan je dan maken met alle mogelijkheden van T-SQL. Dan heb je geen extra applicatie nodig.
Aangezien T-SQL een massa's meer mogelijkheden biedt (kijk bv. eens naar de Windowing functies) dan de Access SQL taal kan je, ook als je Access als report generator gebruikt, het gewenste resultaat volledig in de views berekenen. Bovendien kan je dan de nodige indexen beter analyseren en leggen. Om heel snel te werken kan je ook een indexed view als basis aanmaken. Dit kan razendsnel werken.
Alles hangt natuurlijk af van je kennis van SQL en je rechten op de database.

 

[RobertJB66]

Dir klinkt allemaal zeer interessant, ik ben alleen bang dat mijn kennis van SQL het hier gaat laten afweten. De rechten op de database die heb ik wel. Ik ga eens speuren wat ik hierover kan vinden. Dank je voor de uitleg.

 

[OctaFish]

ik ben alleen bang dat mijn kennis van SQL het hier gaat laten afweten. De rechten op de database die heb ik wel.

Maar ik vermoed de opleiding niet . Het is allemaal niet zo moeilijk, als je ervoor geleerd hebt. Is dat niet het geval, dan zou ik zeggen: lekker afblijven, een professionele dba ernaar laten kijken en genieten van het resultaat.

Tenzij je zo iemand bent die denkt dat je met het kijken van een youtube filmpje daarna zelf ook wel een open hart operatie kan uitvoeren . Oftewel: als het je vak niet is, waarom zou je het in hemelsnaam zelf willen doen? Waarom denken we eigenlijk nog dat mensen een vak moeten leren? Long live YouTube .

 

[NoellaG]

Laat je niet afschrikken door Octafish: in het database deel van dit forum bevat mijn eerste bericht een startcursus T-SQL. Daar kom je al een heel stuk verder mee. Verder kan ik de website www.mssqltips.com aanraden, hier een voorbeeld van een tutorial daar:
https://www.mssqltips.com/sqlservertutorial/160/sql-server-stored-procedure-tutorial/

en natuurlijk, eens dat je de smaak te pakken hebt: de blogs van Brent Ozar. Deze heeft ook heel interessante gratis tools zoals https://www.brentozar.com/first-aid/

@OctaFish: een professionele dba zal je helpen met de installatie en onderhoud van je DB (back-ups, tuning, security, ...) maar om dingen te ontwikkelen kan je beter een SQL developer aanspreken.

 

[OctaFish]

Vind ik allemaal prima. Maar ik blijf bij mijn standpunt, dat amateurs zich niet (teveel) moeten bemoeien met professionele activiteiten. Thuis? Prima, maar in Nederland hebben we opleidingen zodat mensen een vak kunnen leren en uitoefenen. Amateurisme hoort niet thuis in een bedrijf.

 

[NoellaG]

Aan de manier waarop Robert de vragen stelt denk ik niet dat hij een amateur is. En ik kan je verzekeren dat ik zeker geen amateur ben . Ik werk professioneel al meer dan 20 jaar met SQL server. Verder heb ik, weliswaar niet in Nederland, mijn Microsoft ceritficaten behaald op dit vlak.

 

[OctaFish]

En ik kan je verzekeren dat ik zeker geen amateur ben. Ik werk professioneel al meer dan 20 jaar met SQL server.

Echt weer typisch voor jou om te denken dat ik dat ook maar met één lettergreep ergens heb gesuggereerd. Zoals de waard is, vertrouwt-ie zijn gasten…

 

[RobertJB66]

Ok Challenge accepted .

Het zal waarschijnlijk niet de beste oplossing in SQl zijn, maar ik heb het voor elkaar.
Ik heb 5 Views gemaakt in SQL door de Queries uit Access om te zetten naar SQL.
Deze 5 Views heb ik vervolgens aan elkaar gelinkt en hier één view van gemaakt.
Het resultaat hiervan is exact hetzelfde als wat ik had gemaakt in Access alleen draait het nu op de SQl server. Ik kan de View nu eenvoudig aanroepen met een eenvoudige Pass Through Quiry in Access.

Ik moet zeggen dat AI mij hierbij wel een beetje heeft geholpen, maar waar is techniek voor als je het niet zou gebruiken

Morgen maar eens kijken of dit nu veel sneller is. In mijn ontwikkelomgeving zal het mogelijk niet veel uitmaken aangezien SQl server en Acces op dezelfde machine draaien. Ik weet wel dat het in de praktijk veel beter zal moeten zijn want er gaat nu alleen het eindresultaat over het netwerk en niet redelijk wat min of meer ruwe data.

Bedankt voor de input.