Developer Forum

I'm a professional novice. What is that? Someone who has been
programming for many years but has never really moved beyond novice
status because programming is not my core occupation. It's a very
handy side-tool.

Well, one of the problems with this is that I've never really given
much consideration to 'how long' it takes for code sections to do
their job.

...until now.

A project I have that was originally written years ago but is
periodically modified has sections that are just too slow. This is the
result of my ignorance on how to best utilize VB6 coding techniques.

Yesterday, I posted a request on how to time sections of code and was
kindly provided with a suggestion to use GetTickCount. It was quite
revealing to me how changing some code around can make a big
difference.

One procedure I had was taking 15875 (millisecs?) to run. Yawn.

However, I tinkered with it and made several changes resulting in the
code providing the same results but doing so in only 2374 ms. Now
that's an improvement that shows up!

I'm still not happy with it though. I'd like to shave another 1000 ms
off that if possible, so I'm looking at doing the small stuff.

One part of my code calls a function that returns the number of
weekdays between two dates. It does not count weekends.

This is the function I currently am using:

--------- START CODE -------------

Public Function GetWeekdays(BegDate As Variant, EndDate As Variant) As
Long

' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Long

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)

WholeWeeks = DateDiff("w", BegDate, EndDate)

DateCnt = DateAdd("ww", WholeWeeks, BegDate)

EndDays = 0

Do While DateCnt < EndDate

If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <>
"Sat" Then

EndDays = EndDays + 1

End If

DateCnt = DateAdd("d", 1, DateCnt)

Loop

GetWeekdays = WholeWeeks * 5 + EndDays

End Function

----------------- END CODE ---------------

I'm not convinced that this is the most efficient way to accomplish
this task. I've seen some pretty slick coding from the pros on this
newsgroup that use less lines than this to do a lot more.

The above was the best I've come up with so far. It would be great if
some here could point me towards some efficient modifications that may
result is shaving some time off the processing of this simple task.

Not only is my goal to shave off time, but to learn some valuable
lessons on doing so that I may be able to apply to other code as well.

Thank you all in advance!

Webbiz

In article < XXXX@XXXXX.COM >,
XXXX@XXXXX.COM says...

Two quick hints:

1. check the datepart() function to find the day of the week, and do the
comparisom numerically.

2. in date arithmetic, adding an integer to a date increments the date
by that number of days, eliminating the need for the dateadd() function.


--
/~\ The ASCII
\ / Ribbon Campaign
X Against HTML
/ \ Email!

Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Take the first and last wee, and multyply with the other weeks (5
days?) or use dimediff something might work... if every week still has
7 days and you are using this to calculate over more than 2 weeks it
would improve efficansy, effectivnes, efficcasy, ethics...
whaatever... I dont remember... the 5E... sorry SSM (Soft systems
methology), starting to wonder why this is run so often? run it in the
morning or in 00:01 and store it...

Didnt look at the code, do you want milliseconds then s *** that
thougt.. but for hours... welll... that a thought, its like MTS
queuing and reusing queries (withch I nenver liked)

There are some breadcrumbs (pizza) crawling around in the eabord and I
am not that good at american english to start with so ... thin it
around the kk (ahh it wors just have to tap it hader)

//CY

On Tue, 23 Jun 2009 11:23:17 -0700 (PDT), CY < XXXX@XXXXX.COM >




Thanks for the reply. I'm totally confused of what you're talking
about above.

This line caught my attention though, "do you want milliseconds then
s *** that thought..."

Are you referring to my wanting to shave off milliseconds? If so, I'd
like to shave off seconds and minutes overall, but may have to do this
by shaving off milliseconds, especially if the function in question is
being called a 1000 times within a loop. If I shave off just a few
milliseconds, times 1000, well... :-)

I didn't understand the "run it in the morning or in 00:01...". Sorry,
but you lost me.

Thank you. :-)

Webbiz

On Tue, 23 Jun 2009 14:19:59 -0400, David Kerber




Thank you David. I'm going to give this a try. :-)
Webbiz

On Tue, 23 Jun 2009 14:19:59 -0400, David Kerber




Excellent progress David!

Here is my modified code so far:

------------ START ---------------
Public Function GetWeekdays(BegDate As Variant, EndDate As Variant) As
Long

' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim TempDate As Variant
Dim EndDays As Long



BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)

WholeWeeks = DateDiff("w", BegDate, EndDate)

TempDate = DateAdd("ww", WholeWeeks, BegDate)

EndDays = 0

Do While TempDate < EndDate

If DatePart("w", TempDate, vbMonday) < 6 Then
'If Format(TempDate, "ddd") <> "Sun" And Format(TempDate, "ddd")
<> "Sat" Then

EndDays = EndDays + 1

End If

TempDate = TempDate + 1
'TempDate = DateAdd("d", 1, TempDate)

Loop

GetWeekdays = WholeWeeks * 5 + EndDays



End Function

-------------- END ---------------


This modification based on your suggestions has shaved off about 200ms
from the routine. It went from an average of 2430ms down to 2210ms.

:-)
Webbiz

n Tue, 23 Jun 2009 14:08:38 -0500, dpb < XXXX@XXXXX.COM > wrote:

<snipperoni - a San Francisco treat>


Understood.


Agreed. I've narrowed down where my speed issues are to a routine that
contains both Recordset creation and extensive calculations within a
loop. So I had two sections within this one routine that were slowing
things down a lot.

I tacked first the Recordset issue first. Rather than create a
recordset that contained all the information the routine would need
for any situation, I change the SQL to deliver only the bare
essentials based on what the routine needed "at that time" only. This
saved significant time. Then I replaced some of the recordset
manipulation code to use an array instead (using .GetRows) that also
shaved off considerable waste.

Now I'm down to a loop that has to perform extensive date manipulation
mathematics, which brings me to the Weekday count component we're
currently dealing with.

So yes, your suggestion is indeed well taken.


This is a valuable lesson. While I do like as least amount of lines in
my code as possible for clarity, I certainly don't want a bunch of
cryptic lines for the same reason, especially if there are no speed
gains.



Certainly makes sense. Unfortunately I cannot think up a different way
to perform the algorithm in question. This has left me to deal with
the alternate instruction approach, which has shown some improvement
over my bloated approach.

My inability to find an alternate approach to solving my particular
task aside, I understand what you are saying as being most important.



This sounds interesting (and simple, though at this moment of replying
don't have it worked out).

I take my WholeWeeks and mulitply this by 2 to get weekend days.
Subtract this from my TotalDays.

Then I must make an adjustment based on what day of week the count
started and ended.

I've got to give this some thought, but it would seem to remove the
need for a loop. Thx. :-)


Is there a specific website that you refer to as "VB Books Online"?


I was thinking about this as well, actually. When I noted the
"Variant" type, I was trying to remember why it was there. But then,
this is my old code and has been produced over the years as I was
learning one small thing after another.

Thank you VERY MUCH for taking the time to write your lesson.

Regards,

Webbiz

Well, I can't take credit for the following code because I did find it
online. I'm not sure if it is the most efficient just because there
are less lines, especially after your lesson on the fact that having
less code is not necessarily a reduction in time.

However, it did further speed up my routine so that I've got it now
down to running in 1656ms. Yabba! This is now 1/10th the time it
originally took for the whole routine to run and is very noticible.


Public Function GetWeekdays(ByVal StartDate As Date, ByVal EndDate As
Date) As Long


If StartDate > EndDate Then
GetWeekdays = -1
Else

GetWeekdays = Fix(EndDate - StartDate) - Fix(Fix(EndDate -
DateAdd("d", -Weekday(StartDate) + 2, StartDate)) / 7) * 2

End If


End Function

:-)
Webbiz

...
...
...

I'd say this would be one implementation of the earlier suggestion;
clearly losing the loop and two character-matching logical tests is a
step forward.

As to whether it would be the "most efficient", maybe/(probably???) not;
there are possibly/(probably???) some ways to trade memory for the
DateAdd() and Weekday() functions as I suggested earlier by lookup
tables for the end conditions.

But, at that point you're undoubtedly to the second-order effects that
until you can show the revised function is again very high on the list
of execution time expended there will be far more fruitful areas to explore.

But, it's a useful exercise in how to rethink algorithms...

Yes. It's a major player. Consider a routine that needs to know the
distance in workdays between thousands of dates to perform calcs on
those relationships.

Yes. Found it in the MSDN Library under "What Can You Do With Visual
Basic?"

Thanks.

Webbiz

Sorry if I wasnt specific enough...

but something like this... but it will not loop, or cost lots of
ms...



Sub asd()
Dim slask As Integer

' datasettng
begdate = DateValue("2002-01-01")
enddate = DateValue("2008-01-31")


slask = DateDiff("w", begdate, enddate) * 5 ' Ok got it * 5
x = -(Weekday(begdate) - 5) ' starting at a weekday
If x > 0 Then slask = slask + x ' then add it
x = -(Weekday(endate) - 5) ' aha..
If x > 0 Then slask = slask + x

Debug.Print begdate & " --> " & enddate & " Guess u want:" & slask

End Sub

But it might not wor, u never stated wat you wanted, just what u
did...

//CY

> im slask As Integer >> >> ' datasettng >> egdate = DateValue("2002-01-01")> > nddate = DateValue("2008-01-31">
> lask = DateDiff("w", begdate, enddate) * 5 ' Ok got it *>5
> = -(Weekday(begdate) - 5) ' starting at a week>ay
> >If x > 0 Then slask = slask + x ' then ad> it
> = -(Weekday(endate) - 5) ' >ha..
> > f x > 0 Then slask = sla>k > x
>
> ebug.Print begda>e & " --> " & enddate & " Guess u want:" > s>ask
>
> >nd>Sub
>
> But it might not wor, u never stated wat you wanted, just>what u
>>di>...
>
> //CY

And dim those var used as integer, or whatever, now half of them are
variants... costing mem if nothing else..

//CY

Found those 5E...

Five Es for Decision Criteria
efficacy (will it work at all?)
efficiency (will it work with minimum resources?)
effectiveness (does it contribute to the enterprise?)
ethics (is it sound morally?)
elegance (is it beautiful?)

think about that... then its easy to program...

//CY

Or dont it would look like

' Note that this function does not account for idiots.
Dim slask As Integer

' datasettng
begdate = DateValue("2009-01-01")
enddate = DateValue("2009-01-31")


slask = DateDiff("w", begdate, enddate) * 5 _
+ (Abs(-(Weekday(begdate) - 5)) + Abs(-(Weekday(enddate) - 5))) '
aha..

Debug.Print begdate & " --> " & enddate & " Slutresultat:" & slask

;)

//CY