Computed column in view VS trigger on update

Expand Post »

Hi guys,

Let me describe the situation I am in. I have a table of 2000 objects and each object has a range of different prices associated with it, stored in another table.

I have created a view which serves to list the objects as well as their average price. At present I have something along the lines of

CREATE VIEW objview AS
select o.*, (select sum(op.price)/count(*) from objprice op where op.objectid=o.objectid) averageprice
from obj o

I have a feeling that the above might not be very efficient.

Does anyone have any input regarding speed and efficiency on
- using a join in the above instead of a nested query
- having a fixed field in the original obj table which gets updated via a trigger when new values are inserted into objprice

Which is the best solution?

Thanks!

Similar Threads

entry widget in Python
update database through gridview in ASP.NET
To update database through gridview in ASP.NET
Default value of a column in MS SQL
Good Luck w/This one: Excel Question VBA/Macro/other in MS Access and FileMaker Pro

waolly

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Sep 2006

Permalink

Sep 7th, 2006

Re: Computed column in view VS trigger on update

Use SQL Servers AVG keyword/function

MS SQL Syntax (Toggle Plain Text)
SELECT
    o.column1, o.column2, ...
    avg(p.price)
FROM
    Objecttable o
    JOIN Pricetable p ON o.id = p.id
WHERE
    <put criteria IF needed otherwise leave WHERE out>
GROUP BY 
    o.column1, 0.column2, .... (but NOT the COLUMN IN the avg() expr)
SELECT
    o.column1, o.column2, ...
    avg(p.price)
FROM
    Objecttable o
    JOIN Pricetable p on o.id = p.id
WHERE
    <put criteria if needed otherwise leave WHERE out>
GROUP BY 
    o.column1, 0.column2, .... (but not the column in the avg() expr)

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Modal Function

Next Thread in MS SQL Forum Timeline: SQL query

DaniWeb Message

Cancel Changes

User Name
Password