Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2017, 06:47 AM   #1
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,545
Thanks: 189
Thanked 219 Times in 205 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Speed this up at all?

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.
Attached Files
File Type: zip Holiday Planner V2_be.zip (183.9 KB, 22 views)

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-17-2017, 06:50 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,567
Thanks: 0
Thanked 545 Times in 531 Posts
Ranman256 will become famous soon enough
Re: Speed this up at all?

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)
Ranman256 is offline   Reply With Quote
Old 07-17-2017, 07:45 AM   #3
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,545
Thanks: 189
Thanked 219 Times in 205 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: Speed this up at all?

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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-17-2017, 09:40 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,597
Thanks: 37
Thanked 3,105 Times in 3,016 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: Speed this up at all?

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-17-2017, 10:54 AM   #5
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,545
Thanks: 189
Thanked 219 Times in 205 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: Speed this up at all?

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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-17-2017, 02:55 PM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,597
Thanks: 37
Thanked 3,105 Times in 3,016 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: Speed this up at all?

ID fields are OK, it's fields like your start and end date which are not indexed
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 07-18-2017 at 12:10 AM.
CJ_London is offline   Reply With Quote
Old 07-17-2017, 06:38 PM   #7
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 10,803
Thanks: 36
Thanked 872 Times in 784 Posts
The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all
Re: Speed this up at all?

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over six months and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 07-17-2017, 10:06 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 10,816
Thanks: 58
Thanked 1,309 Times in 1,234 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Speed this up at all?

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.
Galaxiom is offline   Reply With Quote
Old 07-18-2017, 12:47 AM   #9
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 704
Thanks: 3
Thanked 168 Times in 161 Posts
static will become famous soon enough
Re: Speed this up at all?

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.
static is offline   Reply With Quote
Old 07-18-2017, 01:34 AM   #10
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,545
Thanks: 189
Thanked 219 Times in 205 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: Speed this up at all?

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.

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 07-18-2017, 02:51 AM   #11
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 10,816
Thanks: 58
Thanked 1,309 Times in 1,234 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Speed this up at all?

Quote:
Originally Posted by Gasman View Post
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.
Quote:
Code:
Public Function NewHolidaysRemaining(pEmployeeID As Long, pDate As Date)

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


'strSQL = strSQL & " WHERE (((Year([StartDate]))=" & Year(pDate) & ") AND ((Year([EndDate]))=" & Year(pDate) & ")"
Galaxiom is offline   Reply With Quote
Old 07-18-2017, 03:20 AM   #12
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,545
Thanks: 189
Thanked 219 Times in 205 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: Speed this up at all?

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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
A Need for Speed Rich1968 SQL Server 11 10-01-2012 11:37 AM
The need for speed WhizzkidWallace General 21 10-27-2005 12:07 AM
speed in look ups scratch Modules & VBA 4 08-24-2005 12:22 AM
Need 4 Speed bodvoc General 13 05-12-2003 05:09 AM
[SOLVED] Need for Speed deetee General 3 02-18-2003 02:26 PM




All times are GMT -8. The time now is 02:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World