# SolvedRecordset of a function that takes a long time to load (1 Viewer)

#### zelarra821

##### Registered User.
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:

Code:
``````Private Sub Año1_AfterUpdate()
Dim Año As String
Año = Me.Año2
& " WHERE Year([Fecha]) = " & Año & "" _
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):

Code:
``````Public Function PorcentajeEstacionAño(Fecha As Date, miResultado As String) As Double
End Function

Public Function VecesPeriodo(Fecha As Date) As Double
Dim rst As DAO.Recordset
Dim strVecesPeriodo As String
& " 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

Dim rst As DAO.Recordset

& " WHERE (((EstacionYAño([Fecha]))='" & EstacionYAño(Fecha) & "')) And Resultado ='" & miResultado & "'"
If Not (rst.EOF And rst.BOF) Then
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.

Thank you!

#### theDBguy

##### I’m here to help
Staff member
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

##### Enthusiastic Amateur
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

##### Registered User.
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

##### Registered User.
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

##### Enthusiastic Amateur
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

##### Registered User.
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:

Code:
``````Private Sub Año1_AfterUpdate()
Dim Año As String
Año = Me.Año2
& " FROM CServicioAño" _
& " WHERE Año = " & Año & "" _
& " ORDER BY OrdenEstacion, CodigoResultado"
End Sub``````

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

Code:
``````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

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):

Code:
``````Public Function VecesPeriodo(Fecha As Date) As Double
Dim rst As DAO.Recordset
Dim strVecesPeriodo As String
& " 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

Dim rst As DAO.Recordset

& " WHERE (Year(Fecha) & OrdenEstacion([Fecha]))=(" & Year(Fecha) & OrdenEstacion([Fecha]) & ") And TServicio.CodigoResultado =" & miResultado & ""
If Not (rst.EOF And rst.BOF) Then
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

##### I’m here to help
Staff member
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

##### Registered User.
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

##### Registered User.
Could you give me an example?

#### theDBguy

##### I’m here to help
Staff member
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

##### Lifelong Learner
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

##### Registered User.
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

##### Registered User.
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

##### Registered User.
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:

Code:
``````SELECT EstacionAñoYResultado([Fecha],[TResultados].[Resultado]) AS EstacionAñoYResultado, Year([Fecha]) AS Año, OrdenEstacion([Fecha]) AS OrdenEstacion, TServicio.CodigoResultado

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

Code:
``````Private Sub Año1_AfterUpdate()
Dim Año As String
Año = Me.Año2
& " 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

##### Lifelong Learner
Glad to hear you got something working

Staff member

Replies
4
Views
165
Replies
183
Views
1,389
Replies
10
Views
81
Replies
7
Views
96
Replies
6
Views
144