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

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{*filter*}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

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{*filter*}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

...

General comments first--

1.  The approach of not trying to optimize until its been shown there's
a need is admirable trait--until there is a problem try to write code
that is first legible and therefore more likely to be maintainable and
second, the most straightforward approach you can think of.  When (and
more importantly _IF_) it's demonstrated there's a bottleneck,

2.  Find out _specifically_ by empirical timing precisely where the
bottleneck(s) are.  More time by far has been wasted on shaving milli-
or microseconds off of portions of code that are thought to be the
problem areas but which actual profiling demonstrates aren't.

So, those are both good starts.

3.  Do _NOT_ confuse and equate efficiency in runtime w/ terseness of
source code;  there is no necessary correlation at all to the length of
the source and the associated run time.  (One of my favorite old timey
training aids in getting this across to new hires was the challenge to
write a long-running code in a very short amount of code.  My answer to
the challenge was always

10 GOTO 10

The point was rarely overlooked!! :)

4.  When beginning to look for optimizations _ALWAYS_ start w/ the
algorithm/implementation of the solution, _NOT_ with simply tinkering w/
alternate instructions to do the same thing as already doing.  Virtually
always, that recasting of the problem will be where the really
significant gains are.

Finally, more directed to your immediate problem...

I didn't try to actually write a function, but my thought is that it
should be pretty simple to simply calculate the weeks elapsed and look
at the start day of the week of the initial date.  Given there are a
fixed number of days/week, the number of weekend days is also fixed
depending on the relationship of the start day to an arbitrary
week-start day (Sunday/Monday/...).  So, instead of the loop, simply
subtract the number of weeks*2 and make the adjustment for any partials
based on a lookup table and that start DOW.

Lastly, one more general comment--the VB "Books Online" document has a
good section on programming style/techniques specifically for VB
addressing coding for performance from both memory and processing time.
  I strongly recommend reading it from beginning to end and applying its
precepts; it's not particularly long but does have solid advice.  And,
of course, there's the use of the compiler optimization flags to help
the compiler do the best it can given the limitations on the age of VB
and the design of the language itself.  It actually doesn't do too
badly, however, if you give it the help outlined.

Just one example of a minor detail--avoid Variants unless absolutely
there is a reason for using them.  The add some extra overhead baggage
that there's no sense in paying for if you don't need the extra
facility, namely overloading data types.  This function doesn't need
that.  In general, that one falls in the realm of the second category of
optimization noted above, but there's no sense in ignoring
easy-to-include habits, either.  The use of Long as indices in arrays
and For loops I noted in another response is another.  It's just good
practice.

--

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

<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

OK, so have you also verified that this function is a significant
contributor to that loop?

Not that I don't think it could be improved significantly to eliminate
the loop inside it entirely relatively simply, but say the function were
much more involved and might take a considerable effort to
rewrite/recast?  Would you find you still hadn't gained much overall
even if the function runtime itself were halved, say?

The point being, obviously, the more granularity in the profiling, the
better you can concentrate efforts to the most beneficial areas.  Don't
guess, measure.

...

...

Just work out on paper from a calendar the number to adjust based on the
starting day in the week and then how many days at the end wrap from
that full week.

It _might_ make sense to always do the computation by starting at a
fixed DOW and find the number of full weeks in the span and then count
the two end conditions.  I can envision many perturbations of the
method, none require a loop.  Think on that line could undoubtedly lead
to some other ways to manipulate the time span as well...

It's the link to it in the VB help files--at least for VB5; I presume
it's in the VB6 help as well.  (I had no real interest/need to upgrade
VB5 and wasn't paying enough attention that by the time I tried it was
to the .net release so never did anything...)

--

...
...

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.

Specifically, in the Programmer's Guide, "Part 2: What You Can Do W/ VB
(sic)" there's a chapter entitled "Designing for Performance and
Compatibility"

I presume the VB6 documentation has enough similarity you can find the
chapter somewhere in the distribution help/documentation files.

--

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