Using SP & TMP tables in place of multiple views
-
We are accessing a database that is normalized to the point of inefficiency. To retrieve the data a number of Views have been created to return the data ina usable format. Not only does the custom application call these views directly but the views reference other views and performance suffers considerably. We are attempting to use stored procedures to create temp tables to be used in place of the views as follows...
View 1 contains 4 references to view 2 as follows
Create view1 as select * from view2 join (select ... from view 2 where ...) UNION select ... from view2 join (select ... from view2 where ...)
View5 contains a select that joins view2 and view4
View7 contains a select that joins view 4 to view5
Is all rather ugly and can be extremely slow. I have attempted to rewrite the views as stored procedures that create temp tables #TMP1, TMP2, TMP4, #TMP5, TMP7 in place of the views. In each stored proc I check for the existence of the temp tables (formerly views) needed and if not in existence create them as follows
Proc 1
if not exists (select * from sysobjects where name = '#tmp2' and xtype='u')
begin
exec CREATE_TMP2
end
if not exists (select * from sysobjects where name = '#TMP1' and xtype='u')
begin
select * into #tmp1 from {original view 1 code}
end --uses #TMP2 in place of VEIW2
etc ...
So basically in each view I ensure any references already exist as Temp Tables and if not call a proc to create them. In this manner each view code is referenced only one time. At the end of the users calling script a stored proc to drop all temp tables will be called.
My current problem is that temp tables created in a SP are not available to the calling script. To fix this I can use global temp tables (##TMP1, ##TMP2, etc...). However there are multiple users (2-5) so I would like to create the tables with the SPID attached SELECT @TAB1='##TMP1_'+@@SPID
The code is very complicated and I do not wish to use EXEC statements to refer to these tables and the user calling .NET code should be able to refer by Temp Table name. I would rather simply be able to refer to the table in a TSQL statement directly as Select * from @Tab1 (where @tab1 would be something like ##TMP1_171)
The views currently work but are exceedingly slow and the temp table concept with stored procedures cuts execution time dramatically. Also the logic embedded in the current VIEWS must be retained.
Thoughts?
Thanks
Mike Hoyt
-
It seems to me that replacing those views with global temp tables would be a really bad idea. You definitely do not want several denormalized copies of your database in tempdb.
I am sure we can give you a much better solution. First of all, what's stopping you from having the sp return the data directly to the client?
Second, Do you really need these nested views? Why not just write stored procs (or a single view) to return the data you need?
SQL guy and Houston Magician
-
Robert
I was asked to make seven VIEWS that are used repeatedly more efficient. The views are on an extremely normalized database and the view themselves are Butt Ugly. I will include HOWEBVAC, HOWEBVAC1 and HOWEBVAC4 for your amusement. Be aware that HOWEBVAC5 refers to HOWEBVAC1 and HOWEBVAC4 and HOWEBVAC7 refers to HOWEBVAC4 and HOWEBVAC5. In this scenario often the same views are called repeatedly while performing a single select on another view! For this reason I thought that creating a Temp table would be more efficient. I have already checked all indicies, etc... Open to other ideas ...
Mike
ALTER View HOWEBVAC as
--Get all entries except 878-VAC non paid blockers, except for 878-blockers that have no thru date (meaning they are not linked to a week)
SELECT PAYCODENAME, FROMDATE, THRUDATE, AMOUNT, PAYCODEDTE, EFFECTDTE,
(case(select count(*) from HOWEBVAC1 a where a.PERSONNUM = b.PERSONNUM and a.FROMDATE = b.FROMDATE and SUBSTRING(a.PAYCODENAME,1,3) <> '140')
when 0 then 'N'
when 1 then 'N'
else 'Y'
END
)
AS BLOCKERS,
APPR, SIGNEDOFF, HISTEDIT, PERSONNUM, PERSONFULLNAME, BADGENUM, COMMENTTEXT, FISCYEAR
from HOWEBVAC1 b where
( NOT (substring(PAYCODENAME,1,3) = '878' and isnull(Thrudate,'') <> ''))
UNION
--Create a Week record for any 878-VAC non-paid blockers (can't actually create 40 hour 878-VAC card and 5 x 8 hours 878-VACs or we
--will have duplicates i.e. 80 hrs. The user will be able to add a 40 hour 878-VAC record, and if from and thru dates are entered
--it will actually create the split records only. This will save the user time. This part of the script needs to group the 5 x 8
--878-VAC cards togethter to make a record for the view.
SELECT a.PAYCODENAME,a.FROMDATE,a.THRUDATE,SUM(a.AMOUNT) AS AMOUNT,MAX(a.PAYCODEDTE) AS PAYCODEDTE,'' as EFFECTDTE, 'Y' AS BLOCKERS,
MIN(a.APPR) AS APPR,MIN(a.SIGNEDOFF) as SIGNEDOFF, MIN(a.HISTEDIT) as HISTEDIT,a.PERSONNUM,a.PERSONFULLNAME,a.BADGENUM,MAX(a.COMMENTTEXT) as
COMMENTTEXT,a.FISCYEAR
from HOWEBVAC1 a left outer join
(select * from HOWEBVAC1 where substring(PAYCODENAME,1,3) in ('110','112','115','130') and isnull(Thrudate,'') <> '') b
on a.fromdate = b.fromdate and a.personnum = b.personnum where
substring(a.PAYCODENAME,1,3) = '878' and isnull(a.Thrudate,'') <> '' and b.PAYCODENAME is NULL
group by a.PAYCODENAME,a.FROMDATE,a.THRUDATE,a.PERSONNUM,a.PERSONFULLNAME,a.BADGENUM,a.FISCYEAR
ALTER View HOWEBVAC1 as
SELECT PC1.NAME PAYCODENAME,
CASE
WHEN ADJAPPLYDTM <> APPLYDTM AND CM.COMMENTTEXT IS NULL THEN CONVERT(VARCHAR(10), ADJAPPLYDTM, 101 )
WHEN (CM.COMMENTTEXT IS NOT NULL AND substring(PC1.NAME,1,3) = '140')
THEN SUBSTRING(CM.COMMENTTEXT,1,2) + '/' + SUBSTRING(CM.COMMENTTEXT,4,2) + '/' + CONVERT(VARCHAR(4), TI.EVENTDTM, 20 )
WHEN BB.FromDate IS NULL
THEN CONVERT(VARCHAR(10), TI.EVENTDTM, 101 )
ELSE BB.FromDate
END AS FROMDATE,
CASE
WHEN BB.ThruDate IS NULL
THEN '' /* CONVERT(VARCHAR(10), TI.EVENTDTM, 101 )*/
ELSE BB.ThruDate
END AS THRUDATE,
CAST(TI.DURATIONSECSQTY AS FLOAT (6,2)) / 3600 AS AMOUNT,
case
WHEN ADJAPPLYDTM <> APPLYDTM THEN CONVERT(VARCHAR(10), ADJAPPLYDTM, 101 )
ELSE
CONVERT(VARCHAR(10), TI.EVENTDTM, 101 )
END AS PAYCODEDTE,
case
WHEN ADJAPPLYDTM <> APPLYDTM THEN CONVERT(VARCHAR(10), APPLYDTM, 101 )
ELSE
''
END AS EFFECTDTE,
CASE WHEN MGR.PERSONID IS NULL THEN 'N' ELSE 'Y' END AS APPR,
CASE
WHEN datediff(day,TI.EVENTDTM,ve.mgrsignoffthrudtm)>= 0 then 'Y'
ELSE 'N'
END AS SIGNEDOFF,
CASE datediff(day,wt.ADJAPPLYDTM,wt.APPLYDTM)
WHEN 0 THEN 'N'
ELSE 'Y'
END as HISTEDIT,
VE.PERSONNUM,
VE.FULLNM AS PERSONFULLNAME,
BA.BADGENUM,CM.COMMENTTEXT,
CASE
WHEN ADJAPPLYDTM <> APPLYDTM AND CM.COMMENTTEXT IS NULL THEN dbo.FN_FiscalYear(ADJAPPLYDTM)
WHEN (CM.COMMENTTEXT IS NOT NULL AND substring(PC1.NAME,1,3) = '140')
THEN
dbo.FN_FiscalYear(convert(datetime,SUBSTRING(CM.COMMENTTEXT,1,2) + '/' + SUBSTRING(CM.COMMENTTEXT,4,2) + '/' + CONVERT(VARCHAR(4), TI.EVENTDTM, 20 )))
WHEN BB.FromDate IS NULL
THEN dbo.FN_FiscalYear(TI.EVENTDTM)
ELSE dbo.FN_FiscalYear(BB.FromDate)
END AS FISCYEAR
FROM TIMESHEETITEM TI JOIN
WTKEMPLOYEE WE ON (TI.EMPLOYEEID = WE.EMPLOYEEID) JOIN
PERSON VE ON (WE.PERSONID = VE.PERSONID) LEFT OUTER JOIN
BADGEASSIGN ba ON WE.PERSONID = ba.PERSONID AND getdate() BETWEEN ba.EFFECTIVEDTM AND ba.EXPIRATIONDTM LEFT OUTER JOIN
WFCTOTAL wt on TI.TIMESHEETITEMID = wt.TIMESHEETITEMID LEFT OUTER JOIN
--LABORACCT LA1 on LA1.LABORACCTID = TI.LABORACCTID LEFT OUTER JOIN
PAYCODE PC1 ON (TI.PAYCODEID = PC1.PAYCODEID) LEFT OUTER JOIN
DATASOURCE DS ON (TI.DATASOURCEID = DS.DATASOURCEID) LEFT OUTER JOIN
CLIENTCONTEXT CC ON (DS.CLIENTCONTEXTID = CC.CLIENTCONTEXTID) LEFT OUTER JOIN
TSCOMMENTMM TC ON (TI.TIMESHEETITEMID = TC.TIMESHEETITEMID) LEFT OUTER JOIN
COMMENTS CM ON (TC.COMMENTID = CM.COMMENTID) LEFT OUTER JOIN
(Select Min(FromDate) as FromDate,
Max(ThruDate)as ThruDate,
PERSONNUM, COMMENTTEXT,FISCYEAR
FROM dbo.HOWEBVAC6
group by personnum, commenttext,FISCYEAR ) BB
ON (CM.COMMENTTEXT = BB.COMMENTTEXT and
BB.FISCYEAR =
(CASE
WHEN ADJAPPLYDTM <> APPLYDTM AND CM.COMMENTTEXT IS NULL THEN dbo.FN_FiscalYear(ADJAPPLYDTM)
WHEN (CM.COMMENTTEXT IS NOT NULL AND substring(PC1.NAME,1,3) = '140')
THEN
dbo.FN_FiscalYear(convert(datetime,SUBSTRING(CM.COMMENTTEXT,1,2) + '/' + SUBSTRING(CM.COMMENTTEXT,4,2) + '/' + CONVERT(VARCHAR(4), TI.EVENTDTM, 20 )))
WHEN BB.FromDate IS NULL
THEN dbo.FN_FiscalYear(TI.EVENTDTM)
ELSE dbo.FN_FiscalYear(BB.FromDate)
END) )
AND (VE.PERSONNUM = BB.PERSONNUM) LEFT OUTER JOIN
MGRAPPROVAL MGR ON (TI.EMPLOYEEID = MGR.PERSONID) AND
(TI.EVENTDTM = MGR.APPROVALDTM)
WHERE substring(PC1.NAME,1,3) in ('110','112','115','140','130','878')
--AND TI.EVENTDTM >= convert(varchar(4),getdate(),20)+ '/01/01'
AND TI.DELETEDSW = 0
ALTER View HOWEBVAC4 as
SELECT
Distinct PC1.NAME PAYCODENAME,
CASE
WHEN ADJAPPLYDTM <> WT.APPLYDTM AND CM.COMMENTTEXT IS NULL THEN CONVERT(VARCHAR(10), ADJAPPLYDTM, 101 )
WHEN (CM.COMMENTTEXT IS NOT NULL AND PC1.NAME = '140-Holiday Pay')
THEN SUBSTRING(CM.COMMENTTEXT,1,2) + '/' + SUBSTRING(CM.COMMENTTEXT,4,2) + '/' + CONVERT(VARCHAR(4), TI.EVENTDTM, 20 )
WHEN BB.FromDate IS NULL
THEN CONVERT(VARCHAR(10), TI.EVENTDTM, 101 )
ELSE BB.FromDate
END AS FROMDATE,
CASE
WHEN BB.ThruDate IS NULL
THEN ''
ELSE BB.ThruDate
END AS THRUDATE,
CAST(TI.DURATIONSECSQTY AS FLOAT (6,2)) / 3600 AS AMOUNT,
case
WHEN ADJAPPLYDTM <> WT.APPLYDTM THEN CONVERT(VARCHAR(10), ADJAPPLYDTM, 101 )
ELSE
CONVERT(VARCHAR(10), TI.EVENTDTM, 101 )
END AS PAYCODEDTE,
case
WHEN ADJAPPLYDTM <> WT.APPLYDTM THEN CONVERT(VARCHAR(10), WT.APPLYDTM, 101 )
ELSE
''
END AS EFFECTDTE,
CASE WHEN MGR.PERSONID IS NULL THEN 'N' ELSE 'Y' END AS APPR,
CASE
WHEN datediff(day,TI.EVENTDTM,ve.mgrsignoffthrudtm)>= 0 then 'Y'
ELSE 'N'
END AS SIGNEDOFF,
CASE datediff(day,wt.ADJAPPLYDTM,wt.APPLYDTM)
WHEN 0 THEN 'N'
ELSE 'Y'
END as HISTEDIT,
VE.PERSONNUM,
VE.FULLNM AS PERSONFULLNAME,
BA.BADGENUM,CM.COMMENTTEXT,
CASE
WHEN ADJAPPLYDTM <> WT.APPLYDTM AND CM.COMMENTTEXT IS NULL THEN dbo.FN_FiscalYear(ADJAPPLYDTM)
WHEN (CM.COMMENTTEXT IS NOT NULL AND PC1.NAME = '140-Holiday Pay')
THEN
dbo.FN_FiscalYear(convert(datetime,SUBSTRING(CM.COMMENTTEXT,1,2) + '/' + SUBSTRING(CM.COMMENTTEXT,4,2) + '/' + CONVERT(VARCHAR(4), TI.EVENTDTM, 20 )))
WHEN BB.FromDate IS NULL
THEN dbo.FN_FiscalYear(TI.EVENTDTM)
ELSE dbo.FN_FiscalYear(BB.FromDate)
END AS FISCYEAR,
LAC.LABORLEV3NM AS Department,
LAC2.LABORLEV3NM as LL3,
LAC2.LABORLEV4NM as LL4,
LAC2.LABORLEV5NM as LL5,
LAC2.LABORLEV6NM as LL6
--VP.HOMELABORLEVELNAME3 AS Department
FROM TIMESHEETITEM TI JOIN
WTKEMPLOYEE WE ON (TI.EMPLOYEEID = WE.EMPLOYEEID AND TI.DELETEDSW = 0) JOIN
PERSON VE ON (WE.PERSONID = VE.PERSONID) LEFT OUTER JOIN
BADGEASSIGN ba ON WE.PERSONID = ba.PERSONID AND getdate() BETWEEN ba.EFFECTIVEDTM AND ba.EXPIRATIONDTM LEFT OUTER JOIN
WFCTOTAL wt on TI.TIMESHEETITEMID = wt.TIMESHEETITEMID LEFT OUTER JOIN
--LABORACCT LA1 on LA1.LABORACCTID = TI.LABORACCTID LEFT OUTER JOIN
PAYCODE PC1 ON (TI.PAYCODEID = PC1.PAYCODEID) LEFT OUTER JOIN
DATASOURCE DS ON (TI.DATASOURCEID = DS.DATASOURCEID) LEFT OUTER JOIN
CLIENTCONTEXT CC ON (DS.CLIENTCONTEXTID = CC.CLIENTCONTEXTID) LEFT OUTER JOIN
TSCOMMENTMM TC ON (TI.TIMESHEETITEMID = TC.TIMESHEETITEMID) LEFT OUTER JOIN
COMMENTS CM ON (TC.COMMENTID = CM.COMMENTID) LEFT OUTER JOIN
vu_WFAREPTOTAL WFA on (WFA.employeeid = ti.employeeid and WFA.paycodeid = ti.paycodeid and wfa.applydtm = ti.eventdtm and wfa.hoursqty = (ti.durationsecsqty/3600))
LEFT OUTER JOIN WFAREPLABACCT WFAL on WFAL.WFAREPACTYSPANID = WFA.WFAREPACTYSPANID LEFT OUTER JOIN
LABORACCT LAB on LAB.LABORACCTID = WFAL.LABORACCTID LEFT OUTER JOIN
COMBHOMEACCT CH on (CH.employeeid = TI.EMPLOYEEID and getdate() BETWEEN CH.EFFECTIVEDTM and CH.EXPIRATIONDTM) LEFT OUTER JOIN
LABORACCT LAC on LAC.LABORACCTID = CH.LABORACCTID LEFT OUTER JOIN
COMBHOMEACCT CH2 on (CH2.employeeid = TI.EMPLOYEEID and (case
WHEN ADJAPPLYDTM <> WT.APPLYDTM THEN CONVERT(VARCHAR(10), ADJAPPLYDTM, 101 )
ELSE
CONVERT(VARCHAR(10), TI.EVENTDTM, 101 )
END ) BETWEEN CH2.EFFECTIVEDTM and CH2.EXPIRATIONDTM) LEFT OUTER JOIN
LABORACCT LAC2 on LAC2.LABORACCTID = CH2.LABORACCTID LEFT OUTER JOIN
--VP_PERSON VP on VP.personid = TI.EMPLOYEEID LEFT OUTER JOIN
(Select Min(FromDate) as FromDate,
Max(ThruDate)as ThruDate,
PERSONNUM, COMMENTTEXT,FISCYEAR
FROM dbo.HOWEBVAC6
group by personnum, commenttext,FISCYEAR ) BB
ON (CM.COMMENTTEXT = BB.COMMENTTEXT and
BB.FISCYEAR =
(CASE
WHEN ADJAPPLYDTM <> WT.APPLYDTM AND CM.COMMENTTEXT IS NULL THEN dbo.FN_FiscalYear(ADJAPPLYDTM)
WHEN (CM.COMMENTTEXT IS NOT NULL AND PC1.NAME = '140-Holiday Pay')
THEN
dbo.FN_FiscalYear(convert(datetime,SUBSTRING(CM.COMMENTTEXT,1,2) + '/' + SUBSTRING(CM.COMMENTTEXT,4,2) + '/' + CONVERT(VARCHAR(4), TI.EVENTDTM, 20 )))
WHEN BB.FromDate IS NULL
THEN dbo.FN_FiscalYear(TI.EVENTDTM)
ELSE dbo.FN_FiscalYear(BB.FromDate)
END) )
AND (VE.PERSONNUM = BB.PERSONNUM) LEFT OUTER JOIN
MGRAPPROVAL MGR ON (TI.EMPLOYEEID = MGR.PERSONID) AND
(TI.EVENTDTM = MGR.APPROVALDTM)
WHERE PC1.NAME in ('110-VAC Taken','112-VAC Prepay','115-VAC Untaken','130-VAC Terminat','140-Holiday Pay','878-VAC')
--AND TI.EVENTDTM >= convert(varchar(4),getdate(),20)+ '/01/01'
AND TI.DELETEDSW = 0 --and TI.LABORACCTID is not null
-
How tied are you to these views? I know you need to retain the logic. But are you at a point where these views? You said that you were asked to improve the performance of these views. Did you say that because these views absolutely must stay (for whatever reason)?
How many different queries do you run against these views? Can we break out some use cases?
SQL guy and Houston Magician
-
A consultant for the software we purchased came to our site and developed the first version of these. Our software developer on this project then made them more efficient.
Lets assume we are tied to this logic. If this were a one user system there would not be a problem and I could proceed.
Mike
-
How about this approach? Assume this is a stored proc that will simulate the output of View1, which itself references View2, which itself references View3
CREATE PROCEDURE CallingProcForView1
AS
-----------
if not exists (select * from sysobjects where name = '#tmp1' and xtype='u')
create table #tmp1 -- simulates the result set of View1
( Field1 INT NOT NULL
, Field2 INT etc )
if not exists (select * from sysobjects where name = '#tmp2' and xtype='u')
create table #tmp2 -- simulates the result set of View2
( Field1 INT NOT NULL
, Field2 INT etc )
if not exists (select * from sysobjects where name = '#tmp3' and xtype='u')
create table #tmp3 -- simulates the result set of View3
( Field1 INT NOT NULL
, Field2 INT etc )
-----------
EXEC ValuesForView3
EXEC ValuesForView2
EXEC ValuesForView1
-----------
SELECT * FROM #tmp1
------------------------------------------------------------------------
CREATE PROCEDURE ValuesForView3
AS
INSERT INTO #tmp3 -- this temp table is created ALWAYS in the calling proc!
( Field1, Field2, etc)
SELECT blah, blah, etc
FROM {from statement for View3}
------------------------------------------------------------------------
CREATE PROCEDURE ValuesForView2
AS
INSERT INTO #tmp2 -- this temp table is created ALWAYS in the calling proc!
( Field1, Field2, etc)
SELECT blah, blah, etc
FROM {from statement for View2, rewritten to change the reference to View3 with a reference to #tmp3}
------------------------------------------------------------------------
CREATE PROCEDURE ValuesForView1
AS
INSERT INTO #tmp3 -- this temp table is created ALWAYS in the calling proc!
( Field1, Field2, etc)
SELECT blah, blah, etc
FROM {from statement for View1, rewritten to change the reference to View2 with a reference to #tmp2}
-----------
You would need to create calling procedures for each View, and procs for populating the views for each view. To get the data, always go through the calling proc, which creates the required temp tables for all the cascading stored procs it references. When the calling proc ends, it will then automatically drop all these instances of the temp tables that have been used in the calling proc, and all the child procs.
This is the approach I am taking to solve a similar issue with a client, though the views that I have to deal were created by a different consultant, one who didn't have access to the same mind altering drugs as the one you dealt with.
-
Grasshopper
Thank you. I think this is the approach I will have to use. Requiress a bit more work on my part creating the table definitions as opposed to SELECT * INTO but is probably the best way to do it. A similar suggestion was made on another forum as well which confirms this ...
Mike
-
Hi Mike,
I noticed a typo in a bit of the code - see correction below
CREATE PROCEDURE ValuesForView1
AS
INSERT INTO #tmp1 -- this temp table is created ALWAYS in the calling proc!
( Field1, Field2, etc)
SELECT blah, blah, etc
FROM {from statement for View1, rewritten to change the reference to View2 with a reference to #tmp2}
Also, I'm curious about more info on this approach, as I dreamed it up and tried it with some test SPs on the dev database, but haven't implemented it yet. Could you give me the link to the other forum where you found something similar suggested?
thanks, Malcolm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply