April 4, 2003 at 5:19 pm
I believe that we all now have proved that we can make the calculations 🙂 But at this point I think the issue is DataStoring vs Dynamic calculations. Using a #table would probably not be the most efficient way of solving this (TheWildHun: I also believe that Jeremy made a less I/O demanding solution to the storing approach). The question (as almost always) is redundancy vs performance (the data DOES exist, the question is what is the most efficient way of storing/retrieving the result of the CALCULATION).
Storing the calculations means that when inserting new values or Updating old values implies Cascading updates 'forward in time' which can result in huge updates, locks and performance degradation. Dynamic calculations, on the other hand, requires self joins, which with efficient indexes, caching and sequential data would, at worst, just mean the read of an additional data page (the data page that contains the data of yesterday for the first record).
At this level of MS SQL Server implementation I don't know the resulting data retrieval methods made in every data page on the 'ON Record.[INT] = Record.[INT] - 1' clause but in worst case every data page has to be iterated an additional time for the self join.
Regards, Hans!
P.S Very interested in comments on this!
April 4, 2003 at 6:31 pm
Hans, I didn't make it clear what I was responding to.
Billy's post indicated a dilemma over sparsity of data. Though the temp table approach is definitely more intensive I/O-wise, it does take care of the date sparsity question not previously addressed. I included the update for the Performance value as an illustration of another method for finding the most recent existing data point... which I also failed to state in my previous post.
Question: How many record inserts are anticipated per day?
Regards,
SJTerrill
April 7, 2003 at 2:50 am
If the table is only updated once a day then a cursor could be used to loop through this table, starting at the record before the changed one up to the current date.
This can be used for the store solution, however you could also use it for the query option with some minor additions. Are users allowed to insert date to any point in the past, or are they prevented from altering data after so-many months? If the data is read-only after a period then you can write the end-of-quarter (or whatever period is used) to another table and use it as a base-point to calculate from. This will mean that you're not calculating from the year dot each time.
Hope this helps.
December 11, 2003 at 8:38 am
quote:
And worse yet, if someone goes back and edits an NAV from several weeks ago - I need to change ALL the performances from that time forward.
Triggers or building it on the fly would be the more conventional method, but I use in-line updates when doing cumulative calculations (~11 mil rows). It is amazingly fast. If your data is updated frequently I would suggest putting the necessary clustered index on the actual table rather than doing it in memory.
December 11, 2003 at 11:47 am
What do you mean by "Inline Updates"?
Data: Easy to spill, hard to clean up!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply