CREATE VIEW need help please - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CREATE VIEW need help please

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2005-12-21 : 09:54:19

Hello,
I created 3 related views and at the third view, it takes about 28 seconds to list 100,000 rows. I am trying to modify the views, like order of PKs.... but no help at all.
Can you show me how to modify this views to make it load faster?
Big thanks....

create VIEW dbo.Day1 AS
SELECT Occurred.Occurred_ID, pro.Con_id,
r_num.numbers+Occurred.Occurred_From AS dayy
FROM r_num, pro INNER JOIN (job INNER JOIN Occurred ON job.job_ID = Occurred.job_ID) ON pro.pro_ID = job.pro_ID
where r_num.numbers<=Occurred.Occurred_to - Occurred.Occurred_From

create VIEW dbo.Day2 AS
SELECT Occurred.Occurred_ID, pro.Con_Id,
(ISNULL(Occurred.La_Money_Occurred,0)+ISNULL(Occurred.Tra_Money_Occurred,0)+
ISNULL(Occurred.O_Occurred,0))/(isnull(datediff(dd,Occurred.Occurred_from,Occurred.Occurred_to),0)+1) AS day_ave
FROM pro INNER JOIN (job INNER JOIN Occurred ON job.job_ID = Occurred.job_ID) ON pro.pro_ID = job.pro_ID

create VIEW dbo.Day3 AS
SELECT Day1.Occurred_ID,
Day1.Con_ID,
convert(char(3),Day1.dayy) + '-' + convert(char(2),Day1.dayy,11) as ddate,
Sum(Day2.day_ave) AS SumOfday_ave
FROM Day1 LEFT JOIN Day2 ON Day1.Occurred_ID = Day2.Occurred_ID
group by Day1.Occurred_ID, Day1.Con_ID, dayy

There is no stupid question.
www.single123.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources