Speed this up at all? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 17:34
Joined
Sep 21, 2011
Messages
14,044
Hi all,

I obtained a Holiday Planner from the net.
We have been using it as a single user mde, but I have been so used to having split DB that I amended the code forgetting that the tables in my mdb version were empty, and the tables in the mde version had the current holiday entitlements.

I managed to get a good version from backup. Phew!

So I have split the mdb, but it appears very slow. The BE is in the same place as my other BE DBs and they perform OK. Perhaps it is because it is mdb snd not sccdb?, so I upgraded both FE and BE to Access 2007, but no difference?

I've attached both FE & BE and would be grateful if anyone could give me any pointers to speed it up. It does behave very slow compared to my other DBs with much more data. I've looked at the indices and they seemed adequate, but added a few for dates as well.

pw to login is torch1234
Many thanks.
 

Attachments

  • Holiday Planner V2_be.zip
    183.9 KB · Views: 58

Ranman256

Well-known member
Local time
Today, 13:34
Joined
Apr 9, 2015
Messages
4,339
slowness could be network speed
cable type
network card speed
an uncompacted db (compact /repair frequently)

I have a spit db on a network and the speed is fast. (getting 100k recs in a second)
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:34
Joined
Sep 21, 2011
Messages
14,044
Hi Ranman256,
That is just it, the other DBs I have created are in the same location FE and BE and behave much faster than this DB even with ten times the amount of data.?
For that reason I do not believe it is the network?
Neither are the other DBs accessed at the same time.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:34
Joined
Feb 19, 2013
Messages
16,553
works OK for me - but I do have a pretty fast network.

By the way, the password is torch2315.

might have something to do with using OLE objects. Also some fields are not indexed which form part of your relationships or are used in criteria (maybe sorting as well). Looks like you have just indexed those fields that end in ID. But on the dataset provided, doesn't make a significant difference to performance
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:34
Joined
Sep 21, 2011
Messages
14,044
CJ_London,

Oops sorry about the password.
I haven't touched it in a while and I believe all the ID fields were indexed when i found it.
I have the original, so will compare tomorrow.

It is just the delay when moving weeks and displaying the remaining amount of holidays left. It just became noticeable after i split the DB.
I am trying to work out if I even need to refresh it each time.

Thank you for taking a look.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:34
Joined
Feb 19, 2013
Messages
16,553
ID fields are OK, it's fields like your start and end date which are not indexed
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:34
Joined
Feb 28, 2001
Messages
26,999
Can you quantify "slow" either in absolute terms or in specific numeric terms relative to your comparison databases? How do you know it has bad performance? If we can get a handle on "slow" then we can try to figure out what is taking so long.

One of my biggest projects was on an .MDB and once we got the network issues clarified it blazed. So I'm disinclined to talk about it as an .MDB vs. something-else issue.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:34
Joined
Jan 20, 2009
Messages
12,849
I have not looked at the database.

Being mdb doesn't cause problems.

More likely that query design in the front end is the issue. Indexes only work if the queries are designed to use them.
 

static

Registered User.
Local time
Today, 17:34
Joined
Nov 2, 2015
Messages
823
Using image paths would probably be better. A path will be shorter than image data and the form might be intelligent enough to use a cache so it only needs to load images once.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:34
Joined
Sep 21, 2011
Messages
14,044
I've amended the code to calculate the holidays to a simple DSum instead of the recordset. I've left everything in so you can see what I was originally using.
I also tried using a date, rather than Year of a date, but that seemed slower.

That has at least made it a little faster to display the holidays remaining.

I am not sure if I even have to recalculate each time I move dates, as the names remain static and I would only really need to calculate after holidays have been added/deleted or amended, but will have to take some time getting to understand it again.

For now it is sufficient for my needs..

CJ_London: I also indesed the start and end dates in the tblHolidayDates.

The _Doc_Man: Just that there is a noticable pause before the calculated days are displayed, when the images are pretty much instant, but they come from a query on a single table.

static: It was hard enough for me to modify for the holidays, so will leave the images as they are for now.:D

Galaxiom: It is my function that was slow. Query for the rest of the data appears to be fine.

Thanks everyone.

Code:
Public Function NewHolidaysRemaining(pEmployeeID As Long, pDate As Date)
On Error GoTo Err_Handler

Dim iHolidaysAllowed As Integer, iHolidaysUsed As Integer
Dim strSQL As String, strCriteria As String
Dim db As Database
Dim rst As Recordset
'Dim dtStart As Date, dtEnd As Date

Set db = CurrentDb()
iHolidaysAllowed = Nz(DLookup("AllowedHolidays", "tblEmployees", "[EmployeeID]=" & pEmployeeID), 0)
'iHolidaysUsed = Nz(DSum("Holidays", "tblHolidayDates", "[EmployeeID]=" & pEmployeeID & " AND [HolidayType]=5"), 0)
'dtStart = DateSerial(Year(pDate), 1, 1)
'dtEnd = DateSerial(Year(pDate), 12, 31)

strCriteria = "EmployeeID = " & pEmployeeID & " AND YEAR([StartDate])= " & Year(pDate) & " AND YEAR([EndDate])= " & Year(pDate)
'strCriteria = "EmployeeID = " & pEmployeeID & " AND [StartDate]>= #" & dtStart & "# AND [EndDate]<= #" & dtEnd & "#"

iHolidaysUsed = Nz(DSum("Holidays", "tblHolidaydates", strCriteria), 0)

'strSQL = "SELECT Workdays([StartDate],[Enddate],'tblPublicHols','HolidayDate') AS HolsBooked, tblHolidayDates.EmployeeID, tblHolidayDates.StartDate, tblHolidayDates.EndDate "
'strSQL = strSQL & " FROM tblHolidayDates"
'strSQL = strSQL & " WHERE (((Year([StartDate]))=" & Year(pDate) & ") AND ((Year([EndDate]))=" & Year(pDate) & ")"
'strSQL = strSQL & " GROUP BY tblHolidayDates.EmployeeID"
'strSQL = strSQL & " HAVING ((tblHolidayDates.EmployeeID)= " & pEmployeeID & "))"

'Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'If Not rst.EOF Then
    'rst.MoveFirst
    'iHolidaysUsed = 0
   ' Do While Not rst.EOF
        'iHolidaysUsed = iHolidaysUsed + rst!HolsBooked
       ' rst.MoveNext
    'Loop
'End If

NewHolidaysRemaining = iHolidaysAllowed - iHolidaysUsed

ExitFunction:
    Set db = Nothing
    Set rst = Nothing

Err_Exit:
    Exit Function
    
Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitFunction

End Function
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:34
Joined
Jan 20, 2009
Messages
12,849
Galaxiom: It is my function that was slow. Query for the rest of the data appears to be fine.

Stepping through recordsets is always going to be slower than a query.

Applying a function that opens a recordset for each employee will be very slow. Using a Domain function will still be slow. Try to do it by joining the tables so the engine can do its group processing. Remember the SQL View can support joins that can't be done in the Design View.

Wherever it is done, applying a function, any function, to every record before the Select can be applied will slow a query.
Code:
Public Function NewHolidaysRemaining(pEmployeeID As Long, pDate As Date)

strCriteria = "EmployeeID = " & pEmployeeID & " AND[B] YEAR([StartDate])[/B]= " & Year(pDate) & " AND [B]YEAR([EndDate])[/B]= " & Year(pDate)
'strCriteria = "EmployeeID = " & pEmployeeID & " AND [StartDate]>= #" & dtStart & "# AND [EndDate]<= #" & dtEnd & "#"


'strSQL = strSQL & " WHERE ((([B]Year([StartDate])[/B])=" & Year(pDate) & ") AND ([B](Year([EndDate])[/B])=" & Year(pDate) & ")"
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:34
Joined
Sep 21, 2011
Messages
14,044
Thank You Galaxiom,

If I get time I will see if I can join the tblHolidayDates to the existing query, but for now I will have to leave it as it is I'm afraid, more important things to work on. It is slightly faster though, so a little progress made.:D
 

Users who are viewing this thread

Top Bottom