Hi, Jamie.
Do you realize you are converting two DATETIME values to String,
comparing them and returning a Boolean, converting to an Integer then
adding to a Long? Here is a suggested alternative:
I think you want to make a mountain out of a molehill. These are two
different ways to accomplish the same task. Each is about as efficient as
the other. Yours takes slightly longer to type, because it has more
characters. Apparently, you'd like to break each method down into pieces and
compare the merits. For what that's worth:
Do you realize you are converting two DATETIME values to String
It's not the whole nine yards. It's just an 8-byte string -- the
representation of four characters, "mmdd" " And yes, there are two values of
eight bytes each.
comparing them and returning a Boolean
Two operands of the same data type (the 8-byte strings) and a relational
operator yields a boolean value. I agree.
A VB boolean data type is represented by 0 and -1 in memory. The Int( )
function returns the integer portion of a number, so these 0 and -1 values
are being converted to four bytes that equal 0 and -1, respectively. Not a
huge undertaking in terms of CPU resources.
A 4-byte integer added to a 4-byte long isn't as time-consuming an operation
as it was before 32-bit operating systems hit the market, when (short)
integers where generally two bytes. It took extra fetch and execute cycles
on the 8-bit and 16-bit operating systems, but adding a 4-byte integer to a
4-byte long these days takes the same number of cycles as adding two 4-byte
longs.
As for your method of calculating age, the DateSerial( ) function returns a
Variant of Date subtype. It also uses two operands (the 8-byte Dates) and a
relational operator to yield a boolean value. It also converts the VB
boolean data type to a 4-byte long. It also adds this to a Variant of long
subtype.
The major difference between the methods is that yours uses the DateSerial(
) function to build a date from three components and mine uses the format
function on two values. So how do they compare? To find out, I used the
method described on the following Web page:
http://support.microsoft.com/default.aspx?id=172338
Since the first time through the test loop generally takes longer than the
second, I ran through the loop twice for each method of determining age to
get an average (or to show a comparison, as you'll see further down). Since
the fraction of a second it took to execute the loop 100 times is so small, I
iterated through the loop 1,000,000 times. I used the following code for
each test:
String Test (to demonstrate how much time it takes to convert two Date/Time
data types to string, instead of just the month and day, even though this
would give an incorrect result):
age = DateDiff("yyyy", DOB, Date) + Int(CStr(Date) < CStr(DOB))
Mine:
age = DateDiff("yyyy", DOB, Date) + Int(Format(Date, "mmdd") < Format(DOB,
"mmdd"))
Yours:
age = DateDiff("yyyy", DOB, Date) + _
CLng(DateSerial(Year(Date), Month(DOB), Day(DOB)) > Date)
I ran the tests several times and found that your method was generally about
2% faster than the one I suggested:
String Test took 8.06158995067809 seconds
String Test took 7.75841929630721 seconds
Mine took 7.45051088895376 seconds
Mine took 7.48338992804951 seconds
Jamie's took 7.40612535950798 seconds
Jamie's took 7.39910686972786 seconds
So it's a tradeoff: faster to type (mine) or faster to execute (yours).
The break-even point for the average typist on a 2.5 GHz PC is about
25,000,000 executions. Higher than that and your method saves time, at a
rate of about 2%. Perhaps you come across tasks where 25,000,000 people (or
any group of items, for that matter) need to have their ages calculated, but
most of us don't fall into this category.
The bottom line is that it's hard to argue that such a tiny fraction of a
second in efficiency is really essential for the vast majority of
programmers. For most of us, it just doesn't matter one way or the other.
Pick one and move on to solve the next problem that really _can_ make a
significant difference in efficiency or cost.
Simply:
SELECT *
FROM Contacts
ORDER BY DOB;
Thanks for that. I need to do a better job editing out the irrelevant parts
when I copy/paste from one of my previous posts on a similar subject. Sorry
for any confusion to anyone reading that post of mine.
Since one good turn deserves another, may I suggest that you return to
another thread where you admonished someone's analogy of a bucket o' fish for
a table that holds records in an unsorted order, instead of calling it a
heap, which indicates a table without a clustered index. While you were
espousing the merits of Jet's use of clustered indexes and how Jet handles
them, you neglected to mention that Jet doesn't even support clustered
indexes.
P.S. I think far more people who ask questions in this newsgroup can relate
to a "bucket o' fish" than to a "heap" for an unordered set.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.
