Wirt writes:
>But there is a second productivity attribute, too, and that is processing
>time. Let us presume that requirement is to find all of the invoices for May,
>regardless of the year, and the date is numerically encoded as a Julian date,
>offset from some arbitrary date, as it is MANMAN, then the query must be
>formulated something like this (using a generic query syntax):
>
>     find when substring(ascii(date(jd(DATE-DATAITEM,offset)))) = 05
 
Bzzzt. (I've always wanted to do that :-) ). That's the hard way. How about:
 
      find when (jd(DATE-DATAITEM) / 100) mod 100 = 5
 
Two integer divisions and a date conversion. The time spent in the date
conversion is paid back when the user wants to ask questions about
relationships between dates:
 
      find when DATE-PAID - DATE-OF-INVOICE > 30
 
These involve no conversions when dates are stored as binary day offsets,
but would involve an ASCII conversion _and_ a date conversion when the date
is stored as a character string.
 
If the date is stored as an integer YYYYMMDD rather than a day offset,
Wirt's original query becomes even cheaper, requiring just two integer
divisions by constants. And in Wirt's example, you've still saved storing
-- and reading -- 40MB.
 
However, I believe that other design decisions will have a greater impact
on overall system performance :-).
 
-- Bruce
 
 
---------------------------------------------------------------------------
Bruce Toback    Tel: (602) 996-8601| B is for Bug. The user's sore;
OPT, Inc.            (800) 858-4507| Our hardware's going out the door.
11801 N. Tatum Blvd. Ste. 142      | But brightly, brightly do I shine
Phoenix AZ 85028                   | Since I can prove it's yours, not mine.
[log in to unmask]                 |  -- D. Diamond, "A Programmer's ABCs"