Opgelost Hoe resultaten weergave te versnellen

NoellaG · 4 sep 2024

De snelheid op SQL zal vooral afhangen van welke indexen er gebruikt worden, en hoe de views aan elkaar gelinkt zijn, dit kan een winst of verlies van tot 100x betekenen. Ik zou zeker al eens het query plan bekijken.

RobertJB66 · 4 sep 2024

Kan het zijn dat wij ons op iets verkeerd gefocust hebben?
Zoals geschreven heb ik het nu in SQL gemaakt en draait het op de SQl server.

Het uitvoeren van de Query neemt 0 seconde maar het updaten van het formulier 34 seconde!
Dit is de code voor het ophalen van de data en het uitvoeren van de formulier update.

Code:

Function DagOverzichtReset()

StartLog ("C:\Temp\QueryLog.txt")
    Set qdf = db.QueryDefs("EECQVandaagSom_PTQ") 'Haal de laatste dag data op.
    qdf.SQL = "SELECT * FROM EECQVandaagSom_View ORDER BY Locatie, Lijn_Nr, TTijd DESC , MaxTijd DESC , Batch, Product;"
LogTime ("EECQVandaagSomReset")

StartLog ("C:\Temp\QueryLog.txt")
    Me.[frmEECDagOverzicht].Form.Requery
LogTime ("EECDagOverzichtFormReset")
    
End Function

Log resultaten:
04/09/2024 14:04:07 - EECQVandaagSomReset took 0 seconds to run.
04/09/2024 14:04:41 - EECDagOverzichtFormReset took 34 seconds to run.

Er wordt geen extra date ophaalt dan het geen te zien is in de onderstaande afbeelding.

OctaFish · 4 sep 2024

Ik weet niet precies wat Startlog is, ik vermoed een eigen routine? Ik zou eerst eens kijken wat het formulier doet zónder de log. En waarom je je view (nog een keer?) sorteert verbaast mij ook. Normaal gesproken zou de view kant en klaar moeten zijn, en geen verdere nabewerking meer nodig (moeten) hebben. Dus probeer het formulier eens rechtstreeks aan de EECQVandaagSom_View te hangen, en kijk of dat verschil uitmaakt.

RobertJB66 · 4 sep 2024

StartLog is een routine waarmee is de tijden die een routine neemt kan loggen. Dit is de code.

Code:

Option Compare Database

' ######### CODE START ##########
Private dteStart As Date
Private dteEnd As Date
Private strLocation As String
Function StartLog(strFileLocation As String)
    dteStart = Now
    strLocation = strFileLocation
End Function
Function LogTime(strQueryName As String)
Dim iFileNum As Integer
Dim strFileName As String
Dim lngElapsedTime As Long

   dteEnd = Now

strFileName = strLocation

iFileNum = FreeFile

' Opens the text file to write to

Open strFileName For Append As iFileNum

' gets the elapsed time

   lngElapsedTime = DateDiff("s", dteStart, dteEnd)

      Print #iFileNum, Now & " - " & strQueryName & " took " & lngElapsedTime & " seconds to run."

      Close iFileNum

   ' reset variables

 End Function

Dit laat bij veder geen enkele andere routine die ik log een vertraging zien. e.e.a. ter info.

Ik heb het zo gedaan omdat er ook gefilterd (ptqFilter) wordt. Dit is de code met het filter erin.

Code:

            StartLog ("C:\Temp\QueryLog.txt")
                Set qdf = db.QueryDefs("EECQVandaagSom_PTQ") 'Haal de laatste dag data op.
                qdf.SQL = "SELECT * FROM EECQVandaagSom_View WHERE " & ptqFilter & " ORDER BY Locatie, Lijn_Nr, TTijd DESC , MaxTijd DESC , Batch, Product;"
            LogTime ("EECQVandaagSomFilter")
            
            StartLog ("C:\Temp\QueryLog.txt")
                 Me.[frmEECDagOverzicht].Form.Requery
            LogTime ("DagOverzichtFormFilterRequery")

Als er een andere manier is om dit te doen dan verneem ik het graag.

Hoe zou een filter door kunnen geven naar EECQVandaagSom zodat het resultaat al gefilterd aangeleverd wordt?

RobertJB66 · 4 sep 2024

Ok ik heb het frmEECDagOverzicht direct aan EECQVandaagSom_View gehangen.
Dan krijg is met de logging een tijd van 0 a 1 seconde.

Dat is dus veel beter. Maar nu dan de vraag hoe kan ik nog een filter naar EECQVandaagSom_View sturen zodat de data die wordt aangeleverd de gefilterde data laat zien. Er hoeft alleen op locatie gefilterd te worden.

Code:

ptqFilter = "Locatie 'Locatie 2' "

OctaFish · 4 sep 2024

Alleen het locatie filter in je query? Of, als de view geladen is, filteren op het formulier. Bijvoorbeeld met een keuzelijst met beschikbare locaties, zodat je simpel het filter kunt veranderen.

RobertJB66 · 4 sep 2024

Als ik je goed begrijp, ga ik nu gewoon in het formulier op locatie filteren.
Dat ga ik proberen.

RobertJB66 · 4 sep 2024

Om het formulier niet te filteren gebruik ik het onderstaande. Dit heeft eerder gewerkt, maar nu krijg ik een foutmelding! 459 - Object or class does not support the set of events

Code:

dFilter = ""
Me.frmEECDagOverzicht.Form.Filter = dFilter
Me.frmEECDagOverzicht.Form.FilterOn = True

of

Code:

 dFilter = "Locatie = "Locatie 2" "

NoellaG · 4 sep 2024

Als je de resultaat view op de SQL server verandert in een procedure of functie kan je de filter parameter ook meegeven naar de SQL server, dan hoeft niks nog in de aplicatie te gebeuren.

RobertJB66 · 4 sep 2024

Klinkt interessant. ~~Kam jij wat instructies bezorgen hoe ik eventueel een View om kan zetten naar een functie~~ ~~of a~~ procedure en hoe ik deze dan vanuit Access het filter parameter meegeef?

De functie is gemaakt nu deze nog aanroepen met of zonder filter parameter vanuit Access.
Ik heb het ook als Stored Procedure gemaakt. welke is het snelst en welke is het handigst om aan te roepen vanuit Access?

NoellaG · 4 sep 2024

een stored procedure is het meest flexibel omdat je daar ook meerdere OUT parameters kan opgeven.
Een functie kan maar één resultaat weergeven.
Een functie kan je gebruiken binnen een select statement en eventueel met een OUTER/Cross apply linken aan betsaande tabellen,
vb

SELECT T1.Field1, T1Field2
F.Value,
FROM MyTable T1
OUTER APPLY dbo.fn_MyFunctionGetTop(MyPar) F ;
GO

een procedure moet je oproepen met een execute statement.

Zie ook https://www.shiksha.com/online-cour...n-procedure-and-function-in-sql-blogId-148707

Het hangt af van hoe jij het liefst werkt, welke je het best neemt.

Hoe je deze vanuit een Access applicatie moet opvragen weet ik niet vanbuiten. Ik ben sinds een jaar of 15 een puur database mens en heb nog zelden zelf applicatie programmatie gedaan na 2009.

RobertJB66 · 4 sep 2024

Zoals aangeven heb ik van de EECQVandaagSom_View een Functie en een Procedure gemaakt.
Het lijkt mo beide vanuit Access aan te roepen.

Code:

SELECT * FROM dbo.EECQVandaagSom_Function();

EXEC dbo.EECQVandaagSom_Procedure;

Als ik ze uitvoer in SSMS dan nemen ze beide 38 seconden om het resultaat te tonen, dit zie ik ook in Access. Lijkt mij niet helemaal juist. Het resultaat zijn 8 regels met data.

Code:

USE [ThermoLink]
GO

/****** Object:  UserDefinedFunction [dbo].[EECQVandaagSom_Function]    Script Date: 04/09/2024 19:09:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[EECQVandaagSom_Function]()
RETURNS TABLE
AS
RETURN
(
   SELECT TOP (100) PERCENT
       dbo.TrendVandaagEnd_View.Locatie,
       dbo.TrendVandaagEnd_View.Lijn_Nr,
       MAX(dbo.TrendNu_View.Tijd) AS TTijd,
       MAX(dbo.EECQVandaag_View.DatumTijd) AS MaxTijd,
       dbo.TrendVandaagEnd_View.Batch,
       dbo.TrendVandaagEnd_View.Product,
       dbo.TrendVandaagEnd_View.ProductID,
       MIN(dbo.EECQVandaag_View.DatumTijd) AS MinTijd,
       dbo.EECQTODAYTotal_View.Dn,
       dbo.EECQTODAYTotal_View.DGem,
       dbo.EECQTODAYTotal_View.DStd,
       dbo.EECQTODAYTotal_View.[DTu2 OK],
       dbo.EECQTODAYTotal_View.[DTu1 OK],
       dbo.EECQTODAYTotal_View.DOK,
       dbo.EECQTODAYTotal_View.[DTo1 OK],
       dbo.EECQTODAYTotal_View.[DTo2 OK],
       dbo.EECQTODAYTotal_View.[DTu%],
       dbo.EECQTODAYTotal_View.[DNo Gap],
       dbo.EECQTODAYTotal_View.[DMD Rej],
       dbo.EECQVandaagLaatste_View.LastOfn,
       dbo.EECQVandaagLaatste_View.LastOfGemm,
       dbo.EECQVandaagLaatste_View.LastOfStdev,
       dbo.EECQVandaagLaatste_View.[LastOfTu2 OK],
       dbo.EECQVandaagLaatste_View.[LastOfTu1 OK],
       dbo.EECQVandaagLaatste_View.LastOfOK,
       dbo.EECQVandaagLaatste_View.[LastOfTo1 OK],
       dbo.EECQVandaagLaatste_View.[LastOfTo2 OK],
       dbo.EECQVandaagLaatste_View.[LastOfTu1%],
       dbo.EECQVandaagLaatste_View.[LastOfNo Gap],
       dbo.EECQVandaagLaatste_View.[LastOfMD Rej],
       dbo.TrendNu_View.Aantal AS TAantal,
       dbo.TrendNu_View.Gemm AS TGemm,
       dbo.TrendNu_View.Tu1p AS TTu1p,
       dbo.TrendNu_View.Ex_NG AS [TEX Ng],
       dbo.TrendNu_View.NoGap AS TNoGap,
       dbo.TrendNu_View.[Tu2 Ok] AS [TTu2 OK],
       dbo.TrendNu_View.[Tu1 Ok] AS [TTu1 Ok],
       dbo.TrendNu_View.Ok AS TOK,
       dbo.TrendNu_View.[To1 Ok] AS [TTo1 Ok],
       dbo.TrendNu_View.[To2 Ok] AS [TTo2 OK],
       dbo.TrendNu_View.Std AS TStd,
       dbo.TrendVandaagEnd_View.FirstOfEenheid,
       dbo.TrendVandaagEnd_View.FirstOfTu2,
       dbo.TrendVandaagEnd_View.FirstOfTu1,
       dbo.TrendVandaagEnd_View.FirstOfREFW,
       dbo.TrendVandaagEnd_View.FirstOfTo1,
       dbo.TrendVandaagEnd_View.FirstOfTo2,
       dbo.TrendVandaagEnd_View.FirstOfTarra,
       dbo.TrendVandaagEnd_View.FirstOfDichtheid
   FROM dbo.TrendVandaagEnd_View
   LEFT OUTER JOIN dbo.EECQVandaagLaatste_View
       ON dbo.TrendVandaagEnd_View.Product = dbo.EECQVandaagLaatste_View.Product
       AND dbo.TrendVandaagEnd_View.Batch = dbo.EECQVandaagLaatste_View.Batch
       AND dbo.TrendVandaagEnd_View.Lijn_Nr = dbo.EECQVandaagLaatste_View.Lijn_Nr
       AND dbo.TrendVandaagEnd_View.Locatie = dbo.EECQVandaagLaatste_View.Locatie
   LEFT OUTER JOIN dbo.EECQTODAYTotal_View
       ON dbo.TrendVandaagEnd_View.Product = dbo.EECQTODAYTotal_View.Product
       AND dbo.TrendVandaagEnd_View.Batch = dbo.EECQTODAYTotal_View.Batch
       AND dbo.TrendVandaagEnd_View.Lijn_Nr = dbo.EECQTODAYTotal_View.Lijn_Nr
       AND dbo.TrendVandaagEnd_View.Locatie = dbo.EECQTODAYTotal_View.Locatie
   LEFT OUTER JOIN dbo.TrendNu_View
       ON dbo.TrendVandaagEnd_View.Product = dbo.TrendNu_View.Product
       AND dbo.TrendVandaagEnd_View.Batch = dbo.TrendNu_View.Batch
       AND dbo.TrendVandaagEnd_View.Lijn_Nr = dbo.TrendNu_View.Lijn_Nr
       AND dbo.TrendVandaagEnd_View.Locatie = dbo.TrendNu_View.Locatie
   LEFT OUTER JOIN dbo.EECQVandaag_View
       ON dbo.TrendVandaagEnd_View.Product = dbo.EECQVandaag_View.Product
       AND dbo.TrendVandaagEnd_View.Batch = dbo.EECQVandaag_View.Batch
       AND dbo.TrendVandaagEnd_View.Lijn_Nr = dbo.EECQVandaag_View.Lijn_Nr
       AND dbo.TrendVandaagEnd_View.Locatie = dbo.EECQVandaag_View.Locatie
   GROUP BY dbo.TrendVandaagEnd_View.Locatie,
            dbo.TrendVandaagEnd_View.Lijn_Nr,
            dbo.TrendVandaagEnd_View.Batch,
            dbo.TrendVandaagEnd_View.Product,
            dbo.TrendVandaagEnd_View.ProductID,
            dbo.EECQTODAYTotal_View.Dn,
            dbo.EECQTODAYTotal_View.DGem,
            dbo.EECQTODAYTotal_View.DStd,
            dbo.EECQTODAYTotal_View.[DTu2 OK],
            dbo.EECQTODAYTotal_View.[DTu1 OK],
            dbo.EECQTODAYTotal_View.DOK,
            dbo.EECQTODAYTotal_View.[DTo1 OK],
            dbo.EECQTODAYTotal_View.[DTo2 OK],
            dbo.EECQTODAYTotal_View.[DTu%],
            dbo.EECQTODAYTotal_View.[DNo Gap],
            dbo.EECQTODAYTotal_View.[DMD Rej],
            dbo.EECQVandaagLaatste_View.LastOfn,
            dbo.EECQVandaagLaatste_View.LastOfGemm,
            dbo.EECQVandaagLaatste_View.LastOfStdev,
            dbo.EECQVandaagLaatste_View.[LastOfTu2 OK],
            dbo.EECQVandaagLaatste_View.[LastOfTu1 OK],
            dbo.EECQVandaagLaatste_View.LastOfOK,
            dbo.EECQVandaagLaatste_View.[LastOfTo1 OK],
            dbo.EECQVandaagLaatste_View.[LastOfTo2 OK],
            dbo.EECQVandaagLaatste_View.[LastOfTu1%],
            dbo.EECQVandaagLaatste_View.[LastOfNo Gap],
            dbo.EECQVandaagLaatste_View.[LastOfMD Rej],
            dbo.TrendNu_View.Aantal,
            dbo.TrendNu_View.Gemm,
            dbo.TrendNu_View.Tu1p,
            dbo.TrendNu_View.Ex_NG,
            dbo.TrendNu_View.NoGap,
            dbo.TrendNu_View.[Tu2 Ok],
            dbo.TrendNu_View.[Tu1 Ok],
            dbo.TrendNu_View.Ok,
            dbo.TrendNu_View.[To1 Ok],
            dbo.TrendNu_View.[To2 Ok],
            dbo.TrendNu_View.Std,
            dbo.TrendVandaagEnd_View.FirstOfEenheid,
            dbo.TrendVandaagEnd_View.FirstOfTu2,
            dbo.TrendVandaagEnd_View.FirstOfTu1,
            dbo.TrendVandaagEnd_View.FirstOfREFW,
            dbo.TrendVandaagEnd_View.FirstOfTo1,
            dbo.TrendVandaagEnd_View.FirstOfTo2,
            dbo.TrendVandaagEnd_View.FirstOfTarra,
            dbo.TrendVandaagEnd_View.FirstOfDichtheid
   ORDER BY dbo.TrendVandaagEnd_View.Locatie,
            dbo.TrendVandaagEnd_View.Lijn_Nr,
            TTijd DESC,
            MaxTijd DESC,
            dbo.TrendVandaagEnd_View.Batch,
            dbo.TrendVandaagEnd_View.Product
);
GO

RobertJB66 · 5 sep 2024

Ik ben teruggegaan naar de code van #22.
Wat ik wel heb gedaan is een stuk minder conditioneel formatteren.
Dit lijkt een groot verschil te maken in tijd.

Ben nog steeds geïnteresseerd waarom de Functie en Procedure zo lang duren.

De tijd die het nu neemt om het formulier te verversen is 1 seconde. Met deze code.
Als ik de logging eruit haal zal het nog wel iets sneller zijn.

Code:

StartLog ("C:\Temp\QueryLog.txt")
    Set qdf = db.QueryDefs("EECQVandaagSom_PTQ") 'Haal de laatste dag data op.
    qdf.SQL = "SELECT * FROM EECQVandaagSom_View ORDER BY TTijd DESC ,Locatie, Lijn_Nr,  MaxTijd DESC , Batch, Product;"
LogTime ("EECQVandaagSomReset")

StartLog ("C:\Temp\QueryLog.txt")
    Me.[frmEECDagOverzicht].Form.Requery
LogTime ("EECDagOverzichtFormReset")

NoellaG · 5 sep 2024

Hi, ik heb momenteel niet veel tijd maar ik zal er eens naar kijken. Al een paar vlugge opmerkingen bij de functie:

je schrijft de functie om parameters te kunnen meegeven, maar ik zie nergens een parameter?
Select top 100 PERCENT heeft echt geen zin
gebruik van aliasen zou de functie meer leesbaar maken

Zonder query plan kan je niet echt zien hoe de query uitgevoerd worden en waar de bottlenecks zitten of welke indexen missen. Je kan dit opvragen door in de SMS in het queryvenster de optie Query -> include actual execution plan aan te zetten. Ook de optie Include Live Query Statistics kan tonen waar de engine tijd verliest.
Ik vermoed dat je een heleboel views niet nodig hebt. Views op views zijn niet de snelste methode, je hebt gauw een hoop veleden/tussenresultaten die de boel vertragen. Misschien beter om de functie direct op de tabellen uit te voeren, eventueel met gebruik van temp tables of CTE's.

RobertJB66 · 5 sep 2024

Dank je voor de informatie daar ga ik naar kijken.

Zoals aan gegeven ben ik even teruggegaan naar mijn eerdere uitvoering. Ik heb het conditionele formatteren geheel uit het formulier gehaald.

Het uitvoeren van de Query neemt 0 seconde maar het verversen van het formulier neemt de tijd in beslag.

Met Conditionele Formattering:

_{05/09/2024 09:08:38 - EECQVandaagSomReset took 0 seconds to run.

05/09/2024 09:09:14 - EECDagOverzichtFormReset took 36 seconds to run.

05/09/2024 09:09:19 - EECQVandaagSomReset took 0 seconds to run.

05/09/2024 09:09:54 - EECDagOverzichtFormReset took 35 seconds to run.

05/09/2024 09:09:57 - EECQVandaagSomReset took 0 seconds to run.

05/09/2024 09:10:31 - EECDagOverzichtFormReset took 34 seconds to run.}

Zonder Conditionele Formattering:
_{05/09/2024 09:10:33 - EECQVandaagSomReset took 0 seconds to run.

05/09/2024 09:10:33 - EECDagOverzichtFormReset took 0 seconds to run.

05/09/2024 09:10:42 - EECQVandaagSomReset took 0 seconds to run.

05/09/2024 09:10:42 - EECDagOverzichtFormReset took 0 seconds to run.

05/09/2024 09:10:43 - EECQVandaagSomReset took 0 seconds to run.

05/09/2024 09:10:43 - EECDagOverzichtFormReset took 0 seconds to run.}

OctaFish · 5 sep 2024

CF kost nu eenmaal een boel tijd. Kom je toch weer terug op het gezegde "Wie mooi wil zijn moet pijn lijden"

.

RobertJB66 · 5 sep 2024

Te vroeg gejuicht.
Nu ook zonder CF heb ik de tijden van ca. 30 seconde weer terug voor het uitvoeren van de formulier Query.

Het klopt dat ik nog geen filter heb meegegeven aan de Procedure ik wil eerst kijken of het mij wel ging lukken.
Wat gek ik is toen ik SSMS opstartte en

Code:

EXEC dbo.EECQVandaagSom_Procedure;

liep dit snel minder dan 1 seconde.
Daarna 100 PERCENT aangepast naar TOP 100 en dat leek nog wel sneller te zijn

.
Gek genoeg als ik de Procedure dan later weer aanroep neemt het zomaar weer 40 seconde.
Als ik het dan later weer veelvuldig probeer dan is het er binnen 1 seconde.

Nog even verder testen. En eens kijk hoe ik het filter mee kan geven aan de Procedure.

Wat de Views betreft ben ik bang dat ik ze wel nodig heb. Ieder View zorgt voor een deel van het overzicht. Het zou zeker beter kunnen als ik alles uit de tabellen haal, echter ben ik bang dat ik dan kennis te kort kom. Maar ik ga er zeker naar kijken.

NoellaG · 5 sep 2024

Niet zo gek dat aanpassen van TOP 100 PERCENT naar TOP 100 sneller gaat. TOP 100 PERCENT haalt alle records op (100% van het totaal = alles); TOP 100 haalt maar 100 recs op. Ook niet zo gek dat als je de query 2 keer na elkaar draait de tweede keer sneller is, dan zitten alle pagina's immers nog in de buffer en worden uit het geheugen gelezen zonder dat er van disk moet gelezen worden.
Als het 'later' maar enkele minuten later is moet je wel eens je PLE (Page Life Expectancy) nakijken.

Kijk zeker ook je query plan na, want het is niet omdat het uiteindelijk resulaat maar 12 records is dat er ook maar zoveel moeten gelezen worden. Met al die views kan het best zijn dat de engine duizenden lijnen moet lezen voordat deze aan het uiteindelijk resultaat komt. Dat hangt van alle linken en indexen af.

OctaFish · 5 sep 2024

TOP 100 of TOP 100% zal in dit geval niet zoveel uitmaken, want het resultaat is een recordset met 8 records. 1oo% van 8 is..... tromgeroffel..... 8. En Top 100 zal nooit 92 lege records laten zien. Voor Top 100 heb je op zijn minst een recordset van 101 records nodig. Weglaten is dus het beste, want dan heb je geen enkele 'restrictie' meegegeven.

NoellaG · 5 sep 2024

ik weet natuurlijk niet hoe het werkt voor Access, maar voor SQL server maakt dit wel degelijk een verschil bij het opstellen van het executieplan. Dan wordt er rekening gehouden met hoeveel recs in het resultaat verwacht worden. Bij 100% is dit aantal dan nog onbekend, bij 100, wel dan is dit max 100.

Opgelost Hoe resultaten weergave te versnellen

Terugkerende gebruiker

Gebruiker

Verenigingslid

Gebruiker

Gebruiker

Verenigingslid

Gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Gebruiker

Terugkerende gebruiker

Gebruiker

Verenigingslid

Gebruiker

Terugkerende gebruiker

Verenigingslid

Terugkerende gebruiker

Wij waarderen jouw privacy