Lesser evil, Cursor Vs Temp table.

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

Transact-SQL (2000)

Lesser evil, Cursor Vs Temp table.

Author

Topic

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-05-19 : 14:59:46

Hi,
One of the SPs of the load process, has a lot of cursors. The table has 120 million records and 5+ million records are appended each time. There are also cursors within cursors.
My question is that if I use temp table, is that going to be better than cursor?

Or any other general suggestions?

SQL2K is what we have. Currently this part alone takes 30 hours .

TIA

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-19 : 15:32:59

Are you importing from a text file?

If you are importing from a Text file, you want to BULK INSERT that data into a staging table, perform any cleaning on that data that you need, and then do an INSERT INTO...SELECT into your production table.
This should SMOKE a cursor setup (30 hours to maybe 2 hours or less).

If you post your code, we can help you convert that to a non-cursor solution.

<Yoda>Cursors, the path to the Dark side they are.</Yoda>

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-05-19 : 15:59:20

It is not coming from a flat file. It is coming from online database to build our warehouse.
I will post the code shortly.
Thanks for your time and help.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-19 : 16:37:17

To build my warehouse, I did the following:
1. BCP'd data out into flat files from my OLTP system
2. Moved the files to the warehosue system
3. BULK INSERT'd those files into a staging table
4. INSERT INTO...SELECT from those staging tables into the production warehouse.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-05-19 : 16:53:15

We are building the warehouse from OLTP.
Since it is not the same structure, there is alot of processing in building. So I don't know how it can be bulk insert.

Anyways, here goes...

Thanks for your patience.

CREATE PROCEDURE _sp_UpdateLoad_FACTTestQuestion
@UpdateDate datetime
AS

-----------------------------------------------------------------------------------------------------------------------------------------------------
-- DECLARE SOURCE VARIABLES
-----------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @intSessionID int
DECLARE @dteStartDate datetime
DECLARE @dteEndDate datetime
DECLARE @intCourseID int
DECLARE @intTraineeID int
DECLARE @intGradeTypeID int
DECLARE @intTestID int
DECLARE @intPassingPercentScore int
DECLARE @intRawScore int
DECLARE @intTotalQuestions int
DECLARE @intQuestionID int
DECLARE @intTrainingLocationID int
DECLARE @intInstructorID int
DECLARE @dteEnrollmentDate datetime
DECLARE @intGivenTestQuestionAnswerID int
DECLARE @bitCorrect bit
DECLARE @strTestType varchar(50)

DECLARE @intCurTraineeID int
DECLARE @intCurTestID int
DECLARE @intCurTestTakenAttempt int

--Key variables
DECLARE @intSessionStartDateKey int
DECLARE @intComponentKey int
DECLARE @intTraineeKey int
DECLARE @intGradeTypeKey int
DECLARE @intSessionEndDateKey int
DECLARE @intTestKey int
DECLARE @intQuestionKey int
DECLARE @intQuestionOptionKey int
DECLARE @intTrainingLocationKey int
DECLARE @intInstructorKey int
DECLARE @intTestCompletedTimeKey int
DECLARE @intTestTakenAttempt int

--Measure variables
DECLARE @intResponseCount int
DECLARE @intCorrectResponseCount smallint
DECLARE @intIncorrectResponseCount smallint
DECLARE @intResponseValue int
DECLARE @intFirstResponse int
DECLARE @intCorrectFirstResponseCount smallint
DECLARE @intIncorrectFirstResponseCount smallint
DECLARE @intTestAttemptCount smallint
DECLARE @intPassedCount smallint

-----------------------------------------------------------------------------------------------------------------------------------------------------
-- DECLARE PROGRAM VARIABLES -- FACT LOAD
-----------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @intInsertedCount int
DECLARE @intTotalCount int
DECLARE @intNullCount int
DECLARE @intFailedCount int
DECLARE @intReturnVal int
DECLARE @Testingcourses smallint
DECLARE @RecCount int
DECLARE @RecCountChk int
DECLARE @RecordsPerMinute float
DECLARE @DTSID uniqueidentifier
DECLARE @FatalErrorDesc varchar(500)
DECLARE @ProgramName varchar(50)
DECLARE @EventID int
DECLARE @Now datetime
DECLARE @Start datetime
DECLARE @StartMinute datetime
DECLARE @RepositoryName varchar(50)
DECLARE @strSQL Varchar(5000)
DECLARE @error int
DECLARE @errordesc varchar (1000)
DECLARE @FactTable varchar(128)
DECLARE @SourceTable varchar(128)
DECLARE @SourceTable2 varchar(128)
--DECLARE @SourceTable3 varchar(128)
DECLARE @SourceTable4 varchar(128)
DECLARE @SourceTable5 varchar(128)
DECLARE @SourceTable6 varchar(128)
DECLARE @SourceTable7 varchar(128)
DECLARE @FactKey varchar(128)
DECLARE @TableInErr varchar(128)
DECLARE @RecIDString varchar(50)
DECLARE @RecordSource varchar(3)
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- DECLARE PROGRAM VARIABLES -- SPECIFIC TO THIS LOAD
-----------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @strTraineeID varchar (10)
DECLARE @strFactTable varchar (30)
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- INITIALIZE VARIABLES
-----------------------------------------------------------------------------------------------------------------------------------------------------
SET @intInsertedCount = 0
SET @intTotalCount = 0
SET @intReturnVal=0
SET @intNullCount=0
SET @intFailedCount=0
SET @RecCount=0
SET @RecCountChk=0
SET @repositoryname = null
SET @FactTable='_TestQuestionFact'
SET @SourceTable ='dwTestCourse'
SET @SourceTable2='dwTest'
--SET @SourceTable3='dwTestTaken'
SET @SourceTable4='dwQuestionOptionLabel'
--SET @SourceTable5='dwTrainingRecord'
--SET @SourceTable6='dwTESTTAKENTRAININGRECORD'
SET @SourceTable7='dwTesttakenquestionanswer'
SET @ProgramName='sp_UpdateLoad_FACTTestQuestion'
SET @RecordSource='TMS'
PRINT 'STARTED PROCESSING: ' + @ProgramName + ' ' + convert(varchar,@Now)
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- INITIALIZE TABLES (Fact table and Error should not have any records for the initial load
-----------------------------------------------------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
--TRUNCATE TABLE testquestionfact (Left in in error from InitLoad procedure)
--DELETE FROM dwerr_testquestionfact
SET @Now=GETDATE()
SET CONCAT_NULL_YIELDS_NULL OFF
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- GET DTSID - THIS IS THE IDENTIFIER FOR THE CURRENT DTS LOAD PACKAGE
-----------------------------------------------------------------------------------------------------------------------------------------------------
EXEC sp_Util_GetDTSID @DTSID OUTPUT
IF @DTSID IS NULL
BEGIN
SET @FatalErrorDesc = 'FATAL ERROR: UNABLE TO DETERMINE DTSID.'
SET @RecIDString='N/A'
SET @TableInErr='N/A'
GOTO Return_Failed
END
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- AUDIT TRACKING: LOG START EVENT
-----------------------------------------------------------------------------------------------------------------------------------------------------
SET @Now=GETDATE()
EXEC @EventID = sp_Util_AdddwEventAudit @ProgramName, @Now, NULL, 'INITIALIZING', @DTSID, 'Initializing Program',0,0,0
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- GET REPOSITORY DATABASE NAME - THIS IS STORED IN THE DWDBCONFIG TABLE
-----------------------------------------------------------------------------------------------------------------------------------------------------
EXEC sp_Util_GetRepositoryname @RepositoryName OUTPUT
IF @RepositoryName IS NULL or @RepositoryName = ''
BEGIN
SET @FatalErrorDesc = 'FATAL ERROR: UNABLE TO DETERMINE REPOSITORY DATABASE NAME.'
SET @RecIDString='N/A'
SET @TableInErr='N/A'
GOTO Return_Failed
END
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- CHECK FOR FATAL ERROR. IF ANY FATAL ERRORS EXIST, STOP PROCESSING
-----------------------------------------------------------------------------------------------------------------------------------------------------
EXEC @intReturnVal=sp_Util_CheckdwFatalError @DTSID
IF @intReturnVal<>0
BEGIN
SET @FatalErrorDesc='FATAL ERROR: FATAL ERROR ENCOUNTERED IN dwFatalErrorTable'
SET @RecIDString='N/A'
SET @TableInErr='N/A'
GOTO Return_Failed
END
--Fix up parameter, use just date part since time values not stored in dimDate
set @UpdateDate = cast(floor(cast(@UpdateDate as float)) as datetime)
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- AUDIT TRACKING: UPDATE EVENT WITH SOURCERECORDCOUNT
-----------------------------------------------------------------------------------------------------------------------------------------------------
SET @strSQL='DECLARE util_cursor CURSOR STATIC FORWARD_ONLY FOR ' +
'SELECT COUNT(*) ' +
'FROM ' + @RepositoryName + '..' + @SourceTable7 + ' TTQA LEFT OUTER JOIN ' + @RepositoryName + '..dwTestTaken TT ON ' +
'TTQA.TestID=TT.TestID AND TTQA.TraineeID=TT.TraineeID AND TTQA.TestTakenAttempt=TT.TestTakenAttempt ' +
'WHERE TT.DateTestScored>=''' + convert(varchar,@UpdateDate) + ''' ' +
'AND TTQA.TraineeID NOT IN (Select TraineeID from GMSTCADDDATA..tblTestTrainee)'
EXEC (@strSQL)
OPEN util_cursor
SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
SET @FatalErrorDesc='FATAL ERROR: UNABLE TO OPEN SOURCE DATA (' + @RepositoryName + '..'+@SourceTable7 + ')'
SET @RecIDString = 'N/A'
SET @TableInErr=@SourceTable
GOTO RETURN_FAILED
END
FETCH NEXT FROM util_cursor INTO @intTotalCount
SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
SET @FatalErrorDesc='FATAL ERROR: UNABLE TO RETRIEVE SOURCE DATA (' + @RepositoryName + '..'+@SourceTable7 + ')'
SET @RecIDString = 'N/A'
SET @TableInErr=@SourceTable
GOTO RETURN_FAILED
END
close util_cursor
deallocate util_cursor
SET @now = getdate()
EXEC sp_Util_UpdatedwEventAudit @EventID, @ProgramName, @now , 'PROCESSING', @DTSID, 'Processing Update Load',@intTotalCount,0,0
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- DELETE TestQuestionFact Records FROM DATA WAREHOUSE WITH DATES>=@UpdateDate
-- The Update load will only update records that exist within a certain time frame.
-----------------------------------------------------------------------------------------------------------------------------------------------------
DELETE _TestQuestionFact
FROM _TestQuestionFact TQF, dimDate D
WHERE TQF.SessionStartDateKey=D.DateKey AND
D.Date>=@UpdateDate AND TQF.RecordSource='TMS'
PRINT ' Deleted ' + convert(varchar, @@ROWCOUNT) + ' Rows from Test Question Fact'
SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
SET @FatalErrorDesc='FATAL ERROR: Unable to delete records from test question fact'
SET @RecIDString='N/A'
SET @TableInErr='TestQuestionFact'
GOTO Return_Failed
END
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- BUILD SOURCE RECORD SET (CURSOR) TO HOLD SOURCE RECORDS
-- TO BE INSERTED INTO FACT TABLE
-----------------------------------------------------------------------------------------------------------------------------------------------------
SET @strSQL = 'DECLARE Source_Cursor CURSOR FOR ' +
'SELECT stt.datetestscored,'+
' stt.datetestscored,'+
' stt.testtakenattempt, '+
-- sTC.courseid,
' sTTQA.TraineeID, '+
' sTT.RawScore,'+
' sTT.TotalQuestions,'+
' sTE.PassingPercentScore,'+
' sTE.TestType,' +
' sTTQA.testID,'+
' sTTQA.QuestionID,'+
'0 traininglocation, '+ -- TrainingLocation not available
'0 instructor, '+ -- Instructor not available
' stt.datetestscored,'+
' sTTQA.QuestionOptionID_Given, '+
' sTTQA.Correct '+
'FROM ' + @RepositoryName + '..' + @SourceTable2 +' sTE '+
' inner JOIN ' + @RepositoryName + '..dwTestTaken sTT ON sTE.TestID = sTT.TestID '+
' inner JOIN ' + @RepositoryName + '..' + @SourceTable7 +' sTTQA ON sTT.TraineeID = sTTQA.TraineeID AND sTT.TestID = sTTQA.TestID AND sTT.TestTakenAttempt = sTTQA.TestTakenAttempt ' +
'WHERE stt.datetestscored >= ''' + convert(varchar, @UpdateDate) + ''' ' +
' AND sTT.TraineeID NOT IN (Select TraineeID from vwtblTestTrainee)' +
'ORDER BY sTT.TraineeID, sTT.testID, sTT.TestTakenAttempt'
EXEC (@strSQL)
OPEN Source_cursor
SET @Error = @@ERROR
IF (@Error <> 0) -- Checks for any error 0 is success
BEGIN
SET @FatalErrorDesc = 'FATAL ERROR: UNABLE TO OPEN SOURCE DATA (' + @RepositoryName + '..' + @SourceTable7 + ')'
SET @RecIDString='N/A'
SET @TableInErr=@SourceTable
GOTO Return_Failed
END
PRINT 'PROCESSING ' + @SourceTable7 + ' into ' + @FactTable + '...'

FETCH NEXT FROM Source_Cursor
INTO @dteStartDate,@dteEndDate, @intTestTakenAttempt, @intTraineeID,
@intRawScore, @intTotalQuestions, @intPassingPercentScore, @strTestType, @intTestID, @intQuestionID, @intTrainingLocationID, @intInstructorID,
@dteEnrollmentDate, @intGivenTestQuestionAnswerID, @bitCorrect

SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
SET @FatalErrorDesc = 'FATAL ERROR: UNABLE TO RETRIEVE SOURCE DATA (' + @RepositoryName + '..' + @SourceTable + ')'
SET @RecIDString='N/A'
SET @TableInErr=@SourceTable
GOTO Return_Failed
END

SET @intCurTraineeID = 0
SET @intCurTestID = 0
SET @intCurTestTakenAttempt = 0

---------------------------------------------------------------------------------------------------------------------------------------------------
-- SET AUDITING STRINGS SPECIFIC TO THIS LOAD
---------------------------------------------------------------------------------------------------------------------------------------------------
SET @strTraineeID = convert(varchar,@inttraineeID)
SET @strFactTable = convert(varchar,@FactTable)
---------------------------------------------------------------------------------------------------------------------------------------------------
-- LOOP THROUGH ALL SOURCE RECORDS INSERTING THEM INTO THE FACT TABLE
---------------------------------------------------------------------------------------------------------------------------------------------------
Begin Transaction --Put processing into a transaction, using automatic transaction mode causes excessive number of commits which increases disk activity

SET @Now = GetDate()
SET @Start = @Now
SET @StartMinute = @Now
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecCount=@RecCount+1
If 1440*Cast((GetDate() - @StartMinute) as float) > 1
BEGIN
SET @Now = GetDate()
SET @RecordsPerMinute = @RecCount- @RecCountChk
PRINT convert(varchar, @Now) + '> Processed ' + convert(varchar, @RecCount) + ' Rows, ' + convert(varchar, @RecordsPerMinute) + ' Rows per Minute.'
EXEC sp_Util_UpdatedwEventAudit @EventID, @ProgramName, @Now , 'PROCESSING', @DTSID, 'Processing Update Load',@intTotalCount,@intInsertedCount,0, @RecordsPerMinute
SET @StartMinute = @Now
SET @RecCountChk = @RecCount
Commit --Commit the transaction every minute to prevent buffer from getting too full.
Begin Transaction
END
----------------------------------------------------------------------------------------------------------------------------------------------------
-- DEFINE EMPTY FIELDS
----------------------------------------------------------------------------------------------------------------------------------------------------
IF @dteStartDate IS NULL SET @dteStartDate = '1-1-1900'
IF @dteEndDate IS NULL SET @dteEndDate = '12-1-9999'
-- IF @dteTestCompleted IS NULL SET @dteTestCompleted = '00:00:00'
IF @intTestTakenAttempt IS NULL SET @intTestTakenAttempt = 0
IF @intTraineeID IS NULL SET @intTraineeID = 0
IF @intRawScore IS NULL SET @intRawScore = 0
IF @strTestType = 'Demographics' SET @intRawScore = 0 --Score should be 0 since no answers are correct, repository computes this differently.
IF @intTotalQuestions IS NULL SET @intTotalQuestions = 0
IF @intPassingPercentScore IS NULL SET @intPassingPercentScore = 0
IF @intTestID IS NULL SET @intTestID = 0
IF @intQuestionID IS NULL SET @intQuestionID = 0
IF @intTrainingLocationID IS NULL SET @intTrainingLocationID = 0
IF @intInstructorID IS NULL SET @intInstructorID = 0
IF @dteEnrollmentDate IS NULL SET @dteEnrollmentDate = '1-1-1900'
IF @intGivenTestQuestionAnswerID IS NULL SET @intGivenTestQuestionAnswerID = 0
IF @bitCorrect IS NULL SET @bitCorrect = 0
----------------------------------------------------------------------------------------------------------------------------------------------------
-- SET KEY VALUES
----------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
-- SESSION START DATE KEY
--------------------------------------------------------------------
EXEC @intSessionStartDateKey =sp_Util_GetDateKey @dteStartDate, @DTSID
--------------------------------------------------------------------
-- SESSION END DATE KEY
--------------------------------------------------------------------
EXEC @intSessionEndDateKey=sp_Util_GetDateKey @dteEndDate, @DTSID
--------------------------------------------------------------------
-- TEST COMPLETED TIME
--------------------------------------------------------------------
--PRINT convert(varchar,@dteTestCompleted)+' '+ 'test time'
--EXEC @intTestCompletedTimeKey=sp_Util_GetTimeKey @dteTestCompleted, @DTSID

-- CHECK FOR MULTIPLE COURSES RELATED TO A SINGLE TEST i.e. (Placement)
-----------------------------------------------------------------------------------------------------------------------------------------------------
SET @strSQL = 'DECLARE course_cursor CURSOR STATIC FORWARD_ONLY FOR
SELECT courseID from ' + @RepositoryName + '..dwTestCourse ' +
'where ' + convert(varchar,@intTestID) + ' = testID'
EXEC (@strSQL)
OPEN course_cursor
SET @testingcourses = @@CURSOR_ROWS
SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
SET @FatalErrorDesc = 'FATAL ERROR: UNABLE TO OPEN SOURCE DATA (' + @RepositoryName + '..dwTestCourse)'
SET @RecIDString='N/A'
SET @TableInErr=@SourceTable
GOTO Return_Failed
END
FETCH NEXT FROM course_cursor INTO @intCourseID
SET @Error = @@ERROR
IF (@Error <> 0) BEGIN
SET @FatalErrorDesc = 'FATAL ERROR: UNABLE TO RETRIEVE SOURCE DATA (' + @RepositoryName + '..dwTestCourse)'
SET @RecIDString='N/A'
SET @TableInErr=@SourceTable
GOTO Return_Failed
END
CLOSE course_cursor
DEALLOCATE course_cursor

-- COMPONENT KEY
IF @testingcourses = 0 --No courses, a demographics or survey test
EXEC @intComponentKey=sp_Util_GetComponentKey 9999998, @DTSID
ELSE IF @testingcourses > 1 --Multiple courses, placement test
EXEC @intComponentKey=sp_Util_GetComponentKey 9999999, @DTSID
ELSE
EXEC @intComponentKey=sp_Util_GetComponentKey @intCourseID, @DTSID

-- TEST ATTEMPT COUNT, PASSED COUNT
-- Attempt count set to 1 for the first question on a test atemp1, 0 otherwise (see code below). Passed count similar but only 1 if passing grade.
SET @intTestAttemptCount = 0
SET @intPassedCount = 0

IF @intCurTraineeID <> @intTraineeID --Trainee changed
BEGIN
SET @intCurTestID = 0 --Start each trainee to invalid test to force test change
SET @intCurTraineeID = @intTraineeID
--Find Trainee Key, only need to do this when TraineeID changes
EXEC @intTraineeKey=sp_Util_GetTraineeKey @intTraineeID,@dteEnrollmentdate, @DTSID
END

IF @intCurTestID <> @intTestID
BEGIN
SET @intCurTestTakenAttempt = 0 --Start each test to invalid attempt to force attempt change
SET @intCurTestID = @intTestID
--Find Test key, only do this once per test
EXEC @intTestKey=sp_Util_GetTestKey @intTestID, @DTSID
END

IF @intCurTestTakenAttempt <> @intTestTakenAttempt
BEGIN
SET @intTestAttemptCount = 1 --Set = 1 for exactly one record per attempt
SET @intCurTestTakenAttempt = @intTestTakenAttempt
--Find Grade, only need to do this once per attempt
EXEC @intGradeTypeKey=sp_Util_GetGrade @intTestID, @intTraineeID, @intTestTakenAttempt, @DTSID
IF @intGradeTypeKey = 2 SET @intPassedCount = 1
END

--------------------------------------------------------------------
-- QUESTION KEY
--------------------------------------------------------------------
EXEC @intQuestionKey=sp_Util_GetQuestionKey @intQuestionID, 'TMS', @DTSID
--------------------------------------------------------------------
-- OPTION GIVEN KEY
--------------------------------------------------------------------
EXEC @intQuestionOptionKey=sp_Util_GetQuestionOptionKey @intGivenTestQuestionAnswerID, @intQuestionID, 'TMS', @DTSID
--------------------------------------------------------------------
-- TRAININGLOCATION KEY
--------------------------------------------------------------------
IF @intTrainingLocationID <> 0 --Allow for future inclusion of Training Location in repository, not included at the moment, should this dimension be dropped?
EXEC @intTrainingLocationKey=sp_Util_GetTrainingLocationKey @intTrainingLocationID, @DTSID
ELSE
SET @intTrainingLocationKey = 0
--------------------------------------------------------------------
-- INSTRUCTOR KEY
--------------------------------------------------------------------
IF @intInstructorID <> 0 --Allow for future inclusion of Instructor in repository, not included at the moment, should this dimension be dropped?
EXEC @intInstructorKey=sp_Util_GetInstructorKey @intInstructorID, @DTSID
ELSE
SET @intInstructorKey = 0
----------------------------------------------------------------------------------------------------------------------------------------------------
-- SET FACT VALUES
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
--SET RESPONSE COUNT
----------------------------------------------------------------------------------------------------------------------------------------------------
SET @intResponseCount = 1
----------------------------------------------------------------------------------------------------------------------------------------------------
-- SET CORRECT RESPONSE COUNT
----------------------------------------------------------------------------------------------------------------------------------------------------
SET @intCorrectResponseCount = @bitCorrect
----------------------------------------------------------------------------------------------------------------------------------------------------
-- SET INCORRECT RESPONSE COUNT
----------------------------------------------------------------------------------------------------------------------------------------------------
IF (@bitCorrect=1)
BEGIN
SET @intIncorrectResponseCount = 0
END
ELSE
BEGIN
SET @intIncorrectResponseCount=1
END
----------------------------------------------------------------------------------------------------------------------------------------------------
-- SET RESPONSE VALUE
----------------------------------------------------------------------------------------------------------------------------------------------------
SET @intResponseValue = @intGivenTestQuestionAnswerID
----------------------------------------------------------------------------------------------------------------------------------------------------
-- SET FIRST RESPONSE
----------------------------------------------------------------------------------------------------------------------------------------------------
SET @intFirstResponse = @intGivenTestQuestionAnswerID
----------------------------------------------------------------------------------------------------------------------------------------------------
-- SET CORRECT FIRST RESPONSE COUNT
----------------------------------------------------------------------------------------------------------------------------------------------------
SET @intCorrectFirstResponseCount = @bitCorrect
----------------------------------------------------------------------------------------------------------------------------------------------------
-- SET INCORRECT FIRST RESPONSE COUNT
----------------------------------------------------------------------------------------------------------------------------------------------------
IF (@bitCorrect=1)
BEGIN
SET @intIncorrectFirstResponseCount = 0
END
ELSE
BEGIN
SET @intIncorrectFirstResponseCount =1
END

IF @intTestTakenAttempt is NULL set @intTestTakenAttempt = 1
----------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT RECORD INTO FACT TABLE IF MINIMUM FACT RECORD REQUIREMENTS ARE MET
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
-- CHECK RECORD FOR MINIMUM FACT RECORD REQUIREMENTS
----------------------------------------------------------------------------------------------------------------------------------------------------
-- IF @intSessionStartDateKey =0 OR @intSessionEndDateKey =0 OR @intComponentKey =0 OR @intTraineeKey =0
IF @intComponentKey =0 OR @intTraineeKey =0
BEGIN
SET @errordesc='Fact Table Keys'
IF @inttraineekey=0 SET @errordesc=@errordesc+' | Trainee |'
IF @intcomponentkey=0 SET @errordesc=@errordesc+' | Component |' --Demographic tests will always generate an error record because there is no associated course.
-- IF @intsessionstartdatekey=0 SET @errordesc = @errordesc + ' | Session Start Date |'
-- IF @intsessionstartdatekey=0 SET @errordesc = @errordesc + ' | Session End Date |'
IF @errordesc is not null SET @errordesc='NULL:'+@errordesc
----------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT RECORD THAT FAILS MINIMUM REQUIREMENTS INTO ERROR TABLE
----------------------------------------------------------------------------------------------------------------------------------------------------
EXEC sp_Util_Add_dwERRTestQuestionFact @intSessionStartDatekey, @intComponentKey, @intTraineeKey, @intGradeTypeKey,
@intSessionEndDateKey, @intTestKey, @intQuestionKey, @intTrainingLocationKey,
@intInstructorKey, @intTestTakenAttempt, @intResponsecount, @intCorrectResponseCount, @intIncorrectResponseCount,
@intResponseValue, @intFirstResponse,@intCorrectFirstResponseCount, @intIncorrectFirstResponseCount,
@programname, @errordesc, @DTSID

SET @intFailedCount=@intFailedCount+1
SET @intNullCount = @intNullCount + 1
END
ELSE
BEGIN
----------------------------------------------------------------------------------------------------------------------------------------------------
-- INSERT RECORD THAT PASSES MINIMUM REQUIREMENTS INTO FACT TABLE
----------------------------------------------------------------------------------------------------------------------------------------------------
--(This INSERT statement is the main bottleneck in the code accounting for about 75% of the execution time)
INSERT INTO _TestQuestionFact (SessionStartDatekey, ComponentKey, TraineeKey, GradeTypeKey,
SessionEndDateKey, TestKey, QuestionKey, QuestionOptionKey,
TrainingLocationKey, InstructorKey, TestTakenAttempt, Responsecount,
CorrectResponseCount, IncorrectResponseCount,ResponseValue, FirstResponse,
CorrectFirstResponseCount, IncorrectFirstResponseCount, RecordSource, TotalQuestions, RawScore, TestAttemptCount, PassedCount)
VALUES (@intSessionStartDatekey, @intComponentKey, @intTraineeKey, @intGradeTypeKey,
@intSessionEndDateKey, @intTestKey, @intQuestionKey, @intQuestionOptionKey,
@intTrainingLocationKey, @intInstructorKey, @intTestTakenAttempt, @intResponsecount,
@intCorrectResponseCount, @intIncorrectResponseCount, @intResponseValue, @intFirstResponse,
@intCorrectFirstResponseCount, @intIncorrectFirstResponseCount, @RecordSource, @intTotalQuestions, @intRawScore, @intTestAttemptCount, @intPassedCount)

SET @error=@@ERROR
----------------------------------------------------------------------------------------------------------------------------------------------------
-- CHECK SUCCESS OF FACT RECORD INSERT
----------------------------------------------------------------------------------------------------------------------------------------------------
IF @Error=2627 --- 2627 is Duplicate Primary Key Error
BEGIN
SET @Now=GETDATE()
PRINT 'ERROR: INSERTING INTO TEST QUESTION FACT TABLE DUPLICATE RECORD FOUND'
SET @errordesc ='ERROR: INSERTING INTO TEST QUESTION FACT TABLE DUPLICATE RECORD FOUND'

EXEC sp_Util_AdddwError @errordesc, @Now, @ProgramName, @strFactTable, @strTraineeID, @DTSID

EXEC sp_Util_Add_dwERRTestQuestionFact @intSessionStartDatekey, @intComponentKey, @intTraineeKey, @intGradeTypeKey,
@intSessionEndDateKey, @intTestKey, @intQuestionKey, @intTrainingLocationKey,
@intInstructorKey, @intTestTakenAttempt, @intResponsecount, @intCorrectResponseCount, @intIncorrectResponseCount,
@intResponseValue, @intFirstResponse,@intCorrectFirstResponseCount, @intIncorrectFirstResponseCount,
@programname, @errordesc, @DTSID

SET @intFailedCount=@intFailedCount+1
END
IF @Error <> 0 AND @Error <>2627 -- Checks for any error 0 is success
BEGIN
SET @FatalErrorDesc='FATAL ERROR: UNABLE TO LOAD ' + @strFactTable +' RECORD'
PRINT @FatalErrorDesc
SET @Now=GETDATE()
EXEC sp_Util_Add_dwERRTestQuestionFact @intSessionStartDatekey, @intComponentKey, @intTraineeKey, @intGradeTypeKey,
@intSessionEndDateKey, @intTestKey, @intQuestionKey, @intTrainingLocationKey,
@intInstructorKey, @intTestTakenAttempt, @intResponsecount,
@intCorrectResponseCount, @intIncorrectResponseCount, @intResponseValue, @intFirstResponse,@intCorrectFirstResponseCount, @intIncorrectFirstResponseCount,
@programname, @fatalerrordesc, @DTSID
GOTO RETURN_FAILED
END

IF @Error=0
SET @intInsertedCount = @intInsertedCount+1
END
----------------------------------------------------------------------------------------------------------------------------------------------------
-- GET NEXT SOURCE RECORD FROM CURSOR
----------------------------------------------------------------------------------------------------------------------------------------------------
FETCH NEXT FROM Source_Cursor
INTO @dteStartDate,@dteEndDate, @intTestTakenAttempt, @intTraineeID, @intRawScore, @intTotalQuestions,
@intPassingPercentScore, @strTestType, @intTestID, @intQuestionID, @intTrainingLocationID, @intInstructorID,
@dteEnrollmentDate, @intGivenTestQuestionAnswerID, @bitCorrect

SET @Error = @@ERROR
IF (@Error <> 0)
BEGIN
SET @FatalErrorDesc = 'FATAL ERROR: UNABLE TO RETRIEVE SOURCE DATA (' + @RepositoryName + '..' + @SourceTable + ')'
SET @RecIDString='N/A'
SET @TableInErr=@SourceTable
GOTO Return_Failed
END
END
Commit

CLOSE source_cursor
DEALLOCATE source_cursor
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- AUDIT TRACKING: LOG COMPLETION
-----------------------------------------------------------------------------------------------------------------------------------------------------
SET @Now=GETDATE()
SET @RecordsPerMinute = @RecCount/(1440*Cast((@Now - @Start) as float) )
EXEC sp_Util_UpdatedwEventAudit @EventID, @ProgramName, @Now , 'COMPLETED', @DTSID, 'Completed Update Load',@intTotalCount,@intInsertedCount,0, @RecordsPerMinute
PRINT ' TOTAL ROWS PROCESSED: ' + convert(varchar, @intTotalCount)
PRINT ' TOTAL ROWS INSERTED: ' + convert(varchar, @intInsertedCount)
PRINT ' TOTAL NULL ERROR ROWS INSERTED: ' + convert(varchar, @intNullCount)
PRINT ' TOTAL ROWS FAILED: ' + convert(varchar, @intFailedCount)
PRINT 'COMPLETED SUCCESSFULLY: ' + @ProgramName + ' ' + convert(varchar,@Now)
SET NOCOUNT OFF
RETURN 0
-----------------------------------------------------------------------------------------------------------------------------------------------------
-- RETURN_FAILED:
-- AUDIT TRACKING: LOG FAILED COMPLETION
-----------------------------------------------------------------------------------------------------------------------------------------------------
RETURN_FAILED:
PRINT ' ' + @FatalErrorDesc
SET @Now=GETDATE()
IF CURSOR_STATUS('global', 'source_cursor')>=0
BEGIN
CLOSE source_cursor
DEALLOCATE source_cursor
END
IF CURSOR_STATUS('global', 'course_cursor')>=0
BEGIN
CLOSE course_cursor
DEALLOCATE course_cursor
END
IF CURSOR_STATUS('global', 'util_cursor')>=0
BEGIN
CLOSE util_cursor
DEALLOCATE util_cursor
END
EXEC sp_Util_AdddwFatalError @DTSID, @FatalErrorDesc, @ProgramName, @Now, @Error
EXEC sp_Util_AdddwError @FatalErrorDesc, @Now, @ProgramName, @TableInErr, @RecIDString, @DTSID
EXEC sp_Util_UpdatedwEventAudit @EventID, @ProgramName, @Now , 'FAILED', @DTSID, 'FAILED UPDATE LOAD',@intTotalCount,@intInsertedCount,0
PRINT 'FAILED PROCESSING: ' + @ProgramName + ' ' + convert(varchar,@Now)
SET NOCOUNT OFF
RETURN -1

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-05-20 : 10:06:29

Pls. help me with this giant. Any good suggestions also would do.

TIA

carrey
Starting Member

22 Posts

Posted - 2004-05-20 : 10:50:17

I don't have time to trawl through the code, but, first thought that springs to mind is to:

a) dump the cursors and move to set based
but
b) deal with the data in manageable sets.

To achieve b) will require some data analysis. I'm not clear on what your code is doing but I'll try and describe an example that may be analagous:

Say, for example, we were dealing with stock trades here for a bank. Now, these trades would each be boked under a Book and each trading desk may have many books. Now, for an average outfit, there will be a few hundred books, each with some trades every day. If you split the load of new trades across 200 books and there are 5 million trades, that gives an average of 25,000 trades per book. Obviously some books would have more than others so that's where the data analysis would come in.

By doing this you can reduce your transaction size dramatically. If the batch size is still too big then it could be sub-divided by instrument type (Equity, Warrant, Option, Future, Bond, Interest Rate Swap....) giving finer granularity. When you get to the optimum split, you'll find that the performance is massively improved and less prone to swamping the log or filling tempdb.

HTH

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-20 : 12:06:33

My OLTP and Warehouse databases are very different. I was able to BCP out my data to a text file, and then import it into staging tables that were spefically designed to allow for BULK INSERT from those BCP'd files. From the staging tables I then have to work a bit of magic to get those rows into my production warehouse.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SQLCode
Posting Yak Master

143 Posts

Posted - 2004-05-20 : 12:26:47

Thats what I am looking at also Michael, Like you said I will try to bcp into text and bulk insert.

Are you using stored procs? Is it possible for you to give a code example of how to do this, bulk insert?

The insert statement is taking most time in my case. ours is not an OLTP warehouse. It is OLAP for the most part.

I appreciate everyone's patience and all the great help and advice.

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-20 : 14:21:03

I'm going from an OLTP database to my OLAP Warehouse.


--This get's the data out for one table from my OLTP database
--p_DataMart_GetAccounts basically does a select * from that table
--For most things, you want to select just today's data

SELECT @DateKey = CAST(CONVERT(VARCHAR(8), @ExecutionDate, 112) AS INT)
-- Account
SET @FileName = REPLACE(@FilePath + 'Account_' + CONVERT(VARCHAR(8), @DateKey, 112) +'.txt','/','-')

SET @bcpCommand = 'bcp "EXEC ' + @DatabaseName + '..p_DataMart_GetAccounts " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -S ' + @ServerName + ' -U ' + @UserName + ' -P ' + @Password + ' -c'

--SELECT @bcpCommand
EXEC master..xp_cmdshell @bcpCommand



--This code is in another stored proc in my Warehouse database
--The TempAccount table is my purpose built staging table.

--Accounts
SELECT @SQL = 'BULK INSERT TempAccount FROM ''' + @FilePath + @AccountFileName + ''' WITH ( DATAFILETYPE = ''char'', FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'', TABLOCK )'

--select @SQL AS AccountBulkInsert
EXEC(@SQL)


--Move Accounts from Temp to Prod
--This is where you would do some cleaning / converting to make the
--data fit into the warehouse
INSERT INTO Account(AccountID, AccountName)
SELECT ta.AccountID, ta.AccountName
FROM TempAccount ta
WHERE NOT EXISTS(SELECT 1 FROM Account prod WHERE prod.AccountID = ta.AccountID)

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources