|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Fastest (best) way to Extract Daily FiguresType 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?
Show quote
Hide quote
"David" <NoWh***@earthlink.net> wrote in message I would make a larger UDT that encapsulates the PerMinute UDT. Example: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? 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". 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. Show quoteHide quote "Nobody" <nob***@nobody.com> wrote in message news:i53ten$3fb$1@speranza.aioe.org... > "David" <NoWh***@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". > > > >
Show quote
Hide quote
"David" <NoWh***@earthlink.net> wrote in message Knowing absolutely nothing about your app I'd say this is a perfect job for 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? a database. Rolling up data like this is something they're really good at.
Show quote
Hide quote
"David" <NoWh***@earthlink.net> wrote "Without having to compare dates'", what gain would that really be, when> 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? 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 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. Show quoteHide quote "Larry Serflaten" <serfla***@gmail.com> wrote in message news:i56153$o26$1@news.eternal-september.org... > > "David" <NoWh***@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" <NoWh***@earthlink.net> wrote It appears the real trouble here is in defining the problem. The user is> 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. 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 Larry Serflaten thanks for responding
RE: > It appears the real trouble here is in defining the problem. The user is What I've done with the program to date is build in as much flexibility as > only allowed to do what you allow them to do. How are you going to > know what the user wants, when your application starts? 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 Show quoteHide quote "Larry Serflaten" <serfla***@gmail.com> wrote in message news:i58rj6$ia3$1@news.eternal-september.org... > > "David" <NoWh***@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 > > > > > >
Show quote
Hide quote
"David" <NoWh***@earthlink.net> wrote in message Some things are better left to a database engine, especially if you plan on 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 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.12%29.aspx Transact-SQL Reference(MS SQL 2000): http://msdn.microsoft.com/en-us/library/aa299742%28SQL.80%29.aspx 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.
Show quote
Hide quote
"David" <NoWh***@earthlink.net> wrote Look again at the structure you posted:> 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? 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 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 has merit, as maybe the approach I'm currently using "is" the best.>work >for very little gain (if any). 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 Show quoteHide quote "Larry Serflaten" <serfla***@gmail.com> wrote in message news:i5avho$9eb$1@news.eternal-september.org... > > "David" <NoWh***@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 > > > > 
Other interesting topics
VB5 Runtime on Windows 7
Question about control properties Which Tab Control is better: TabStrip or SSTab? vbAdvance general dll problem Register ActiveX EXE Using OutputDebugString too much can cause slowness in GUI response Owned On Vista WebCAM Support Send To vs Open With Using fCreateShellLink in VB6? |
|||||||||||||||||||||||
