473,722 Members | 2,341 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

slow query

My problem:
I'm having trouble with a query taking much too long to run; a query
without any criteria evaluating only 650 records takes over 300 seconds
to run (over the network. On local drive takes 120 seconds).

The Setup:
I'm running Access 97 non-developer edition. I have exactly zero other
tools to use and no way to change that =(. My database is compiled and
resides on a network drive. The database has not been split into a
front and backend, as I don't have the tools to effectively
redistribute the front end to the more than 350 (occasional) users
every time I make a change. Although the database currently holds only
650 records, it is expected to contain around 10,000 records after it
has been up and running for a few months. (I.E., my problem will get
worse, not better). The results of this query display in a read only
continuous forms format.

I've isolated 98% of the problem down to a counter routine I've called
in the query. This routine tallies and displays the number of days in a
date span minus weekends and company holidays. The purpose is to
display the total age of an 'incident' (one record), the length of time
it has been in its current department, and the length of time its
current owner has 'owned' an incident. With these counters disabled,
the query runs in 5 seconds, (which is perfectly acceptable). I need to
get this query to display all the numbers I need it to in no more than
10 seconds.

The routine itself I shamelessly borrowed from another article as a
workaround (regrettably, I don't remember his name) when I discovered
that the DateDiff function provided with A97 couldn't correctly compute
weekdays only. (Apparently the "w" argument in the DateDiff function of
A97 does not, has not, nor ever will function correctly)

The problematic code is pasted below. I've been looking for ways to
tweak it a bit, and managed to increase performance by 40% by moving
the SET db and RST statements to a separate public function that runs
only once rather than each and every time the loop runs. It is still
too slow though.

This code iterates one time for every day between two date spans and
increments a counter if the date it is currently evaluating is not a
weekend or company holiday.

Public Function srcWorkingDays2 (StartDate As Date, EndDate As Date) As
Double
Dim intCount As Double
Dim RST As Recordset
Dim db As Database

Set db = CurrentDb
Set RST = db.OpenRecordse t("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

intCount = 0

Do While StartDate < EndDate

RST.FindFirst "[HolidayDate] = #" & StartDate & "#"
If WeekDay(StartDa te) <> vbSunday And WeekDay(StartDa te) <> vbSaturday
Then
If RST.NoMatch Then
intCount = intCount + 1
End If
End If

StartDate = StartDate + 1

Loop

srcWorkingDays2 = intCount
End Function

This is an example of one of the expressions in the query that calls
this function:
TOTDaysOpen:
srcworkingdays2 (Format$([currentTOTdate],"mm/dd/yyyy"),IIf(IsNu ll([dateclosed]),Date(),Format $([dateclosed],"mm/dd/yyyy")))

A big problem area in the VB part of the code seems to be the FindFirst
action. Commenting out just the Holidays part of the calculation
slashes query times by 60%. I've also noticed that Access seems to be
running the query four complete times every time a user hits the search
button on my search-input screen. This appears to be some internal Jet
workings, and I have no idea how to put a stop to it. If that could be
stopped that would slash query times by another 75%.

However, even with both of those improvements the query time would
still be well over the 10-second mark. What I'm thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.

I haven't the foggiest idea how I might go about setting that up.

Any tips or suggestions any of you may have would be much appreciated.

Dec 28 '05 #1
11 3142
On 28 Dec 2005 15:04:11 -0800, An***********@b cbsmn.com wrote:

However, even with both of those improvements the query time would
still be well over the 10-second mark. What I'm thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.

I haven't the foggiest idea how I might go about setting that up.

Any tips or suggestions any of you may have would be much appreciated.


Take your srcWorkingDays2 function out of the query all together and call it from a text box in the continuous form.

Assuming you have textboxes in the continuous form called StartDate and EndDate, add a textbox and as it's control
source put -
=srcWorkingDays 2([StartDate],[EndDate])

The function will only be called for records currently displayed in the continuous form. You may however get a slight
delay as each record is evaluated but this usually insignificant.
Wayne Gillespie
Gosford NSW Australia
Dec 29 '05 #2
My suggestion is that in a couple of sentences you describe exactly
what it is that you want to do without reference to any code but
describing your tables carefully and ask for suggestions as to how to
do it.

Dec 29 '05 #3
An***********@b cbsmn.com wrote:
My problem:
I'm having trouble with a query taking much too long to run; a query
without any criteria evaluating only 650 records takes over 300 seconds
to run (over the network. On local drive takes 120 seconds).

The Setup:
I'm running Access 97 non-developer edition. I have exactly zero other
tools to use and no way to change that =(. My database is compiled and
resides on a network drive. The database has not been split into a
front and backend, as I don't have the tools to effectively
redistribute the front end to the more than 350 (occasional) users
every time I make a change. Although the database currently holds only
650 records, it is expected to contain around 10,000 records after it
has been up and running for a few months. (I.E., my problem will get
worse, not better). The results of this query display in a read only
continuous forms format.

I've isolated 98% of the problem down to a counter routine I've called
in the query. This routine tallies and displays the number of days in a
date span minus weekends and company holidays. The purpose is to
display the total age of an 'incident' (one record), the length of time
it has been in its current department, and the length of time its
current owner has 'owned' an incident. With these counters disabled,
the query runs in 5 seconds, (which is perfectly acceptable). I need to
get this query to display all the numbers I need it to in no more than
10 seconds.

The routine itself I shamelessly borrowed from another article as a
workaround (regrettably, I don't remember his name) when I discovered
that the DateDiff function provided with A97 couldn't correctly compute
weekdays only. (Apparently the "w" argument in the DateDiff function of
A97 does not, has not, nor ever will function correctly)

The problematic code is pasted below. I've been looking for ways to
tweak it a bit, and managed to increase performance by 40% by moving
the SET db and RST statements to a separate public function that runs
only once rather than each and every time the loop runs. It is still
too slow though.

This code iterates one time for every day between two date spans and
increments a counter if the date it is currently evaluating is not a
weekend or company holiday.

Public Function srcWorkingDays2 (StartDate As Date, EndDate As Date) As
Double
Dim intCount As Double
Dim RST As Recordset
Dim db As Database

Set db = CurrentDb
Set RST = db.OpenRecordse t("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

intCount = 0

Do While StartDate < EndDate

RST.FindFirst "[HolidayDate] = #" & StartDate & "#"
If WeekDay(StartDa te) <> vbSunday And WeekDay(StartDa te) <> vbSaturday
Then
If RST.NoMatch Then
intCount = intCount + 1
End If
End If

StartDate = StartDate + 1

Loop

srcWorkingDays2 = intCount
End Function

This is an example of one of the expressions in the query that calls
this function:
TOTDaysOpen:
srcworkingdays2 (Format$([currentTOTdate],"mm/dd/yyyy"),IIf(IsNu ll([dateclosed]),Date(),Format $([dateclosed],"mm/dd/yyyy")))

A big problem area in the VB part of the code seems to be the FindFirst
action. Commenting out just the Holidays part of the calculation
slashes query times by 60%. I've also noticed that Access seems to be
running the query four complete times every time a user hits the search
button on my search-input screen. This appears to be some internal Jet
workings, and I have no idea how to put a stop to it. If that could be
stopped that would slash query times by another 75%.

However, even with both of those improvements the query time would
still be well over the 10-second mark. What I'm thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.

I haven't the foggiest idea how I might go about setting that up.

Any tips or suggestions any of you may have would be much appreciated.


After reflection it seems there should be a way to count the weekend
days and holidays without going through each date individually. That
reflection caused me to think of:

SELECT IncidentID, [EndDate] - [StartDate] + 1 - (SELECT
Count(HolidayDa te) FROM tblHolidays WHERE (HolidayDate BETWEEN
[StartDate] AND [EndDate]) AND Weekday(Holiday Date) <> 1 AND
Weekday(Holiday Date) <> 7) - CountWeekendDay s([StartDate], [EndDate])
AS WorkingDays FROM tblIncidents;

That is, (the number of workdays) = (the number of days between
StartDate and EndDate inclusive) - (holidays that are not on a weekend)
- (weekend days). That leaves the CountWeekendDay s function to do.

'Start Module Code---
Public Function CountWeekendDay s(dtStart As Date, dtEnd As Date)
Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDay s = 0
intSat = (LEDay(dtEnd, 7) - GEDay(dtStart, 7)) / 7 + 1
intSun = (LEDay(dtEnd, 1) - GEDay(dtStart, 1)) / 7 + 1
CountWeekendDay s = (intSat + intSun + Abs(intSat) + Abs(intSun)) / 2
End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function
'End Module Code-----

Note: I whipped this up rather quickly with almost no testing so be
sure to test it and verify the logic before using. LEDay finds the
previous vbDay on or before the given date. Also note that the
firstdayofweek parameter of the Weekday function can be used instead as
an aid to get the previous or following weekend day. (X + Abs(X)) / 2
just gives X when X is positive and 0 otherwise. The IIf or Sgn
functions could have worked for this purpose also. The '<> 1' and '<>
7' can use '> 1' and '< 7' in the SQL statement instead. I'm going to
have to change some other code I have soon for calculating workdays so
that large date ranges don't cause a performance issue since I
implemented it by stepping through dates. I personally don't use a
holiday table so I'm going to have to rethink the best way to handle
that situation as well. I thought Wayne's idea was excellent.

James A. Fortune
CD********@Fort uneJames.com

Dec 29 '05 #4
Thanks so much for all the feedback. I'll try and give your ideas a try
today.

Dec 29 '05 #5
CD********@Fort uneJames.com wrote in
news:11******** *************@z 14g2000cwz.goog legroups.com:

After reflection it seems there should be a way to count the
weekend days and holidays without going through each date
individually. That reflection caused me to think of:

SELECT IncidentID, [EndDate] - [StartDate] + 1 - (SELECT
Count(HolidayDa te) FROM tblHolidays WHERE (HolidayDate BETWEEN
[StartDate] AND [EndDate]) AND Weekday(Holiday Date) <> 1 AND
Weekday(Holiday Date) <> 7) - CountWeekendDay s([StartDate],
[EndDate]) AS WorkingDays FROM tblIncidents;

That is, (the number of workdays) = (the number of days
between StartDate and EndDate inclusive) - (holidays that are
not on a weekend) - (weekend days). That leaves the
CountWeekendDay s function to do.

'Start Module Code---
Public Function CountWeekendDay s(dtStart As Date, dtEnd As
Date) Dim intSat As Integer
Dim intSun As Integer

'This function assumes dtStart <= dtEnd
CountWeekendDay s = 0
intSat = (LEDay(dtEnd, 7) - GEDay(dtStart, 7)) / 7 + 1
intSun = (LEDay(dtEnd, 1) - GEDay(dtStart, 1)) / 7 + 1
CountWeekendDay s = (intSat + intSun + Abs(intSat) +
Abs(intSun)) / 2 End Function

Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function

Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function
'End Module Code-----

Note: I whipped this up rather quickly with almost no testing
so be sure to test it and verify the logic before using.
LEDay finds the previous vbDay on or before the given date.
Also note that the firstdayofweek parameter of the Weekday
function can be used instead as an aid to get the previous or
following weekend day. (X + Abs(X)) / 2 just gives X when X
is positive and 0 otherwise. The IIf or Sgn functions could
have worked for this purpose also. The '<> 1' and '<> 7' can
use '> 1' and '< 7' in the SQL statement instead. I'm going
to have to change some other code I have soon for calculating
workdays so that large date ranges don't cause a performance
issue since I implemented it by stepping through dates. I
personally don't use a holiday table so I'm going to have to
rethink the best way to handle that situation as well. I
thought Wayne's idea was excellent.

James A. Fortune
CD********@Fort uneJames.com

Interesting technique.

Years ago I used a different calendar table (in foxBase Plus as
a time reference). The
table had three fields: Workdate, daynum, isholiday. Daynum
incremented on workdays, but did not increment on weekends or
holidays, so daynum of enddate minus daynum of startdate.plus 1
gave the number of working days between the two dates. The plus
one comes from the fact that the start of startdate is start of
workday and end of enddate is end of workday, YMMV

It would be fast in Access although a little wasteful of
diskspace.
--
Bob Quintal

PA is y I've altered my email address.
Dec 30 '05 #6
On Wed, 28 Dec 2005 23:51:51 GMT, Wayne Gillespie
<be*****@NOhotm ailSPAM.com.au> wrote:
On 28 Dec 2005 15:04:11 -0800, An***********@b cbsmn.com wrote:

However, even with both of those improvements the query time would
still be well over the 10-second mark. What I'm thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.

I haven't the foggiest idea how I might go about setting that up.

Any tips or suggestions any of you may have would be much appreciated.


Take your srcWorkingDays2 function out of the query all together and call it from a text box in the continuous form.

Assuming you have textboxes in the continuous form called StartDate and EndDate, add a textbox and as it's control
source put -
=srcWorkingDay s2([StartDate],[EndDate])

The function will only be called for records currently displayed in the continuous form. You may however get a slight
delay as each record is evaluated but this usually insignificant.
Wayne Gillespie
Gosford NSW Australia


And if I may tack on...

While Wayne's suggestion will speed up things considerably, you might
squeeze a bit more performance if you do your count of holidays
another way. Because, assuming there are less holidays than the total
days in each case, you could open a recordset based on all holidays
between the open and end of each case, and loop through these,
counting the number of found instances.

P
Dec 31 '05 #7
CD********@Fort uneJames.com wrote:
I'm going to
have to change some other code I have soon for calculating workdays so
that large date ranges don't cause a performance issue since I
implemented it by stepping through dates. I personally don't use a
holiday table so I'm going to have to rethink the best way to handle
that situation as well. I thought Wayne's idea was excellent.


In order to implement this I need to change my holiday functions so
that the input is any date and the output is the date of that holiday
for that year. Let h(dtX) represent the holiday date returned for a
typical holiday during Year(dtX). To count the number of times a
holiday occurs within a date range, only the behavior near the start
and end dates of the range matter since any whole year will contain
exactly one of that holiday. If the year of the start date differs
from the year of the end date then the count of holidays between dt1
and dt2 is simply Abs(h(dt1) >= dt1) + Abs(h(dt2) <= dt2) + Year(dt2) -
Year(dt1) - 1, subject to final verification and testing of the logic.
When Year(dt1) = Year(dt2), h(dt1) = h(dt2) and the first two terms
cannot both be False when dt1 < dt2 so the same expression seems to
work for dt1 and dt2 being in the same year. I will verify all the
logic of this before presenting a final solution. The conversion of
the holiday functions to return a date doesn't look difficult and
should also be more useful to those who need to create holiday tables.

James A. Fortune
CD********@Fort uneJames.com

I was ftp'ing aroung for free software, preferably with source code.
Back then most universities and companies had one or two mainframes
connected to the internet. Suddenly, at harvard.edu there was what
looked like a roomful of computers available for anonymous ftp. Almost
all of them had the word "mac" as part of the name. PC's hooked up to
the internet? They're opening a whole new world.

Jan 17 '06 #8
CD********@Fort uneJames.com wrote:
In order to implement this I need to change my holiday functions so
that the input is any date and the output is the date of that holiday
for that year.


Here are the preliminary changes to the holiday functions subject to
verification and testing:

'Begin modDirectDateFu nctions
Option Compare Database
Option Explicit

Public Function GetChristmas(dt InYear As Date) As Date
GetChristmas = DateSerial(Year (dtInYear), 12, 25)
End Function

Public Function GetColumbus(dtI nYear As Date) As Date
'Second Monday in October
GetColumbus = DateSerial(Year (dtInYear), 10, NthXDay(2, vbMonday,
DateSerial(Year (dtInYear), 10, 1)))
End Function

Public Function GetEaster(dtInY ear As Date) As Date
Dim M As Integer
Dim d As Integer
Dim y As Integer
Dim DT As Date

M = 24
y = Year(dtInYear)
d = (19 * (y Mod 19) + M) Mod 30
DT = DateAdd("d", d, DateSerial(y, 3, 22))
DT = DT + (8 - WeekDay(DT)) Mod 7
GetEaster = DateSerial(Year (dtInYear), Month(DT), Day(DT))
End Function

Public Function GetIndependence (dtInYear As Date) As Date
GetIndependence = DateSerial(Year (dtInYear), 7, 4)
End Function

Public Function GetLabor(dtInYe ar As Date) As Date
'First Monday in September
GetLabor = DateSerial(Year (dtInYear), 9, NthXDay(1, vbMonday,
DateSerial(Year (dtInYear), 9, 1)))
End Function

Public Function GetMartinLuther King(dtInYear As Date) As Date
'Third Monday in January
GetMartinLuther King = DateSerial(Year (dtInYear), 1, NthXDay(3,
vbMonday, DateSerial(Year (dtInYear), 1, 1)))
End Function

Public Function GetMemorial(dtI nYear As Date) As Date
'Last Monday in May
GetMemorial = DateSerial(Year (dtInYear), 5,
Day(LastXDay(Da teSerial(Year(d tInYear), 5, 1), vbMonday)))
End Function

Public Function GetNewYears(dtI nYear As Date) As Date
GetNewYears = DateSerial(Year (dtInYear), 1, 1)
End Function

Public Function GetPresidents(d tInYear As Date) As Date
'Third Monday in February
GetPresidents = DateSerial(Year (dtInYear), 2, NthXDay(3, vbMonday,
DateSerial(Year (dtInYear), 2, 1)))
End Function

Public Function GetThanksgiving (dtInYear As Date) As Date
'Fourth Thursday in November
GetThanksgiving = DateSerial(Year (dtInYear), 11, NthXDay(4, vbThursday,
DateSerial(Year (dtInYear), 11, 1)))
End Function

Public Function GetVeterans(dtI nYear As Date) As Date
GetVeterans = DateSerial(Year (dtInYear), 11, 11)
End Function

'These functions are required for many of the functions above
Public Function LastXDay(dtD As Date, DayConst As Integer) As Date
LastXDay = DateSerial(Year (dtD), Month(dtD) + 1,
(-WeekDay(DateSer ial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod
7)
End Function

Public Function NthXDay(N As Integer, d As Integer, dtD As Date) As
Integer
NthXDay = (7 - WeekDay(DateSer ial(Year(dtD), Month(dtD), 1)) + d) Mod 7
+ 1 + (N - 1) * 7
End Function
'End modDirectDateFu nctions

Sample call:
GetEaster(#1/1/06#) => 4/16/06

The holiday functions are listed in alphabetical order. The NthXDay
function requires a date within the target month so I had to
manufacture one for several of the holiday functions.

James A. Fortune
CD********@Fort uneJames.com

Jan 22 '06 #9
CD********@Fort uneJames.com wrote:
CD********@Fort uneJames.com wrote:
In order to implement this I need to change my holiday functions so
that the input is any date and the output is the date of that holiday
for that year.


Here are the preliminary changes to the holiday functions subject to
verification and testing:

'Begin modDirectDateFu nctions
Option Compare Database
Option Explicit

Public Function GetChristmas(dt InYear As Date) As Date
GetChristmas = DateSerial(Year (dtInYear), 12, 25)
End Function

Public Function GetColumbus(dtI nYear As Date) As Date
'Second Monday in October
GetColumbus = DateSerial(Year (dtInYear), 10, NthXDay(2, vbMonday,
DateSerial(Year (dtInYear), 10, 1)))
End Function

Public Function GetEaster(dtInY ear As Date) As Date
Dim M As Integer
Dim d As Integer
Dim y As Integer
Dim DT As Date

M = 24
y = Year(dtInYear)
d = (19 * (y Mod 19) + M) Mod 30
DT = DateAdd("d", d, DateSerial(y, 3, 22))
DT = DT + (8 - WeekDay(DT)) Mod 7
GetEaster = DateSerial(Year (dtInYear), Month(DT), Day(DT))
End Function

Public Function GetIndependence (dtInYear As Date) As Date
GetIndependence = DateSerial(Year (dtInYear), 7, 4)
End Function

Public Function GetLabor(dtInYe ar As Date) As Date
'First Monday in September
GetLabor = DateSerial(Year (dtInYear), 9, NthXDay(1, vbMonday,
DateSerial(Year (dtInYear), 9, 1)))
End Function

Public Function GetMartinLuther King(dtInYear As Date) As Date
'Third Monday in January
GetMartinLuther King = DateSerial(Year (dtInYear), 1, NthXDay(3,
vbMonday, DateSerial(Year (dtInYear), 1, 1)))
End Function

Public Function GetMemorial(dtI nYear As Date) As Date
'Last Monday in May
GetMemorial = DateSerial(Year (dtInYear), 5,
Day(LastXDay(Da teSerial(Year(d tInYear), 5, 1), vbMonday)))
End Function

Public Function GetNewYears(dtI nYear As Date) As Date
GetNewYears = DateSerial(Year (dtInYear), 1, 1)
End Function

Public Function GetPresidents(d tInYear As Date) As Date
'Third Monday in February
GetPresidents = DateSerial(Year (dtInYear), 2, NthXDay(3, vbMonday,
DateSerial(Year (dtInYear), 2, 1)))
End Function

Public Function GetThanksgiving (dtInYear As Date) As Date
'Fourth Thursday in November
GetThanksgiving = DateSerial(Year (dtInYear), 11, NthXDay(4, vbThursday,
DateSerial(Year (dtInYear), 11, 1)))
End Function

Public Function GetVeterans(dtI nYear As Date) As Date
GetVeterans = DateSerial(Year (dtInYear), 11, 11)
End Function

'These functions are required for many of the functions above
Public Function LastXDay(dtD As Date, DayConst As Integer) As Date
LastXDay = DateSerial(Year (dtD), Month(dtD) + 1,
(-WeekDay(DateSer ial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod
7)
End Function

Public Function NthXDay(N As Integer, d As Integer, dtD As Date) As
Integer
NthXDay = (7 - WeekDay(DateSer ial(Year(dtD), Month(dtD), 1)) + d) Mod 7
+ 1 + (N - 1) * 7
End Function
'End modDirectDateFu nctions

Sample call:
GetEaster(#1/1/06#) => 4/16/06

The holiday functions are listed in alphabetical order. The NthXDay
function requires a date within the target month so I had to
manufacture one for several of the holiday functions.

James A. Fortune
CD********@Fort uneJames.com


This is still not the final solution since I haven't verified the
logic. I have done some testing without finding any mistakes so far.

Version 1:

Public Function CountHolidays(d tStart As Date, dtEnd As Date)
Dim lngTemp As Long

lngTemp = lngTemp + Abs(GetNewYears (dtStart) >= dtStart) +
Abs(GetNewYears (dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetMartinLu therKing(dtStar t) >= dtStart) +
Abs(GetMartinLu therKing(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart)
- 1
lngTemp = lngTemp + Abs(GetPresiden ts(dtStart) >= dtStart) +
Abs(GetPresiden ts(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetEaster(d tStart) >= dtStart) +
Abs(GetEaster(d tEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetMemorial (dtStart) >= dtStart) +
Abs(GetMemorial (dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetIndepend ence(dtStart) >= dtStart) +
Abs(GetIndepend ence(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetLabor(dt Start) >= dtStart) +
Abs(GetLabor(dt End) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetColumbus (dtStart) >= dtStart) +
Abs(GetColumbus (dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetVeterans (dtStart) >= dtStart) +
Abs(GetVeterans (dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetThanksgi ving(dtStart) >= dtStart) +
Abs(GetThanksgi ving(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetChristma s(dtStart) >= dtStart) +
Abs(GetChristma s(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
CountHolidays = lngTemp
End Function

Version 2:

Public Function CountHolidays(d tStart As Date, dtEnd As Date)
Dim intNumberOfHoli days As Integer
Dim lngTemp As Long

intNumberOfHoli days = 11
lngTemp = (Year(dtEnd) - Year(dtStart) - 1) * intNumberOfHoli days
lngTemp = lngTemp + Abs(GetNewYears (dtStart) >= dtStart) +
Abs(GetNewYears (dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetMartinLu therKing(dtStar t) >= dtStart) +
Abs(GetMartinLu therKing(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetPresiden ts(dtStart) >= dtStart) +
Abs(GetPresiden ts(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetEaster(d tStart) >= dtStart) +
Abs(GetEaster(d tEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetMemorial (dtStart) >= dtStart) +
Abs(GetMemorial (dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetIndepend ence(dtStart) >= dtStart) +
Abs(GetIndepend ence(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetLabor(dt Start) >= dtStart) +
Abs(GetLabor(dt End) <= dtEnd)
lngTemp = lngTemp + Abs(GetColumbus (dtStart) >= dtStart) +
Abs(GetColumbus (dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetVeterans (dtStart) >= dtStart) +
Abs(GetVeterans (dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetThanksgi ving(dtStart) >= dtStart) +
Abs(GetThanksgi ving(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetChristma s(dtStart) >= dtStart) +
Abs(GetChristma s(dtEnd) <= dtEnd)
CountHolidays = lngTemp
End Function

Version 2 is slightly faster but the user needs to remember to change
intNumberOfHoli days when commenting out unwanted holidays. Version 1
can be used by commenting out unwanted holidays directly.

This function assumes the existence of the functions contained in the
quoted post. It gets more efficient compared to other methods as the
number of years in the interval increases. I.e., it gets sweeter as
the years go by :-). It still runs quickly for short ranges.
Actually, it should take about the same time to run regardless of the
number of years in the interval. Mind the line wrap. The code still
does not account for holidays that are observed on Friday or Monday
when they fall on a weekend. When I'm finished, if I use a holiday
table it will have everything except holidays in it :-).

James A. Fortune
CD********@Fort uneJames.com

Feb 12 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3143
by: Shay | last post by:
essentially I am trying to do some counts based on some assumptions in the recordset. So I get the RS back, put the values into a variable, move to the next record in the RS and compare what is in the variable to the value in the next record in the recordset and do a count. Then overwrite the value in the variables and do the same for the next record and so. But this runs extremly slow. 5000 records takes about 10 minutes in IE6 and I...
2
5349
by: Tim Fountain | last post by:
We've recently enabled slow query logging on a server and it's proving interesting seeing which queries are bogging things down. This one is puzzling me a little: SELECT articleid, type, authorid, authorname, text, posted FROM comments WHERE status = 'normal' ORDER BY posted DESC LIMIT 5; The purpose of this query is to list the five most recent (non-deleted) comments. Here is the table structure:
1
1651
by: Thomas Bartkus | last post by:
Is it possible that the elapsed time being measured includes waiting for the client to acknowledge that it has received all the data? In *addition* to the server execution time? Documentation seems to *imply* that the slow query log only looks at server execution time. But, it doesn't acknowledge this directly and there seems to be a distinct connection between slow network pipes and slow queries. For example - even the simplest...
2
6234
by: AG | last post by:
I am able to run a query which runs FAst in QA but slow in the application.It takes about 16 m in QA but 1000 ms on the Application.What I wanted to know is why would the query take a long time in the application when it runs fast on SQL server? How should we try debugging it? Ajay
2
2016
by: Yonatan Goraly | last post by:
I am in the process of adding PostgreSQL support for an application, in addition to Oracle and MS SQL. I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III board. I have a query that generally looks like this: SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND t2.p='string' AND t2.q=1 This query is strikingly slow (about 100 sec when both t1 and t2 has
3
4800
by: Janross | last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's network, it takes at least 5 minutes to run. Obviously not workable! I know where the problem is, I just don't know how to fix it. The query calls a function, and I assume it gets slow because the function runs on every record. So--is there a way to rewrite the function so it's quicker?...
3
2888
by: Jennyfer J Barco | last post by:
In my application I have a datagrid. The code calls a Stored procedure and brings like 200 records. I created a dataset and then a dataview to bind the results of the query to my grid using MyGrid.DataBind() Once the records are loaded, to handle the next, previous button is too slow. I have in the same screen OptionsBox and everytime I click in any option I show some text fields in the screen. Anything the user does is very slow. When...
0
2668
by: Dave Hammond | last post by:
Hi All, I'm trying to use the slow-query-log (with --log-queries-not-using-indexes enabled) to determine if any queries need optimization, and have a few questions about some entries I'm seeing. First question: given a table defined as: CREATE TABLE `oa_location` (
9
19155
by: Emin | last post by:
Dear Experts, I have a fairly simple query in which adding a where clause slows things down by at least a factor of 100. The following is the slow version of the query ------------------------- SELECT * FROM ( Select x.event_date From x FULL OUTER JOIN y ON x.event_date = y.event_date
2
9840
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes to open in datasheet view. As an experiment, I deleted all rows in all tables; after that, the query took only seconds to open in both design view and datasheet view. From these facts, I conclude that Access is evaluating the query when I go to...
0
9383
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9154
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8051
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6681
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5995
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4502
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2601
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2147
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.