Asp Forum - Fastest (best) way to Extract Daily Figures - microsoft.public.vb.general.discussion

David

8/25/2010 7:27:00 PM

220 0 <#tBDFuIRLHA.796@TK2MSFTNGP02.phx.gbl>
From: "David" <NoWhere@earthlink.net>
Subject: Fastest (best) way to Extract Daily Figures
Date: Wed, 25 Aug 2010 15:27:13 -0400
Lines: 26
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
Message-ID: <#tBDFuIRLHA.796@TK2MSFTNGP02.phx.gbl>
Newsgroups: microsoft.public.vb.general.discussion
NNTP-Posting-Host: ip70-176-129-219.ph.ph.cox.net 70.176.129.219
Path: s05-b06.iad!s04-b166.am1!squishee.iad!txtbe01.phx!txtbe01.iad!npeersf02.iad.highwinds-media.com!npeer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!nntp.club.cc.cmu.edu!feeder.erje.net!newsfeed01.sul.t-online.de!newsfeed00.sul.t-online.de!t-online.de!TK2MSFTFEEDS02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
X-Received-Date: Thu, 12 May 2011 15:22:28 UTC (s05-b06.iad)

I have a "real-time" data structure in the following format:

Type ThisDataInfo
DT As Date
Amount As Single
End Type

The above structure data is minute by minute.
I would now like to get daily information.

Would it be better to loop through the data once at startup and build
another structure of just daily data and then track the current day

- or -

would it better to incorporate a flag (e.g. FlagDateEnds as Boolean) in the
original structure so that the crossover (old date to new date) can be
easily found without
having to compare dates?

10 Answers

(nobody)

8/25/2010 8:12:00 PM

"David" <NoWhere@earthlink.net> wrote in message
news:%23tBDFuIRLHA.796@TK2MSFTNGP02.phx.gbl...
>I have a "real-time" data structure in the following format:
>
> Type ThisDataInfo
> DT As Date
> Amount As Single
> End Type
>
> The above structure data is minute by minute.
> I would now like to get daily information.
>
> Would it be better to loop through the data once at startup and build
> another structure of just daily data and then track the current day
>
> - or -
>
> would it better to incorporate a flag (e.g. FlagDateEnds as Boolean) in
> the original structure so that the crossover (old date to new date) can be
> easily found without
> having to compare dates?

I would make a larger UDT that encapsulates the PerMinute UDT. Example:

Type TPerDay
MinuteData(1 To 1440) As ThisDataInfo ' 1440 = 60 * 24

Dirty As Boolean
ThisDay As Date
AvgAmount As Single
End Type

The "Dirty" flag should be used if recalculation need to be redone, for
example, if the per-minute data changed, or the day is not complete yet.

Or since you probably have an array of ThisDataInfo, just make DayStartIndex
and DayEndIndex point to the index in the per-minute array. Example:

Type TPerDay
DayStartIndex As Long
DayEndIndex As Long
Dirty As Boolean
ThisDay As Date
AvgAmount As Single
End Type

Ultimately, I prefer to use a class, to make it easier to maintain data
integrity. In this case, everything that sets the data, and gets the info
back has to call a function, and this function saves the data and sets
various flags to make sure that these flags are consistent with what's in
the data. Remember, if you get a compiler warning about using UDT in
functions, declare the function using "Friend" instead of "Public".

David

8/25/2010 11:41:00 PM

Thanks for your response Nobody.

1)
Interesting idea making Minute data as an array under TPerDay.
Only issue I see with this is if the user also wants to display data for
some
period between one minute and one-day, the need to accommodate
a number of arrays would cause the TPerDay structure to grow quite large.

2)
Using an Index (pointer) to reference the minute element makes sense
-- but -- as it see it both structures (Minute and TPerDay) would need to be
arrays in order to handle multiple days.

-------------------------
Right now I'm leaning toward #2, as I "believe" it may be more flexible for
subsequent revisions.

"Nobody" <nobody@nobody.com> wrote in message
news:i53ten$3fb$1@speranza.aioe.org...
> "David" <NoWhere@earthlink.net> wrote in message
> news:%23tBDFuIRLHA.796@TK2MSFTNGP02.phx.gbl...
>>I have a "real-time" data structure in the following format:
>>
>> Type ThisDataInfo
>> DT As Date
>> Amount As Single
>> End Type
>>
>> The above structure data is minute by minute.
>> I would now like to get daily information.
>>
>> Would it be better to loop through the data once at startup and build
>> another structure of just daily data and then track the current day
>>
>> - or -
>>
>> would it better to incorporate a flag (e.g. FlagDateEnds as Boolean) in
>> the original structure so that the crossover (old date to new date) can
>> be easily found without
>> having to compare dates?
>
> I would make a larger UDT that encapsulates the PerMinute UDT. Example:
>
> Type TPerDay
> MinuteData(1 To 1440) As ThisDataInfo ' 1440 = 60 * 24
>
> Dirty As Boolean
> ThisDay As Date
> AvgAmount As Single
> End Type
>
> The "Dirty" flag should be used if recalculation need to be redone, for
> example, if the per-minute data changed, or the day is not complete yet.
>
> Or since you probably have an array of ThisDataInfo, just make
> DayStartIndex and DayEndIndex point to the index in the per-minute array.
> Example:
>
> Type TPerDay
> DayStartIndex As Long
> DayEndIndex As Long
> Dirty As Boolean
> ThisDay As Date
> AvgAmount As Single
> End Type
>
> Ultimately, I prefer to use a class, to make it easier to maintain data
> integrity. In this case, everything that sets the data, and gets the info
> back has to call a function, and this function saves the data and sets
> various flags to make sure that these flags are consistent with what's in
> the data. Remember, if you get a compiler warning about using UDT in
> functions, declare the function using "Friend" instead of "Public".
>
>
>
>

Jeff Johnson [MVP: VB]

8/26/2010 2:06:00 PM

Larry Serflaten

8/26/2010 3:28:00 PM

"David" <NoWhere@earthlink.net> wrote
> I have a "real-time" data structure in the following format:
>
> Type ThisDataInfo
> DT As Date
> Amount As Single
> End Type
>
> The above structure data is minute by minute.
> I would now like to get daily information.
>
> Would it be better to loop through the data once at startup and build
> another structure of just daily data and then track the current day
>
> - or -
>
> would it better to incorporate a flag (e.g. FlagDateEnds as Boolean) in the
> original structure so that the crossover (old date to new date) can be
> easily found without
> having to compare dates?

"Without having to compare dates'", what gain would that really be, when
the proposed solution compares a flag, instead of the date?

What does it mean 'to get daily information'?

If you loop through the data at start up, the data more than likely
comes from a file. If you are often separating that information out into
day by day reports, why not break up the data into multiple files, where
each file is one day's data?

Really, as others have already indicated, more information about what
you want to do is needed. Typically, I look for ways to hold data
such that its easy for the program to use. But without knowing what you
intend to do, it is difficult to suggest a better plan of action.

LFS

David

8/27/2010 1:23:00 PM

Mr. Johnson and Mr. Serflaten thanks for responding.

>>Mr. Johnson.

Already have a DB where I roll up information.
In fact the DB was setup to allow the user to rollup any time period from
one minute to yearly.

>>Mr. Serflaten:

Where I'm struggling is dealing with multiple days of data.
For example:

1) If I load in 3 days of one minute data into an array, I can then loop
through the array from 0 to N and provide output accordingly.
2)
If I go with the data "per-day" then I need a double loop, one for each day
and one for N days. Tracking which day is which also presents more of an
issue IMHO.

Each will work but "#1" seems a little cleaner.

Now, if the user wants daily info I can:

1) Calculate the daily from the one minute as I'm loading it
- or -
2) Have a separate table of daily data
- or -
3) Re-process the minute data extracting daily information when / if it is
needed.

Here "#2" seems the cleanist, but if the user wants graphic output of minute
data and also wants to flag the highest or lowest sale of the day then
having this information in the minute structure makes more sense since a
daily table would Not yield this information on a minute basis.

"Larry Serflaten" <serflaten@gmail.com> wrote in message
news:i56153$o26$1@news.eternal-september.org...
>
> "David" <NoWhere@earthlink.net> wrote
>> I have a "real-time" data structure in the following format:
>>
>> Type ThisDataInfo
>> DT As Date
>> Amount As Single
>> End Type
>>
>> The above structure data is minute by minute.
>> I would now like to get daily information.
>>
>> Would it be better to loop through the data once at startup and build
>> another structure of just daily data and then track the current day
>>
>> - or -
>>
>> would it better to incorporate a flag (e.g. FlagDateEnds as Boolean) in
>> the
>> original structure so that the crossover (old date to new date) can be
>> easily found without
>> having to compare dates?
>
> "Without having to compare dates'", what gain would that really be, when
> the proposed solution compares a flag, instead of the date?
>
> What does it mean 'to get daily information'?
>
> If you loop through the data at start up, the data more than likely
> comes from a file. If you are often separating that information out into
> day by day reports, why not break up the data into multiple files, where
> each file is one day's data?
>
> Really, as others have already indicated, more information about what
> you want to do is needed. Typically, I look for ways to hold data
> such that its easy for the program to use. But without knowing what you
> intend to do, it is difficult to suggest a better plan of action.
>
> LFS
>
>

Larry Serflaten

8/27/2010 5:12:00 PM

"David" <NoWhere@earthlink.net> wrote

> Now, if the user wants daily info I can:
>
> 1) Calculate the daily from the one minute as I'm loading it
> - or -
> 2) Have a separate table of daily data
> - or -
> 3) Re-process the minute data extracting daily information when / if it is
> needed.

It appears the real trouble here is in defining the problem. The user is
only allowed to do what you allow them to do. How are you going to
know what the user wants, when your application starts?

You indicated you have the information already stored in a DB. It
would seem a few well crafted 'stored procedures' would retrieve
the information you've mentioned. (highs or lows of a specifc or
several days, etc...)

I would think you could leave the data on the disk, until you actually
needed it (minute by minute graphs/reports, etc...) and then called up
in a form that can be easily used (like the large array idea you mentioned).

Just some food for thought....
LFS

David

8/28/2010 2:00:00 AM

Larry Serflaten thanks for responding

RE:
> It appears the real trouble here is in defining the problem. The user is
> only allowed to do what you allow them to do. How are you going to
> know what the user wants, when your application starts?

What I've done with the program to date is build in as much flexibility as
possible
that way the user can modify as they wish. Currently the user loads data
from the DB into individual array structures by defining a lookback period.
Where the problem arises -- re: Schema and my initial post -- is the best
(and fastest) way to handle and "intersect" multiple period information.

For example: if the user loads two separate arrays
(say minute and daily data per the previous example) --> what would be the
fastest
way to flag high and low sales for "EACH" day.

As previously stated I can loop through the minute, locate each day by
testing for a date change, and then track Hi and Low in the dataset until I
find the Highest or Lowest value. In this case NO Daily dataset array
structure would even need to be created.

However, if I did load both minute and daily, is there a way to interface
the two
so I can more readily find the minute Highest and Lowest value for each day
using the daily or am I wasting time and effort?

Nobody (as I recall) had a schema -- use daily structures with subset
arrays -- but unless I misunderstood didn't see where it was any
better/faster.

David

"Larry Serflaten" <serflaten@gmail.com> wrote in message
news:i58rj6$ia3$1@news.eternal-september.org...
>
> "David" <NoWhere@earthlink.net> wrote
>
>> Now, if the user wants daily info I can:
>>
>> 1) Calculate the daily from the one minute as I'm loading it
>> - or -
>> 2) Have a separate table of daily data
>> - or -
>> 3) Re-process the minute data extracting daily information when / if it
>> is
>> needed.
>
> It appears the real trouble here is in defining the problem. The user is
> only allowed to do what you allow them to do. How are you going to
> know what the user wants, when your application starts?
>
> You indicated you have the information already stored in a DB. It
> would seem a few well crafted 'stored procedures' would retrieve
> the information you've mentioned. (highs or lows of a specifc or
> several days, etc...)
>
> I would think you could leave the data on the disk, until you actually
> needed it (minute by minute graphs/reports, etc...) and then called up
> in a form that can be easily used (like the large array idea you
> mentioned).
>
> Just some food for thought....
> LFS
>
>
>
>
>
>

(nobody)

8/28/2010 9:29:00 AM

"David" <NoWhere@earthlink.net> wrote in message
news:eArkuSlRLHA.4996@TK2MSFTNGP04.phx.gbl...
> Larry Serflaten thanks for responding
>
> RE:
>> It appears the real trouble here is in defining the problem. The user
>> is
>> only allowed to do what you allow them to do. How are you going to
>> know what the user wants, when your application starts?
>
> What I've done with the program to date is build in as much flexibility as
> possible
> that way the user can modify as they wish. Currently the user loads data
> from the DB into individual array structures by defining a lookback
> period. Where the problem arises -- re: Schema and my initial post -- is
> the best (and fastest) way to handle and "intersect" multiple period
> information.
>
> For example: if the user loads two separate arrays
> (say minute and daily data per the previous example) --> what would be the
> fastest
> way to flag high and low sales for "EACH" day.
>
> As previously stated I can loop through the minute, locate each day by
> testing for a date change, and then track Hi and Low in the dataset until
> I find the Highest or Lowest value. In this case NO Daily dataset array
> structure would even need to be created.
>
> However, if I did load both minute and daily, is there a way to interface
> the two
> so I can more readily find the minute Highest and Lowest value for each
> day
> using the daily or am I wasting time and effort?
>
> Nobody (as I recall) had a schema -- use daily structures with subset
> arrays -- but unless I misunderstood didn't see where it was any
> better/faster.
>
> David

Some things are better left to a database engine, especially if you plan on
using a lot of queries, and reports, so you don't have to reinvent the
wheel. For example, you can use the following SQL statement to find the Min
and Max per day(air code):

SELECT Min(Amount), Max(Amount) FROM MyTable GROUP BY Date(DT) WHERE DT
BETWEEN #1/1/2010# AND #8/28/2010# ORDER BY DT

This would return a table with two columns, and as many rows as there are
days in the database. I used the Date() function in GROUP BY to strip the
time part. If you want it to run faster, use separate fields for date and
time. Also, DT field needs to by indexed, otherwise the DB Engine would
create a temporary index, and discard it afterward. Min() and Max() are
called aggregate functions, and there are more, like Count(), Sum(), and
Avg().

Some links:

Microsoft Access SQL Reference(Access 2007):
http://msdn.microsoft.com/en-us/library/bb259125%28v=office....

Transact-SQL Reference(MS SQL 2000):
http://msdn.microsoft.com/en-us/library/aa299742%28SQL....

You can always build your query using MS Access GUI(if you have it), then
view the SQL statement by going to View-->SQL View.

Larry Serflaten

8/28/2010 12:32:00 PM

"David" <NoWhere@earthlink.net> wrote

> For example: if the user loads two separate arrays
> (say minute and daily data per the previous example) --> what would be the
> fastest
> way to flag high and low sales for "EACH" day.
>
> As previously stated I can loop through the minute, locate each day by
> testing for a date change, and then track Hi and Low in the dataset until I
> find the Highest or Lowest value. In this case NO Daily dataset array
> structure would even need to be created.
>
> However, if I did load both minute and daily, is there a way to interface
> the two
> so I can more readily find the minute Highest and Lowest value for each day
> using the daily or am I wasting time and effort?

Look again at the structure you posted:

Type ThisDataInfo
DT As Date
Amount As Single
End Type

You really have no way to 'flag' any entry as there is no space in the structure
for it. Assuming you have that in an array (as stated above) you'll need a second
structure to hold either the Hi and Lo amount values, or their index positions
for however many days. That is how you would have to 'flag' them.

So the question is, can you loop through the data to build that second structure,
faster than the DB engine could return the information for you?

To be honest, I don't think the user could tell a difference. I would suspect it
would take longer for code to execute that shows the results on the screen,
than it would for either method of retieving the data.

As you mentioned before, you already have the data in a DB. I would think
you would do better to try to leverage a DB engine to your advantage,
than try to beat it with your own custom solutions. Not to say you'd be
wasting your time, but you'd be doing some significant work for very little
gain (if any).

As I said earlier, to flag your hi's and lo's you'd have to build a second
structure to indicate which minutes were 'flagged'. You could do that
yourself, but Nobody showed how the DB could give you the same
information.

It is still your decision as to which method is best for you, but I think
you've found that others suggest letting the DB mine the data, as you need it....

LFS

David

8/28/2010 7:19:00 PM

Mr. Serflaten:

Thanks again for the feedback.
I understand the proposed solutions which for the most part mimick what I'm
doing.
Your comment of :

>Not to say you'd be wasting your time, but you'd be doing some significant
>work >for very little gain (if any).

has merit, as maybe the approach I'm currently using "is" the best.

I do like Nobody's idea of using a "day structure" as a novel approach
but as previously stated don't believe is the best in this instance.

Please consider this thread closed and again thank you and everyone else
who contributed for there time and effort on my behalf.

David

"Larry Serflaten" <serflaten@gmail.com> wrote in message
news:i5avho$9eb$1@news.eternal-september.org...
>
> "David" <NoWhere@earthlink.net> wrote
>
>> For example: if the user loads two separate arrays
>> (say minute and daily data per the previous example) --> what would be
>> the
>> fastest
>> way to flag high and low sales for "EACH" day.
>>
>> As previously stated I can loop through the minute, locate each day by
>> testing for a date change, and then track Hi and Low in the dataset until
>> I
>> find the Highest or Lowest value. In this case NO Daily dataset array
>> structure would even need to be created.
>>
>> However, if I did load both minute and daily, is there a way to interface
>> the two
>> so I can more readily find the minute Highest and Lowest value for each
>> day
>> using the daily or am I wasting time and effort?
>
> Look again at the structure you posted:
>
> Type ThisDataInfo
> DT As Date
> Amount As Single
> End Type
>
> You really have no way to 'flag' any entry as there is no space in the
> structure
> for it. Assuming you have that in an array (as stated above) you'll need
> a second
> structure to hold either the Hi and Lo amount values, or their index
> positions
> for however many days. That is how you would have to 'flag' them.
>
> So the question is, can you loop through the data to build that second
> structure,
> faster than the DB engine could return the information for you?
>
> To be honest, I don't think the user could tell a difference. I would
> suspect it
> would take longer for code to execute that shows the results on the
> screen,
> than it would for either method of retieving the data.
>
> As you mentioned before, you already have the data in a DB. I would think
> you would do better to try to leverage a DB engine to your advantage,
> than try to beat it with your own custom solutions. Not to say you'd be
> wasting your time, but you'd be doing some significant work for very
> little
> gain (if any).
>
> As I said earlier, to flag your hi's and lo's you'd have to build a second
> structure to indicate which minutes were 'flagged'. You could do that
> yourself, but Nobody showed how the DB could give you the same
> information.
>
> It is still your decision as to which method is best for you, but I think
> you've found that others suggest letting the DB mine the data, as you need
> it....
>
> LFS
>
>
>
>

microsoft.public.vb.general.discussion

Fastest (best) way to Extract Daily Figures

David

(nobody)

David

Jeff Johnson [MVP: VB]

Larry Serflaten

David

Larry Serflaten

David

(nobody)

Larry Serflaten

David

x Login to ForumsZone