how would you optimise this query???
-
i have a number of business programs. Each program is started anew at the beginning of each fiscal year. each program has a number of goals and customers subscribe to the goals.
i have to pull all this info out of the database.
i have a cursor that gets the first program, inside this program i have a cursor that gets the first period, and inside that i have a cursor that gets the info on each goal. it has to work like, get the program, then get the specified period ( of which there is a list) and itterate through each goal in that period for that program.
program cursor
{
period cursor
{
goal cursor
}
exec sproc that returns data
}
}
}
this takes ages ( hours and hours ) to run. is there any way i could have designed this using joins and simple selects to make it more efficient??
-
The answer is probably yes, but it does depend on what 'sproc' does.
If you post an example with the table structure, some sample data, and the expected output, you'll get an answer in no time...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
the inner sproc takes the @customerId,@programId, @period, , calls another sproc and returns all "goals" and goal related info for that particular customer in relation to that program. here is the format of the output table:
FinalReportData
-- (
-- CustomerID INT,
-- CustomerName VARCHAR(50),
-- ProgramID INT,
-- ProgramName VARCHAR(50),
-- GoalID INT,
-- GoalDescription CHAR(50) NULL,
-- GoalTargetAmount DECIMAL (19,9) ,
-- PeriodGroupGoalRevenue Decimal(14,2) NULL,
-- PeriodGroupBaseRevenue Decimal(14,2) NULL,
-- MinPercent Decimal(14,5) NULL,
-- MaxPercent Decimal(14,5) NULL,
-- ScoreID INT NULL,
-- ScorePercent DECIMAL (19,9) ,
-- Confirmed CHAR(3) NULL,
-- IsSuggestedScore TINYINT NULL,
-- ModifiedByName CHAR(30) NULL,
-- LastModifiedDate SMALLDATETIME NULL,
-- )
Here is my main sproc with all the cursors:
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
ALTER
PROCEDURE [dbo].[reprortSproc]
AS
BEGIN
Declare
@customerid int
declare
@programId int
Declare
@Period varchar(50)
Declare
@SQLString varchar(300)
SET NOCOUNT ON;
truncate
table finalReportData
truncate
table reportData
DECLARE
someCursor INSENSITIVE CURSOR FOR
SELECT programId from tempPartnerReport
OPEN
someCursor
FETCH
NEXT FROM someCursor INTO @programID
While
(select fetch_status from sys.dm_exec_cursors(0)where name like 'someCursor')=0
BEGIN
DECLARE
secondCursor INSENSITIVE CURSOR FOR
SELECT period from tempPartnerReport
OPEN secondCursor
FETCH NEXT from secondCursor INTO @Period
While (select fetch_status from sys.dm_exec_cursors(0)where name like 'secondCursor')=0
BEGIN
DECLARE thirdCursor INSENSITIVE CURSOR FOR
SELECT customerID from Customer where programID = @programID
open thirdCursor
FETCH NEXT FROM thirdCursor into @CustomerID
While (select fetch_status from sys.dm_exec_cursors(0)where name like 'thirdCursor')=0
BEGIN
EXEC ScoresGetlinesTemp @customerID,@programId , @period ,NULL,NULL,0
where @customerID in (select customerID from customer where programid = @programId)
update reportData
set customerID = @customerId,
customerName
= (select customerName from customer where customerId = @customerId),
programId
= @programId,
ProgramName
= (select description from program where programId = @programId),
period
= @period
insert into finalReportData
select * from ReportData
truncate table ReportData
FETCH NEXT FROM thirdCursor into @CustomerID
END
CLOSE thirdCursor
DEALLOCATE thirdCursor
FETCH NEXT from secondCursor INTO @Period
END
DEALLOCATE
secondCursor
FETCH
NEXT FROM someCursor INTO @programID
END
DEALLOCATE
someCursor
END
The Inner sproc being called populates the report data table with a few rows. these are updated then with extra data , inserted into the final data table. the reportData table is truncated and the process begins again. if you need any more data just shout. thanks
-
it would seem your data table has to have customer, period, goal in it and you should just be able to join it.
select customername, programname, goalname,
customers c, programs p, goals g, programgoals x, data d, periods t
where c.program = p.program and p.program = x.program and x.goal = d.goal and d.date = t.date and t.period = x.period
group by c.customername,p.programname, g.goalname, p.period
that assumes a customer has one progam and a program has many goals, programgoals have targets by period, and customer datapoints are stored by customer, goal, and date.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply