Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-16 : 09:38:06
|
| Robert writes "Dear SQLTeam,I have SQL server standard, and I know that indexed views are a feature of enterprise. However, I have found the need for an indexed view, and I really don't want to spend $20,000.I found a very simple idea could save me much time. I'm calculating moving averages for financial data. I wrote a function to do this, and created a view with three columns.. one for 5 day, 50 day, and 200 day. It ran very, very slow.. I thought about it for a second and it hit me.. Add those three columns to my table. Add the three colums in that table to my view. So, I did. Then I wrote the following SQL code to leverage the "updatable view" functionality in SQL 2000.create procedure sp_updatePoorMansIndexedView as begin --updates all the columns in the table to have the needed values. update tbMyTable set tbColA = vwColA where tbColA is NULL update tbMyTable set tbColB = vwColB where tbColB is NULL update tbMyTable set tbColC = vwColC where tbColC is NULLendand that updates my table, effectively creating an indexed view, and saved me 20 seconds * 700 clients or ~14000 seconds per client.. when I was just wasting CPU re-calculating things I could store.. just call the stored procedure before accessing the table.. (or specifically before code that needs to access those colums, or right after you insert new data into your table..)I have yet to try this with more than one base table. I bet it would work. What do you think? Should I try it? Is this a unique topic, or did I just miss the ball entirely? Is there a demand for a poor man's indexed view?My Best,Robert Wafle" |
|
|
|
|
|
|
|
Indexed views - I need them but I can not afford them