Solved Recordset of a function that takes a long time to load

[zelarra821]

Good afternoon.

I have a form where I show a graph based on the year that I select in a drop-down, and my problem is that it takes half a life to load, and it hangs for a while thinking.

This is the event in the form:

Private Sub Año1_AfterUpdate()
Dim Año As String
    Año = Me.Año2
    Me.Grafica.RowSource = "SELECT EstacionAñoYResultado([Fecha],[Resultado]) AS Estacion, PorcentajeEstacionAño([Fecha],[Resultado]) AS PorcentajeEstacionAño" _
                            & " FROM TResultados INNER JOIN TServicio ON TResultados.CodigoResultado = TServicio.CodigoResultado" _
                            & " WHERE Year([Fecha]) = " & Año & "" _
                            & " GROUP BY EstacionAñoYResultado([Fecha],[Resultado]), PorcentajeEstacionAño([Fecha],[Resultado]), Year([Fecha]), OrdenEstacion([Fecha]), TResultados.CodigoResultado" _
                            & " ORDER BY Year([Fecha]), OrdenEstacion([Fecha]), TResultados.CodigoResultado"
End Sub

As you can see, there are three custom functions, and one of them is the one that gives me problems (PercentajeEstacionYear).

I've read more posts on this forum, and you recommend using Recordset (I use it). They also recommended me once for something totally different to use the Where clause to filter the data, because, if you have 1000 records, with a Where you only load the 100 you want to use, and not the 1000 (I also use that). I have simplified the recordset to the maximum, and neither.

Here is the custom function (there is one, but you need two more for the calculation):

Public Function PorcentajeEstacionAño(Fecha As Date, miResultado As String) As Double
    PorcentajeEstacionAño = VecesResultado(Fecha, miResultado) / VecesPeriodo(Fecha)
End Function

Public Function VecesPeriodo(Fecha As Date) As Double
Dim rst As DAO.Recordset
Dim strVecesPeriodo As String
    strVecesPeriodo = "SELECT Count(TResultados.Resultado) AS VecesPeriodo" _
            & " FROM TResultados INNER JOIN TServicio ON TResultados.CodigoResultado = TServicio.CodigoResultado" _
            & " WHERE (((EstacionYAño([Fecha]))='" & EstacionYAño(Fecha) & "'))"
    Set rst = CurrentDb.OpenRecordset(strVecesPeriodo)
    If Not (rst.EOF And rst.BOF) Then
         VecesPeriodo = rst("VecesPeriodo")
    End If
    rst.Close
    Set rst = Nothing
End Function
    
Public Function VecesResultado(Fecha As Date, miResultado As String) As Double
Dim rst As DAO.Recordset
Dim strVecesResultado As String
    
    strVecesResultado = "SELECT Count(TResultados.Resultado) AS VecesResultado" _
                        & " FROM TResultados INNER JOIN TServicio ON TResultados.CodigoResultado = TServicio.CodigoResultado" _
                        & " WHERE (((EstacionYAño([Fecha]))='" & EstacionYAño(Fecha) & "')) And Resultado ='" & miResultado & "'"
    Set rst = CurrentDb.OpenRecordset(strVecesResultado)
    If Not (rst.EOF And rst.BOF) Then
         VecesResultado = rst("VecesResultado")
    End If
    rst.Close
    Set rst = Nothing
End Function

And it is not a problem of the number of records because, although the table stores 1135 records, then for each year there are between 200-300 records no more, and I filter so that it uses only those 200-300 records.

Can anyone advise me something?

Thank you!

 

[theDBguy]

What is a "long time" to load? I mean, how much time is it taking to load for 200-300 records in a year? If you run that SQL as a query, does it run any faster?

 

[Gasman]

All I can offer is that you are running those functions in the select and then again in the GROUP BY.
Have you tried getting the data in one query and use in another.?

 

[zelarra821]

What is a "long time" to load? I mean, how much time is it taking to load for 200-300 records in a year? If you run that SQL as a query, does it run any faster?

What I mean is, it takes like 4-5 seconds to load, and Access hangs. If I run it in a query, the exact same thing happens.

 

[zelarra821]

All I can offer is that you are running those functions in the select and then again in the GROUP BY.
Have you tried getting the data in one query and use in another.?

Are you telling me to create a query (not with VBA) with what's in GROUP BY? If so, I don't care because the query you create also runs very slow. The problem is with the function that divides the result of the other two functions.

 

[Gasman]

Are you telling me to create a query (not with VBA) with what's in GROUP BY? If so, I don't care because the query you create also runs very slow. The problem is with the function that divides the result of the other two functions.

What I am saying is if you run a query with those functions, you will have hard and set values.? Then you just group on those values in another query, rather than calculating all over again?
Plus those functions appear to only return one record?, so I dare say the experts can offer another method.?

I will now bow out.

 

[zelarra821]

Let's see, I've done what Gasman says. Also, I have changed the WHERE clauses to numeric criteria.

Here is the code in which I set the Recordset of the chart:

Private Sub Año1_AfterUpdate()
Dim Año As String
    Año = Me.Año2
    Me.Grafica.RowSource = "SELECT EstacionAñoYResultado, [VecesResultado]/[VecesPeriodo] AS PorcentajeEstacionAño" _
                            & " FROM CServicioAño" _
                            & " WHERE Año = " & Año & "" _
                            & " GROUP BY EstacionAñoYResultado, [VecesResultado]/[VecesPeriodo], OrdenEstacion, CodigoResultado" _
                            & " ORDER BY OrdenEstacion, CodigoResultado"
End Sub

Here, the SQL of the previous query (CServicioAño):

SELECT EstacionAñoYResultado([Fecha],[TResultados].[Resultado]) AS EstacionAñoYResultado, VecesPeriodo([Fecha]) AS VecesPeriodo, VecesResultado([Fecha],[TResultados].[CodigoResultado]) AS VecesResultado, Year([Fecha]) AS Año, OrdenEstacion([Fecha]) AS OrdenEstacion, TServicio.CodigoResultado
FROM TResultados INNER JOIN TServicio ON TResultados.CodigoResultado = TServicio.CodigoResultado;

And then the two functions (I have removed the function that divided the result of the two functions, and I have done it in the previous query):

Public Function VecesPeriodo(Fecha As Date) As Double
Dim rst As DAO.Recordset
Dim strVecesPeriodo As String
    strVecesPeriodo = "SELECT Count(TResultados.Resultado) AS VecesPeriodo" _
            & " FROM TResultados INNER JOIN TServicio ON TResultados.CodigoResultado = TServicio.CodigoResultado" _
            & " WHERE (Year(Fecha) & OrdenEstacion([Fecha]))=" & Year(Fecha) & OrdenEstacion([Fecha]) & ""
    Set rst = CurrentDb.OpenRecordset(strVecesPeriodo)
    If Not (rst.EOF And rst.BOF) Then
         VecesPeriodo = rst("VecesPeriodo")
    End If
    rst.Close
    Set rst = Nothing
End Function
    
Public Function VecesResultado(Fecha As Date, miResultado As Integer) As Double
Dim rst As DAO.Recordset
Dim strVecesResultado As String
    
    strVecesResultado = "SELECT Count(TResultados.Resultado) AS VecesResultado" _
                        & " FROM TResultados INNER JOIN TServicio ON TResultados.CodigoResultado = TServicio.CodigoResultado" _
                        & " WHERE (Year(Fecha) & OrdenEstacion([Fecha]))=(" & Year(Fecha) & OrdenEstacion([Fecha]) & ") And TServicio.CodigoResultado =" & miResultado & ""
    Set rst = CurrentDb.OpenRecordset(strVecesResultado)
    If Not (rst.EOF And rst.BOF) Then
         VecesResultado = rst("VecesResultado")
    End If
    rst.Close
    Set rst = Nothing
End Function

By making these changes, I have realized that the problem comes when I have to group the data. This is what makes the query load very slow.

The previous query is not grouped, as you can see. But then the query that serves as the source of the graph does.

How can i fix this?

 

[theDBguy]

Hi. One potential approach, if you can adapt it, is to create a temporary table for your grouped data to feed your graph/chart.

 

[zelarra821]

Hi. One potential approach, if you can adapt it, is to create a temporary table for your grouped data to feed your graph/chart.

How can I do it?

 

[zelarra821]

Could you give me an example?

 

[theDBguy]

Could you give me an example?

Not in front of a computer now, but you could copy your group by query and convert it into a make-table query and then use that table for your graph.

 

[Isaac]

Mixing VBA functions in a GroupBy query has been a recipe for disastrous performance in my personal experience.

May want to do what dbGuy mentioned -

I think what I would try first is:

1) edit the query so as NOT to group by
2) dump data from the query in a table
3) create a new query sourced from the table, and in here, do the group by

...write code to perform that if necessary to source the chart.

 

[zelarra821]

Not in front of a computer now, but you could copy your group by query and convert it into a make-table query and then use that table for your graph.

I don't want to leave you unanswered. I'm going to do a mix between what you propose and what Isaac says. Thanks.

 

[zelarra821]

Mixing VBA functions in a GroupBy query has been a recipe for disastrous performance in my personal experience.

May want to do what dbGuy mentioned -

I think what I would try first is:

1) edit the query so as NOT to group by
2) dump data from the query in a table
3) create a new query sourced from the table, and in here, do the group by

...write code to perform that if necessary to source the chart.

You say it is a bad idea to mix functions and GROUP BY. Agree.

However, I put you in a situation.

I have a table in which I add a parameter every day. So I have the Date fields and the Parameter field.

I have records since 2017, therefore I want to have an annual summary.

To do this, the only thing I can think of is to create a first query, where I group the dates of that table, by year. So, I have a query with the 4 years in which I have records. But note that here already in the GROUP BY there is going to be a function, which is Year (Date).

Now, in a second query, add the previous query and the custom functions created by me to its source.

I don't know if you understand me, and if this option would be correct for the performance of the database.

Thank you!

 

[zelarra821]

I have done the following. I have created a query to only extract the period, for which I have no choice but to group, as I explained in my previous message:

SELECT EstacionAñoYResultado([Fecha],[TResultados].[Resultado]) AS EstacionAñoYResultado, Year([Fecha]) AS Año, OrdenEstacion([Fecha]) AS OrdenEstacion, TServicio.CodigoResultado
FROM TResultados INNER JOIN TServicio ON TResultados.CodigoResultado = TServicio.CodigoResultado
GROUP BY EstacionAñoYResultado([Fecha],[TResultados].[Resultado]), Year([Fecha]), OrdenEstacion([Fecha]), TServicio.CodigoResultado;

And then, I have used that query to create the source of the chart:

Private Sub Año1_AfterUpdate()
Dim Año As String
    Año = Me.Año2
    Me.Grafica.RowSource = "SELECT EstacionAñoYResultado, PorcentajeEstacionAño([Año],[OrdenEstacion],[CodigoResultado]) AS PorcentajeEstacionAño" _
                            & " FROM CEstacionAñoyResultado" _
                            & " WHERE CEstacionAñoyResultado.Año=" & Año & "" _
                            & " ORDER BY Año, OrdenEstacion, CodigoResultado"
End Sub

It's going so much better than before. I no longer know if what I have done can be improved in something else.

Thank you very much for the help.

 

[Isaac]

Glad to hear you got something working

 

[theDBguy]

Ditto! Glad to hear you made good progress. Good luck with your project.