July 30, 2009 at 1:13 pm
I have a stored procedure that updates a table using data from a view. The view uses pivot to pull data from a staging table and cannot be indexed. The first time the view is accessed by the stored procedure after updating the staging table I get timeout errors in my Web application. After the view has been successfully accessed once the stored procedure works fine until the staging table is updated again.
I believethat this is because the view has been rendered and does not need to be updated until the data in the staging table changes. (just a guess)
So, is there any way that I can force the view to render before I call the stored procedure that requires it?
Thanks in advance for any help.
July 30, 2009 at 1:19 pm
Is it something where you could have the proc populate a temp table instead of using a view? That might do what you need, and might be more efficient.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 30, 2009 at 6:16 pm
Post the view...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2009 at 6:02 am
It could be compile time or the time needed to move the data into cache or both.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2009 at 9:44 am
Here is the view:
select
a.Serial,
A.Date,
CASE WHEN ((Ag.Tot_TrafficA + Ag.Tot_TrafficB) > 0 ) THEN '1' ELSE '0' END 'Business_Day',
CASE WHEN (Ag.Est > 0 ) THEN '1' ELSE '0' END 'Estimated',
Ag.Tot_TrafficA,
Ag.Tot_TrafficB,
Ag.Tot_Trans,
A.A_0000,
A.A_0100,
A.A_0200,
A.A_0300,
A.A_0400,
A.A_0500,
A.A_0600,
A.A_0700,
A.A_0800,
A.A_0900,
A.A_1000,
A.A_1100,
A.A_1200,
A.A_1300,
A.A_1400,
A.A_1500,
A.A_1600,
A.A_1700,
A.A_1800,
A.A_1900,
A.A_2000,
A.A_2100,
A.A_2200,
A.A_2300,
B.B_0000,
B.B_0100,
B.B_0200,
B.B_0300,
B.B_0400,
B.B_0500,
B.B_0600,
B.B_0700,
B.B_0800,
B.B_0900,
B.B_1000,
B.B_1100,
B.B_1200,
B.B_1300,
B.B_1400,
B.B_1500,
B.B_1600,
B.B_1700,
B.B_1800,
B.B_1900,
B.B_2000,
B.B_2100,
B.B_2200,
B.B_2300,
T.T_0000,
T.T_0100,
T.T_0200,
T.T_0300,
T.T_0400,
T.T_0500,
T.T_0600,
T.T_0700,
T.T_0800,
T.T_0900,
T.T_1000,
T.T_1100,
T.T_1200,
T.T_1300,
T.T_1400,
T.T_1500,
T.T_1600,
T.T_1700,
T.T_1800,
T.T_1900,
T.T_2000,
T.T_2100,
T.T_2200,
T.T_2300
from
(SELECT distinct M1.serial, M1.Date,
isnull([0],0) as 'A_0000',
isnull([1],0) as 'A_0100',
isnull([2],0) as 'A_0200',
isnull([3],0) as 'A_0300',
isnull([4],0) as 'A_0400',
isnull([5],0) as 'A_0500',
isnull([6],0) as 'A_0600',
isnull([7],0) as 'A_0700',
isnull([8],0) as 'A_0800',
isnull([9],0) as 'A_0900',
isnull([10],0) as 'A_1000',
isnull([11],0) as 'A_1100',
isnull([12],0) as 'A_1200',
isnull([13],0) as 'A_1300',
isnull([14],0) as 'A_1400',
isnull([15],0) as 'A_1500',
isnull([16],0) as 'A_1600',
isnull([17],0) as 'A_1700',
isnull([18],0) as 'A_1800',
isnull([19],0) as 'A_1900',
isnull([20],0) as 'A_2000',
isnull([21],0) as 'A_2100',
isnull([22],0) as 'A_2200',
isnull([23],0) as 'A_2300'
FROM HC4DataTemp AS M1
INNER JOIN (SELECT serial, store, File_path, Date, "Hour", TrafficB
FROM HC4DataTemp) AS M2
PIVOT ( SUM(TrafficB) FOR "Hour" IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],
[10], [11], [12], [13], [14], [15], [16], [17], [18], [19],[20],[21],[22],[23]
)) AS M3
ON M1.Serial = M3.Serial AND M1.Date = M3.Date
) as A
join
(SELECT distinct M1.serial, M1.Date,
isnull([0],0) as 'B_0000',
isnull([1],0) as 'B_0100',
isnull([2],0) as 'B_0200',
isnull([3],0) as 'B_0300',
isnull([4],0) as 'B_0400',
isnull([5],0) as 'B_0500',
isnull([6],0) as 'B_0600',
isnull([7],0) as 'B_0700',
isnull([8],0) as 'B_0800',
isnull([9],0) as 'B_0900',
isnull([10],0) as 'B_1000',
isnull([11],0) as 'B_1100',
isnull([12],0) as 'B_1200',
isnull([13],0) as 'B_1300',
isnull([14],0) as 'B_1400',
isnull([15],0) as 'B_1500',
isnull([16],0) as 'B_1600',
isnull([17],0) as 'B_1700',
isnull([18],0) as 'B_1800',
isnull([19],0) as 'B_1900',
isnull([20],0) as 'B_2000',
isnull([21],0) as 'B_2100',
isnull([22],0) as 'B_2200',
isnull([23],0) as 'B_2300'
FROM HC4DataTemp AS M1
INNER JOIN (SELECT serial, store, File_path, Date, "Hour", TrafficA
FROM HC4DataTemp) AS M2
PIVOT ( SUM(TrafficA) FOR "Hour" IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],
[10], [11], [12], [13], [14], [15], [16], [17], [18], [19],[20],[21],[22],[23]
)) AS M3
ON M1.Serial = M3.Serial AND M1.Date = M3.Date
) as b
on A.Serial = B.serial and A.date = B.date
join
(SELECT distinct M1.serial, M1.Date,
isnull([0],0) as 'T_0000',
isnull([1],0) as 'T_0100',
isnull([2],0) as 'T_0200',
isnull([3],0) as 'T_0300',
isnull([4],0) as 'T_0400',
isnull([5],0) as 'T_0500',
isnull([6],0) as 'T_0600',
isnull([7],0) as 'T_0700',
isnull([8],0) as 'T_0800',
isnull([9],0) as 'T_0900',
isnull([10],0) as 'T_1000',
isnull([11],0) as 'T_1100',
isnull([12],0) as 'T_1200',
isnull([13],0) as 'T_1300',
isnull([14],0) as 'T_1400',
isnull([15],0) as 'T_1500',
isnull([16],0) as 'T_1600',
isnull([17],0) as 'T_1700',
isnull([18],0) as 'T_1800',
isnull([19],0) as 'T_1900',
isnull([20],0) as 'T_2000',
isnull([21],0) as 'T_2100',
isnull([22],0) as 'T_2200',
isnull([23],0) as 'T_2300'
FROM HC4DataTemp AS M1
INNER JOIN (SELECT serial, store, File_path, Date, "Hour", Trans
FROM HC4DataTemp) AS M2
PIVOT ( SUM(Trans) FOR "Hour" IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],
[10], [11], [12], [13], [14], [15], [16], [17], [18], [19],[20],[21],[22],[23]
)) AS M3
ON M1.Serial = M3.Serial AND M1.Date = M3.Date
) as T
on B.Serial = T.serial and B.date = T.date
join
(select
distinct Serial,
Date, sum(trafficB)'Tot_TrafficA',
sum(trafficA)'Tot_TrafficB',
Sum(Trans)'Tot_Trans',
COUNT(NULLIF(Est,0))'Est'
from HC4DataTemp group by serial, date) as Ag
on T.Serial = Ag.Serial and T.Date = Ag.Date
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply