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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2005-12-06 : 18:10:46
|
| Hi,Our company works on insurance products. We had a sproc that summed up an number of items and returned a final premium, payments etc for ONE particular quote. We had a business requirement to provide a report that displayed multiple total premiums for a number of quotes. My first (and failed) attempt was just to convert this sproc into a udf where the quoteID was a parameter. Due to the number of records in a production environment the response time was horrible (clearly a bad idea).That being said, I am looking into building a more compact calculated view which would contain the necessary items, but I am concerned about performance. Excuse my ignorance concerning views but is it possible for a view to have an index? Is a calcuated view the best approach to this problem or should I go down a different path? Any tips on how to maximize performance?Ignoring the report requirement, most of the time the product only needs to display the total premium for ONE particular quote. For this scenario, would it be best to have a sproc that only calculated the premium for the one record or would it be a good idea to query this new view? (I would think the view itself would have a fair amount of overhead, when we only needed one result, it might be better to calculate it seperately)Just wanting to bounce this off a few others before starting.Here is the link to the related issue (I've decided to scrap that idea and focus on the view approach, assuming no one has any objections)http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58619Nic |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-12-06 : 18:19:20
|
| Hard to say nic.If the sproc contains a single select statements to achieve the result then throw into a view. The optimiser should take care of it.If it takes multiple steps.. don't be afraid to use use temp tables and loops.DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
 |
|
|
|
|
|
Calculated View/UDF