Looking for Ideas on a Long-running Stored Proc

[JohnBates]

hi experts,

We have a (apparently) poorly-designed process to update several tables. It runs for 6 hours, using 80-100 % of the CPU during that time. When this proc is not running, the CPU is at an average of 45 %.

This developer chose to use select from views, which select from other views.... you can see it all below.
Just 1 stored proc, then everything else is view-driven.

I know there are MANY factors that can cause a job to run too long... I have reviewed the indexes (even recreated them)... the tables appear to have the right columns indexed.

In general... is the following technique a good one, in terms of performance?

Thanks for any feedback you are willing to give !
John

CREATE PROCEDURE dbo.procUpdate_ContributionDetail
AS
SET NOCOUNT ON

BEGIN
drop table [dbo].[tblCDb]

SELECT dbo.vwCD.*
INTO dbo.tblCDb
FROM dbo.vwCD

drop table [dbo].[tblCD]
exec sp_rename 'tblCDb', 'tblCD'
END
___________________
CREATE VIEW dbo.vwCD
AS
SELECT dbo.vwCD_Master.myyear, dbo.vwCD_Master.dlrno AS dlrnum, dbo.vwCD_Master.prdlncd AS division, dbo.[Product Groups].[prod group description],
dbo.vwCD_cont.shipments, dbo.vwCD_cont.[sumoftrans amt] AS fund, dbo.vwCD_Match.[sumoftrans amt] AS gmmatch,
dbo.vwCD_Master.conpct AS contribution
FROM dbo.vwCD_Master INNER JOIN
dbo.[Product Groups] ON dbo.vwCD_Master.prdlncd = dbo.[Product Groups].[prod group code] LEFT OUTER JOIN
dbo.vwCD_cont ON dbo.vwCD_Master.myyear = dbo.vwCD_cont.[Year] AND dbo.vwCD_Master.dlrno = dbo.vwCD_cont.dlrnum AND
dbo.vwCD_Master.prdlncd = dbo.vwCD_cont.[Product Group] LEFT OUTER JOIN
dbo.vwCD_Match ON dbo.vwCD_Master.myyear = dbo.vwCD_Match.[Year] AND dbo.vwCD_Master.dlrno = dbo.vwCD_Match.dlrnum AND
dbo.vwCD_Master.prdlncd = dbo.vwCD_Match.[Product Group]
________________________________
CREATE VIEW dbo.vwCD_Master
AS
SELECT dbo.vwYears.Year AS myyear, dbo.vwEnrollment_master_CD.dlrno, dbo.vwEnrollment_master_CD.prdlncd,
dbo.vwEnrollment_master_CD.conpct
FROM dbo.vwYears CROSS JOIN
dbo.vwEnrollment_master_CD
___________________________
CREATE VIEW dbo.vwCD_cont
AS
SELECT dbo.Promos.Year, dbo.[Fund Transactions].[Dlr Num] AS dlrnum, dbo.[Fund Transactions].[Product Group],
dbo.[Product Groups].[prod group description] AS division, dbo.vwEnrollment_coop.conpct AS contribution, SUM(dbo.[Fund Transactions].Units)
AS shipments, SUM(dbo.[Fund Transactions].[Trans Amt]) AS [sumoftrans amt]
FROM dbo.[Fund Transactions] LEFT OUTER JOIN
dbo.vwEnrollment_coop ON dbo.[Fund Transactions].[Dlr Num] = dbo.vwEnrollment_coop.dlrno AND
dbo.[Fund Transactions].[Product Group] = dbo.vwEnrollment_coop.prdlncd LEFT OUTER JOIN
dbo.[Dealer Names And Addresses] ON dbo.[Fund Transactions].[Dlr Num] = dbo.[Dealer Names And Addresses].[Dlr Num] LEFT OUTER JOIN
dbo.Promos ON dbo.[Fund Transactions].Promo = dbo.Promos.Promo LEFT OUTER JOIN
dbo.[Product Groups] ON dbo.[Fund Transactions].[Product Group] = dbo.[Product Groups].[prod group code]
WHERE (dbo.[Fund Transactions].[Trans Type] = N'a') AND (dbo.Promos.[Promo Group] = 'n') AND (dbo.Promos.[Payment Group] = 'p')
GROUP BY dbo.Promos.Year, dbo.[Fund Transactions].[Dlr Num], dbo.[Product Groups].[prod group description], dbo.vwEnrollment_coop.conpct,
dbo.[Fund Transactions].[Product Group]

 

[mrdenny]

Nested views do have some performence issues associated with them. I've gone over the reasons here thread183-923515.

If you do selects off of each of these 3 views how many records return. I'm thinking a procedure like this shouldn't take more than a few minutes unless we are working with hundreds of millions of records.

Also how many records are in the tblCD table?

If you can also provide a list of all the indexes on these source tables (Scripting the tables will be just fine) we'll see what we can't do about speeding this up for you.

Also next time this runs, please watch the disk queue in perfmon and see what the disk queue does.

Post all that to the ticket, and I'll take a look at it Monday.

Denny

--Anything is possible. All it takes is a little research. (Me)