Lynn Pettis (11/27/2008)
jacroberts (11/27/2008)
Lynn Pettis (11/27/2008)
jacroberts (11/27/2008)
timothyawiseman (11/27/2008)
jacroberts (11/25/2008)
timothyawiseman (11/24/2008)[hr
This is true, for a small number of rows. Your RBAR TVF will become ineffecient should requirements change and the number of rows it has to generate becomes quite large. This is what we all have been talking about in this thread. Try it, see how long your RBAR TVF takes to generate 10,000 rows versus the same function written in a set-based manner.
But it's not for 10,000 rows it's for a max of 31 days in a month. Even if it were generating 10,000 rows, which incidently would table about 2 seconds, it would still be insignificant compared to the run time of the query.
You are missing the major points. One requirements change, and what is effecient now won't be an longer. And two, someone is going to come along and see your code in your RBAR TVF and use it somewhere else and it won't be effecient at all.
If you take the time to write solid, efficient, and scalable code all the time you don't have to worry about those two things occuring as much. Something I have learned since becoming a member of this awesome site and getting tips and tricks from the like of Jeff Moden, Gail Shaw, etc..
It's horses for courses. There are times when it's good and times when it's bad to use different methods. If you think using a lookup table will always make your queries run faster then you are wrong. It is quite possible to use a lookup table on a query that runs quickly on a small amount of data, when there are requierments changes or even just with the progression of time causing more data to be added to a table, or the lookup table becomes larger the query will become horrendously slow and will need someone to come in and optimise it. In the example I gave the number of days reported on never changed from the number of days in a month; what did change was the number of days in the lookup table and the number of rows in the tables to be reported on. This the reason the report slowed down and using an RBAR TVF fixed that particular problem. I'm not saying that you should use them indiscriminately but there are times when they are suitable. Not only did the report run faster but it also removed the need to regularly insert more rows into the RefDates table as the new TVF would work for any dates, not just the ones on the lookup table.