April 14, 2010 at 7:58 am
I have a stored procedure where I am converting old mainframe data that is in a staging table, and converting it over to the new structure. I tried using DTA but it contains temp tables and also I've heard that that isn't the best route to go anyway for tuning procs.
What are some other routes that I could take to try and improve the performance of my procedure? I have about 1.5 million records that I need to convert and right now my proc is running at about 24 hours, which will not be acceptable on Go-Live/Conversion weekend.
TIA
April 14, 2010 at 8:25 am
You could call the sproc manually from SSMS with the "Include Actual Execution Plan" which would give you a wealth of information as to how it actually ran. You could also trace it and be sure to include statement detail. With this you could get a lot of detail as well.
Basically you are looking for the sections of code that run a long time and that you can make changes for speed. Don't get sucked into small sections that run poorly but optimizing them will not get you much. Look for table scans and index scans. Table scans on small tables are usually not a problem. A table scan on a 1M row table = BAD, REALLY BAD.
I hope this helped.
CEWII
April 14, 2010 at 8:35 am
First thing first... find out where those 24 hours are spent.
Such a process should have two very different steps...
1) Mainframe to SQL Server staging table.
2) SQL Server staging to final table.
An ETL process like this one has no other choice than doing full table scans on main table, after all - all rows have to be moved from mainframe to staging then moved again to final table.
My approach will be to fine tune each step as a different, unrelated project.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 14, 2010 at 9:00 am
The conversion from the mainframe data (in a VSAM file) inserted into the SQL staging table only takes a few minutes. Then I add some indexes to my staging table, then start the conversion from the staging table to the final table. It's that last step that takes ~24 hours.
After trying Elliot's suggesting about displaying the actual execution plan, it appears as though the most time consuming/resource intensive portions of the proc are in "Clustered Index Update" statements.
One other thought, right now, I am processing this where I do a Select Top 1 blah Where (criteria here) and assign the values in my select statement to variables. There are some if statements that I have to do, but if they meet the criteria, then I do an insert into the final table, I then do another Select Top 1 blah...In this scenario, does it sound like it might be faster to use a cursor? (I am using a while loop).
Thanks again
April 14, 2010 at 9:07 am
gregory.anderson (4/14/2010)
After trying Elliot's suggesting about displaying the actual execution plan, it appears as though the most time consuming/resource intensive portions of the proc are in "Clustered Index Update" statements.
By the way, that "Clustered Index Update" statement is where I update the staging tables "ConversionStatus" field. The where clause is on the clustered index, so I would think it would be fast, or maybe that's not the one that is taking the longest...
April 14, 2010 at 9:59 am
gregory.anderson (4/14/2010)
...I am processing this where I do a Select Top 1 blah Where (criteria here) and assign the values in my select statement to variables. ...
Is it possible in any way that you're trying to process one row at a time (aka "c u r s o r" or "l o o p"? If so, please try to convert it into a set based solution.
April 14, 2010 at 10:19 am
lmu92 (4/14/2010)
gregory.anderson (4/14/2010)
...I am processing this where I do a Select Top 1 blah Where (criteria here) and assign the values in my select statement to variables. ...Is it possible in any way that you're trying to process one row at a time (aka "c u r s o r" or "l o o p"? If so, please try to convert it into a set based solution.
Yes, one row at a time, and each row can potentially have anywhere from 0 to 10 "extra" records to convert.
I actually only have 680,000 rows in the table, but after conversion it ends up being around 1.5 million records because that averages out to 2.x records per row in the staging table.
To give you a bad example, I have the primary record, then there can be anywhere from 0 to 10 "previous" records. And based on certain criteria, that determines whether any/some/all of the "previous" records get converted.
Can you give me a quick example of a set-based solution (I'll google at the same time) to see how that would be done, and if it will work with my data?
April 14, 2010 at 10:44 am
gregory.anderson (4/14/2010)
Can you give me a quick example of a set-based solution (I'll google at the same time) to see how that would be done, and if it will work with my data?
Not really. Edit: At least: not yet.
If you'd post table def of source and target table together with some sample data and expected result to show us the concept you're using together with a biref explanation of what you need to do I'm sure we can help you to get rid of the "evil thing" called RBAR. 😉
For details on how to provide ready to use sample data please read and follow the first link in my signature.
April 14, 2010 at 11:39 am
Ok, I'll try and post the sql stuff...this is going to be long
Staging Table
CREATE TABLE [dbo].[PWDPermitConversionData](
[PWDPermitConversionDataID] [int] IDENTITY(1,1) NOT NULL,
[PermitPrefix] [varchar](1) NOT NULL,
[PermitNumber] [varchar](6) NOT NULL,
[CustomerNumber] [int] NULL,
[SSN] [varchar](9) NOT NULL,
[DLN] [varchar](9) NULL,
[LastName] [varchar](20) NULL,
[FirstName] [varchar](15) NULL,
[MiddleName] [varchar](15) NULL,
[Suffix] [varchar](3) NULL,
[DOB] [varchar](8) NULL,
[Street] [varchar](24) NULL,
[City] [varchar](14) NULL,
[State] [varchar](2) NULL,
[Zip] [varchar](9) NULL,
[County] [varchar](2) NULL,
[Location] [varchar](3) NULL,
[HCExam] [varchar](3) NULL,
[HCStation] [varchar](2) NULL,
[IssueDate] [varchar](8) NULL,
[ExpirationDate] [varchar](8) NULL,
[Status] [varchar](2) NULL,
[PrevPermPref1] [varchar](1) NULL,
[PrevPermNum1] [varchar](6) NULL,
[PrevPermStatus1] [varchar](2) NULL,
[PrevPermReturn1] [varchar](1) NULL,
[PrevPermExpiration1] [varchar](8) NULL,
[PrevPermPref2] [varchar](1) NULL,
[PrevPermNum2] [varchar](6) NULL,
[PrevPermStatus2] [varchar](2) NULL,
[PrevPermReturn2] [varchar](1) NULL,
[PrevPermExpiration2] [varchar](8) NULL,
[PrevPermPref3] [varchar](1) NULL,
[PrevPermNum3] [varchar](6) NULL,
[PrevPermStatus3] [varchar](2) NULL,
[PrevPermReturn3] [varchar](1) NULL,
[PrevPermExpiration3] [varchar](8) NULL,
[PrevPermPref4] [varchar](1) NULL,
[PrevPermNum4] [varchar](6) NULL,
[PrevPermStatus4] [varchar](2) NULL,
[PrevPermReturn4] [varchar](1) NULL,
[PrevPermExpiration4] [varchar](8) NULL,
[PrevPermPref5] [varchar](1) NULL,
[PrevPermNum5] [varchar](6) NULL,
[PrevPermStatus5] [varchar](2) NULL,
[PrevPermReturn5] [varchar](1) NULL,
[PrevPermExpiration5] [varchar](8) NULL,
[PrevPermPref6] [varchar](1) NULL,
[PrevPermNum6] [varchar](6) NULL,
[PrevPermStatus6] [varchar](2) NULL,
[PrevPermReturn6] [varchar](1) NULL,
[PrevPermExpiration6] [varchar](8) NULL,
[PrevPermPref7] [varchar](1) NULL,
[PrevPermNum7] [varchar](6) NULL,
[PrevPermStatus7] [varchar](2) NULL,
[PrevPermReturn7] [varchar](1) NULL,
[PrevPermExpiration7] [varchar](8) NULL,
[PrevPermPref8] [varchar](1) NULL,
[PrevPermNum8] [varchar](6) NULL,
[PrevPermStatus8] [varchar](2) NULL,
[PrevPermReturn8] [varchar](1) NULL,
[PrevPermExpiration8] [varchar](8) NULL,
[PrevPermPref9] [varchar](1) NULL,
[PrevPermNum9] [varchar](6) NULL,
[PrevPermStatus9] [varchar](2) NULL,
[PrevPermReturn9] [varchar](1) NULL,
[PrevPermExpiration9] [varchar](8) NULL,
[PrevPermPref10] [varchar](1) NULL,
[PrevPermNum10] [varchar](6) NULL,
[PrevPermStatus10] [varchar](2) NULL,
[PrevPermReturn10] [varchar](1) NULL,
[PrevPermExpiration10] [varchar](8) NULL,
[InvalidDLNumber] [varchar](1) NULL,
[ConversionStatus] [int] NOT NULL,
[ConversionErrorMessage] [text] NULL,
[ConversionStatusPrev1] [int] NULL,
[ConversionErrorMessagePrev1] [text] NULL,
[ConversionStatusPrev2] [int] NULL,
[ConversionErrorMessagePrev2] [text] NULL,
[ConversionStatusPrev3] [int] NULL,
[ConversionErrorMessagePrev3] [text] NULL,
[ConversionStatusPrev4] [int] NULL,
[ConversionErrorMessagePrev4] [text] NULL,
[ConversionStatusPrev5] [int] NULL,
[ConversionErrorMessagePrev5] [text] NULL,
[ConversionStatusPrev6] [int] NULL,
[ConversionErrorMessagePrev6] [text] NULL,
[ConversionStatusPrev7] [int] NULL,
[ConversionErrorMessagePrev7] [text] NULL,
[ConversionStatusPrev8] [int] NULL,
[ConversionErrorMessagePrev8] [text] NULL,
[ConversionStatusPrev9] [int] NULL,
[ConversionErrorMessagePrev9] [text] NULL,
[ConversionStatusPrev10] [int] NULL,
[ConversionErrorMessagePrev10] [text] NULL
)
Final/Destination Table
CREATE TABLE [dbo].[PWDPermit](
[PWDPermitID] [int] IDENTITY(1,1) NOT NULL,
[PermitNo] [int] NOT NULL,
[CustNo] [int] NOT NULL,
[PWDPermitTypeID] [int] NOT NULL,
[PWDPermitStatusID] [int] NOT NULL,
[PWDPermitDeviceTypeID] [int] NOT NULL,
[Location] [varchar](3) NOT NULL,
[Examiner] [varchar](3) NOT NULL,
[Station] [varchar](2) NOT NULL,
[IssueDate] [datetime] NOT NULL,
[ExpirationDate] [datetime] NULL,
[InvalidDLNumber] [bit] NOT NULL,
[Returned] [bit] NOT NULL,
[CreatedBy] [varchar](8) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedBy] [varchar](8) NULL,
[UpdatedDate] [datetime] NULL
)
And the infamous stored procedure...
SelectTop 1
@ID = PWDPermitConversionDataID,
@PermitNumber = PermitNumber,
@CustomerNumber = CustomerNumber,
@PermitType = PermitPrefix,
@PermitStatus = [Status],
@Location = Location,
@Examiner = HCExam,
@Station = HCStation,
@IssueDate = IssueDate,
@ExpirationDate = ExpirationDate,
@InvalidDL = InvalidDLNumber
From IARTS..PWDPermitConversionData
Where CustomerNumber IS NOT NULL
And CustomerNumber > 0
And ConversionStatus = 0
AndPermitPrefix Not In ('P', 'H')
And[Status] <> '06'
And
(
(
(
CaseWhen ExpirationDate <> 'NONE' And ExpirationDate <> 'NOE' And ExpirationDate <> '' Then Convert(DateTime, ExpirationDate)
Else @MaxDate
End IS NULL
OrCaseWhen ExpirationDate <> 'NONE' And ExpirationDate <> 'NOE' And ExpirationDate <> '' Then Convert(DateTime, ExpirationDate)
Else @MaxDate
End > @DateMinus1Year
)
AndPermitPrefix <> 'M'
)
Or
(
PermitPrefix = 'M'
)
)
Set@ParentIssueDate = Convert(DateTime, @IssueDate)
--Print '----------------------------------------'
--Print 'ID = ' + Cast(@ID as varchar)
--Print 'PermitNumber = ' + Cast(@PermitNumber as varchar)
--Print 'CustomerNumber = ' + Cast(@CustomerNumber as varchar)
--Print 'PermitType = ' + Cast(@PermitType as varchar)
--Print 'PermitStatus = ' + Cast(@PermitStatus as varchar)
--Print 'Location = ' + Cast(@Location as varchar)
--Print 'Examiner = ' + Cast(@Examiner as varchar)
--Print 'Station = ' + Cast(@Station as varchar)
--Print 'IssueDate = ' + Cast(@IssueDate as varchar)
--Print 'ExpirationDate = ' + Cast(@ExpirationDate as varchar)
--Print 'InvalidDL = ' + Cast(@InvalidDL as varchar)
While(@ID IS NOT NULL And @ID > 0)
Begin
--------------------------------------------------------------------------------------------------------
-- Insert the primary record data inside a try/catch block
--------------------------------------------------------------------------------------------------------
Begin Try
Insert Into IARTS..PWDPermit
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
Values
(
CaseWhen @PermitNumber = ''Then Null
Else Cast(@PermitNumber As Int)
End,
@CustomerNumber,
CaseWhen @PermitType = 'D'Then @PermitType_Org
When @PermitType = 'M'Then @PermitType_Ind
When @PermitType = 'V'Then @PermitType_Temp
When @PermitType = 'S'Then @PermitType_Sticker
When @PermitType = ''Then Null
Else @PermitType -- This will make it fail for sure
End,
CaseWhen @PermitStatus = 'RP'Then @PermitStatus_RU
When @PermitStatus = ''Then Null
Else Cast(@PermitStatus As Int)
End,
CaseWhen @PermitType = 'S'Then @PermitDeviceType_Sticker
When @PermitType In ('D', 'M', 'V')Then @PermitDeviceType_Placard
When @PermitType = ''Then Null
Else @PermitDeviceType_Unknown
End,
CaseWhen @Location = '' Or @Location IS NULL Then '999'
Else @Location
End,
CaseWhen @Examiner = '' Or @Examiner IS NULL Then '999'
Else @Examiner
End,
CaseWhen @Station = '' Or @Station IS NULL Then '99'
Else @Station
End,
Cast(@IssueDate As DateTime),
CaseWhen @ExpirationDate = 'NONE' Or @ExpirationDate = '' Or @ExpirationDate = 'NOE'Then @MaxDate
Else Cast(@ExpirationDate As DateTime)
End,
CaseWhen @InvalidDL = 'Y'Then @True
Else @False
End,
@False,
@User,
@Today
)
Set@Error = 1
Set@ErrorMessage = Null
--Print 'Insert was successful'
--Print '----------------------------------------'
--Print ''
End Try
Begin Catch
Set@Error = Error_Number()
Set@ErrorMessage = Error_Message()
--Print 'Insert failed'
--Print '----------------------------------------'
--Print ''
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the primary record
--------------------------------------------------------------------------------------------------------
UpdateIARTS..PWDPermitConversionData
SetConversionStatus = @Error,
ConversionErrorMessage = Cast(@ErrorMessage As text)
WherePWDPermitConversionDataID = @ID
If(@Error > 1)
Begin
Insert Into IARTS..PWDPermitConversionErrors
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
Values
(
@ID,
0,
@Error,
@ErrorMessage
)
End
-- We only want to do previous permits that are for individuals
If(@PermitType = 'M')
Begin
--------------------------------------------------------------------------------------------------------
-- Try and import the "previous" permits
-- There can be 0 thru 10 possible examples here so let's use a dynamic query to accomplish this
--------------------------------------------------------------------------------------------------------
Declare@PreviousCounter Int,
@sql nvarchar(2000)
Set@PreviousCounter = 1
--------------------------------------------------------------------------------------------------------
-- Retrieve the data from the first "previous" permit and see if there are any records that need converted
--------------------------------------------------------------------------------------------------------
Set@sql ='
Insert Into IARTS..PreviousPermit
SelectPWDPermitConversionDataID,
CustomerNumber,
PrevPermPref' + Cast(@PreviousCounter as varchar) + ' As [PrevPermPref],
PrevPermNum' + Cast(@PreviousCounter as varchar) + ' As [PrevPermNum],
PrevPermStatus' + Cast(@PreviousCounter as varchar) + ' As [PrevPermStatus],
PrevPermReturn' + Cast(@PreviousCounter as varchar) + ' As [PrevPermReturn],
PrevPermExpiration' + Cast(@PreviousCounter as varchar) + ' As [PrevPermExpiration]
FromIARTS..PWDPermitConversionData
WherePWDPermitConversionDataID = ' + Cast(@ID as varchar) + '
AndPrevPermPref' + Cast(@PreviousCounter as varchar) + ' Not In (''P'', ''H'')
AndConversionStatusPrev' + Cast(@PreviousCounter as varchar) + ' IS NULL
'
Exec sp_executesql @sql
Select@ID = PWDPermitConversionDataID,
@CustomerNumber = CustomerNumber,
@PermitType = PrevPermPref,
@PermitNumber = PrevPermNum,
@PermitStatus = PrevPermStatus,
@Returned = PrevPermReturn,
@ExpirationDate = PrevPermExpiration
FromIARTS..PreviousPermit
Truncate Table IARTS..PreviousPermit
--Print '----------------------------------------'
--Print 'ID = ' + Cast(@ID as varchar)
--Print 'PermitNumber = ' + Cast(@PermitNumber as varchar)
--Print 'CustomerNumber = ' + Cast(@CustomerNumber as varchar)
--Print 'PermitType = ' + Cast(@PermitType as varchar)
--Print 'PermitStatus = ' + Cast(@PermitStatus as varchar)
--Print 'ExpirationDate = ' + Cast(@ExpirationDate as varchar)
--Print 'Returned = ' + Cast(@Returned as varchar)
While(@PreviousCounter <= 10 And @PermitNumber IS NOT NULL And LTRIM(RTRIM(@PermitNumber)) <> '')
Begin
--------------------------------------------------------------------------------------------------------
-- Insert the "previous" permit data into the table
--------------------------------------------------------------------------------------------------------
Begin Try
Insert Into IARTS..PWDPermit
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
Values
(
CaseWhen @PermitNumber = ''Then Null
Else Cast(@PermitNumber As Int)
End,
@CustomerNumber,
CaseWhen @PermitType = 'D'Then @PermitType_Org
When @PermitType = 'M'Then @PermitType_Ind
When @PermitType = 'V'Then @PermitType_Temp
When @PermitType = 'S'Then @PermitType_Sticker
When @PermitType = ''Then Null
Else @PermitType
End,
CaseWhen @PermitStatus = 'RP'Then @PermitStatus_RU
When @PermitStatus = ''Then Null
Else Cast(@PermitStatus As Int)
End,
CaseWhen @PermitType = 'S'Then @PermitDeviceType_Sticker
When @PermitType In ('D', 'M', 'V')Then @PermitDeviceType_Placard
When @PermitType = ''Then Null
Else @PermitDeviceType_Unknown
End,
'999', -- This is unknown
'999', -- This is unknown
'99', -- This is unknown
CaseWhen @ExpirationDate = 'NONE' Or @ExpirationDate = '' Or @ExpirationDate = 'NOE'Then @MinDate
When Convert(DateTime, @ExpirationDate) > @Today Then DateAdd(mm, -1, @ParentIssueDate)
Else Cast(@ExpirationDate As DateTime)
End, -- This is unknown
CaseWhen @ExpirationDate = 'NONE' Or @ExpirationDate = '' Or @ExpirationDate = 'NOE'Then @MaxDate
Else Cast(@ExpirationDate As DateTime)
End,
@False, -- This is unknown
CaseWhen @Returned = 'Y'Then @True
Else @False
End,
@User,
@Today
)
Set@Error = 1
Set@ErrorMessage = Null
--Print 'Insert was successful'
--Print '----------------------------------------'
--Print ''
End Try
Begin Catch
Set@Error = Error_Number()
Set@ErrorMessage = Error_Message()
Set@ErrorMessage = Replace(@ErrorMessage, '''', '''''')
--Print 'Insert failed'
--Print '----------------------------------------'
--Print ''
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the "previous" permit record
--------------------------------------------------------------------------------------------------------
If(@ErrorMessage IS Null Or LTRIM(RTRIM(@ErrorMessage)) = '')
Set@sql = '
UpdateIARTS..PWDPermitConversionData
SetConversionStatusPrev' + Cast(@PreviousCounter as varchar) + ' = ' + Cast(@Error as varchar) + '
WherePWDPermitConversionDataID = ' + Cast(@ID as varchar) + '
'
Else
Begin
Set@sql ='
UpdateIARTS..PWDPermitConversionData
SetConversionStatusPrev' + Cast(@PreviousCounter as varchar) + ' = ' + Cast(@Error as varchar) + ',
ConversionErrorMessagePrev' + Cast(@PreviousCounter as varchar) + ' = ''' + @ErrorMessage + '''
WherePWDPermitConversionDataID = ' + Cast(@ID as varchar) + '
'
End
Exec sp_executesql @sql
If(@Error > 1)
Begin
Insert Into IARTS..PWDPermitConversionErrors
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
Values
(
@ID,
@PreviousCounter,
@Error,
@ErrorMessage
)
End
--------------------------------------------------------------------------------------------------------
-- Retrieve the data from the next "previous" permit and see if there are any records that need converted
--------------------------------------------------------------------------------------------------------
Set@PreviousCounter = @PreviousCounter + 1
If(@PreviousCounter <= 10)
Begin
Set@sql ='
Insert Into IARTS..PreviousPermit
SelectPWDPermitConversionDataID,
CustomerNumber,
PrevPermPref' + Cast(@PreviousCounter as varchar) + ' As [PrevPermPref],
PrevPermNum' + Cast(@PreviousCounter as varchar) + ' As [PrevPermNum],
PrevPermStatus' + Cast(@PreviousCounter as varchar) + ' As [PrevPermStatus],
PrevPermReturn' + Cast(@PreviousCounter as varchar) + ' As [PrevPermReturn],
PrevPermExpiration' + Cast(@PreviousCounter as varchar) + ' As [PrevPermExpiration]
FromIARTS..PWDPermitConversionData
WherePWDPermitConversionDataID = ' + Cast(@ID as varchar) + '
AndPrevPermPref' + Cast(@PreviousCounter as varchar) + ' Not In (''P'', ''H'')
AndConversionStatusPrev' + Cast(@PreviousCounter as varchar) + ' IS NULL
'
Exec sp_executesql @sql
Select@ID = PWDPermitConversionDataID,
@CustomerNumber = CustomerNumber,
@PermitType = PrevPermPref,
@PermitNumber = PrevPermNum,
@PermitStatus = PrevPermStatus,
@Returned = PrevPermReturn,
@ExpirationDate = PrevPermExpiration
From IARTS..PreviousPermit
Truncate Table IARTS..PreviousPermit
--Print '----------------------------------------'
--Print 'ID = ' + Cast(@ID as varchar)
--Print 'PermitNumber = ' + Cast(@PermitNumber as varchar)
--Print 'CustomerNumber = ' + Cast(@CustomerNumber as varchar)
--Print 'PermitType = ' + Cast(@PermitType as varchar)
--Print 'PermitStatus = ' + Cast(@PermitStatus as varchar)
--Print 'ExpirationDate = ' + Cast(@ExpirationDate as varchar)
--Print 'Returned = ' + Cast(@Returned as varchar)
End
End
End
Set@ID = Null
--------------------------------------------------------------------------------------------------------
-- Grab the next record for conversion
--------------------------------------------------------------------------------------------------------
SelectTop 1
@ID = PWDPermitConversionDataID,
@PermitNumber = PermitNumber,
@CustomerNumber = CustomerNumber,
@PermitType = PermitPrefix,
@PermitStatus = [Status],
@Location = Location,
@Examiner = HCExam,
@Station = HCStation,
@IssueDate = IssueDate,
@ExpirationDate = ExpirationDate,
@InvalidDL = InvalidDLNumber
From IARTS..PWDPermitConversionData
Where CustomerNumber IS NOT NULL
And CustomerNumber > 0
And ConversionStatus = 0
AndPermitPrefix Not In ('P', 'H')
And[Status] <> '06'
And
(
(
(
CaseWhen ExpirationDate <> 'NONE' And ExpirationDate <> 'NOE' And ExpirationDate <> '' Then Convert(DateTime, ExpirationDate)
Else @MaxDate
End IS NULL
OrCaseWhen ExpirationDate <> 'NONE' And ExpirationDate <> 'NOE' And ExpirationDate <> '' Then Convert(DateTime, ExpirationDate)
Else @MaxDate
End > @DateMinus1Year
)
AndPermitPrefix <> 'M'
)
Or
(
PermitPrefix = 'M'
)
)
Set@ParentIssueDate = Convert(DateTime, @IssueDate)
--Print '----------------------------------------'
--Print 'ID = ' + Cast(@ID as varchar)
--Print 'PermitNumber = ' + Cast(@PermitNumber as varchar)
--Print 'CustomerNumber = ' + Cast(@CustomerNumber as varchar)
--Print 'PermitType = ' + Cast(@PermitType as varchar)
--Print 'PermitStatus = ' + Cast(@PermitStatus as varchar)
--Print 'Location = ' + Cast(@Location as varchar)
--Print 'Examiner = ' + Cast(@Examiner as varchar)
--Print 'Station = ' + Cast(@Station as varchar)
--Print 'IssueDate = ' + Cast(@IssueDate as varchar)
--Print 'ExpirationDate = ' + Cast(@ExpirationDate as varchar)
--Print 'InvalidDL = ' + Cast(@InvalidDL as varchar)
End
--------------------------------------------------------------------------------------------------------
-- The above section will hit any primary record that has been updated, but will not grab any "previous"
-- records that have been updated...let's handle those here
--------------------------------------------------------------------------------------------------------
DeclarepermitCursor Cursor Local Forward_Only For
SelectPWDPermitConversionDataID,
CaseWhen ConversionStatusPrev1 = 0Then 1
When ConversionStatusPrev2 = 0Then 2
When ConversionStatusPrev3 = 0Then 3
When ConversionStatusPrev4 = 0Then 4
When ConversionStatusPrev5 = 0Then 5
When ConversionStatusPrev6 = 0Then 6
When ConversionStatusPrev7 = 0Then 7
When ConversionStatusPrev8 = 0Then 8
When ConversionStatusPrev9 = 0Then 9
When ConversionStatusPrev10 = 0Then 10
End As [RecordIndicator]
FromIARTS..PWDPermitConversionData
WherePermitPrefix = 'M'
And
(
ConversionStatusPrev1 = 0
OrConversionStatusPrev2 = 0
OrConversionStatusPrev3 = 0
OrConversionStatusPrev4 = 0
OrConversionStatusPrev5 = 0
OrConversionStatusPrev6 = 0
OrConversionStatusPrev7 = 0
OrConversionStatusPrev8 = 0
OrConversionStatusPrev9 = 0
OrConversionStatusPrev10 = 0
)
Declare@previousID Int,
@RecordIndicator Int
OpenpermitCursor
Fetch Next From permitCursor Into @previousID, @RecordIndicator
While(@@Fetch_Status = 0)
Begin
Set@sql ='
Insert Into IARTS..PreviousPermit
SelectPWDPermitConversionDataID,
CustomerNumber,
PrevPermPref' + Cast(@RecordIndicator as varchar) + ' As [PrevPermPref],
PrevPermNum' + Cast(@RecordIndicator as varchar) + ' As [PrevPermNum],
PrevPermStatus' + Cast(@RecordIndicator as varchar) + ' As [PrevPermStatus],
PrevPermReturn' + Cast(@RecordIndicator as varchar) + ' As [PrevPermReturn],
PrevPermExpiration' + Cast(@RecordIndicator as varchar) + ' As [PrevPermExpiration]
FromIARTS..PWDPermitConversionData
WherePWDPermitConversionDataID = ' + Cast(@previousID as varchar) + '
AndPrevPermPref' + Cast(@RecordIndicator as varchar) + ' Not In (''P'', ''H'')
AndConversionStatusPrev' + Cast(@PreviousCounter as varchar) + ' IS NULL
'
Exec sp_executesql @sql
Select@ID = PWDPermitConversionDataID,
@CustomerNumber = CustomerNumber,
@PermitType = PrevPermPref,
@PermitNumber = PrevPermNum,
@PermitStatus = PrevPermStatus,
@Returned = PrevPermReturn,
@ExpirationDate = PrevPermExpiration
From IARTS..PreviousPermit
Truncate Table IARTS..PreviousPermit
--Print '----------------------------------------'
--Print 'ID = ' + Cast(@ID as varchar)
--Print 'PermitNumber = ' + Cast(@PermitNumber as varchar)
--Print 'CustomerNumber = ' + Cast(@CustomerNumber as varchar)
--Print 'PermitType = ' + Cast(@PermitType as varchar)
--Print 'PermitStatus = ' + Cast(@PermitStatus as varchar)
--Print 'ExpirationDate = ' + Cast(@ExpirationDate as varchar)
--Print 'Returned = ' + Cast(@Returned as varchar)
--------------------------------------------------------------------------------------------------------
-- Insert the "previous" permit data into the table
--------------------------------------------------------------------------------------------------------
Begin Try
Insert Into IARTS..PWDPermit
(
[PermitNo],
[CustNo],
[PWDPermitTypeID],
[PWDPermitStatusID],
[PWDPermitDeviceTypeID],
[Location],
[Examiner],
[Station],
[IssueDate],
[ExpirationDate],
[InvalidDLNumber],
[Returned],
[CreatedBy],
[CreatedDate]
)
Values
(
CaseWhen @PermitNumber = ''Then Null
Else Cast(@PermitNumber As Int)
End,
@CustomerNumber,
CaseWhen @PermitType = 'D'Then @PermitType_Org
When @PermitType = 'M'Then @PermitType_Ind
When @PermitType = 'V'Then @PermitType_Temp
When @PermitType = 'S'Then @PermitType_Sticker
When @PermitType = ''Then Null
Else @PermitType
End,
CaseWhen @PermitStatus = 'RP'Then @PermitStatus_RU
When @PermitStatus = ''Then Null
Else Cast(@PermitStatus As Int)
End,
CaseWhen @PermitType = 'S'Then @PermitDeviceType_Sticker
When @PermitType In ('D', 'M', 'V')Then @PermitDeviceType_Placard
When @PermitType = ''Then Null
Else @PermitDeviceType_Unknown
End,
'999', -- This is unknown
'999', -- This is unknown
'99', -- This is unknown
CaseWhen @ExpirationDate = 'NONE' Or @ExpirationDate = '' Or @ExpirationDate = 'NOE'Then @MinDate
When Convert(DateTime, @ExpirationDate) > @TodayThen DateAdd(yy, -1, @ExpirationDate)
Else Cast(@ExpirationDate As DateTime)
End, -- This is unknown
CaseWhen @ExpirationDate = 'NONE' Or @ExpirationDate = '' Or @ExpirationDate = 'NOE'Then Null
Else Cast(@ExpirationDate As DateTime)
End,
@False, -- This is unknown
CaseWhen @Returned = 'Y'Then @True
Else @False
End,
@User,
@Today
)
Set@Error = 1
Set@ErrorMessage = Null
--Print 'Insert was successful'
--Print '----------------------------------------'
--Print ''
End Try
Begin Catch
Set@Error = Error_Number()
Set@ErrorMessage = Error_Message()
Set@ErrorMessage = Replace(@ErrorMessage, '''', '''''')
--Print 'Insert failed'
--Print '----------------------------------------'
--Print ''
End Catch
--------------------------------------------------------------------------------------------------------
-- Update the import status for the "previous" permit record
--------------------------------------------------------------------------------------------------------
If(@ErrorMessage IS Null Or LTRIM(RTRIM(@ErrorMessage)) = '')
Set@sql = '
UpdateIARTS..PWDPermitConversionData
SetConversionStatusPrev' + Cast(@RecordIndicator as varchar) + ' = ' + Cast(@Error as varchar) + '
WherePWDPermitConversionDataID = ' + Cast(@previousID as varchar) + '
'
Else
Begin
Set@sql ='
UpdateIARTS..PWDPermitConversionData
SetConversionStatusPrev' + Cast(@RecordIndicator as varchar) + ' = ' + Cast(@Error as varchar) + ',
ConversionErrorMessagePrev' + Cast(@RecordIndicator as varchar) + ' = ''' + @ErrorMessage + '''
WherePWDPermitConversionDataID = ' + Cast(@previousID as varchar) + '
'
End
Exec sp_executesql @sql
If(@Error > 1)
Begin
Insert Into IARTS..PWDPermitConversionErrors
(
[PWDPermitConversionDataID],
[RecordIndicator],
[ErrorID],
[ErrorMessage]
)
Values
(
@ID,
@PreviousCounter,
@Error,
@ErrorMessage
)
End
Fetch Next From permitCursor Into @previousID, @RecordIndicator
End
April 14, 2010 at 11:54 am
gregory.anderson (4/14/2010)
After trying Elliot's suggesting about displaying the actual execution plan, it appears as though the most time consuming/resource intensive portions of the proc are in "Clustered Index Update" statements.
Are you updating staging table? if that's the case get rid of clustered index on staging table - each time you change a value on cluster index column SQL Server will reorder whole table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 14, 2010 at 11:57 am
PaulB-TheOneAndOnly (4/14/2010)
gregory.anderson (4/14/2010)
After trying Elliot's suggesting about displaying the actual execution plan, it appears as though the most time consuming/resource intensive portions of the proc are in "Clustered Index Update" statements.Are you updating staging table? if that's the case get rid of clustered index on staging table - each time you change a value on cluster index column SQL Server will reorder whole table.
Yes, so do I do any index on the primary key of that table then? Should I make it non-clustered and unique, or no index at all?
Based on the prmiary key, I'm updating the different conversion status fields based on a particular primary key value.
April 14, 2010 at 12:01 pm
gregory.anderson (4/14/2010)
PaulB-TheOneAndOnly (4/14/2010)
gregory.anderson (4/14/2010)
After trying Elliot's suggesting about displaying the actual execution plan, it appears as though the most time consuming/resource intensive portions of the proc are in "Clustered Index Update" statements.Are you updating staging table? if that's the case get rid of clustered index on staging table - each time you change a value on cluster index column SQL Server will reorder whole table.
Yes, so do I do any index on the primary key of that table then? Should I make it non-clustered and unique, or no index at all?
Based on the prmiary key, I'm updating the different conversion status fields based on a particular primary key value.
I would create on staging table ONLY the indexes I do need to massage data there. Any extra index will only be adding overhead.
I would not create a clustered index no matter what. You can always create unique (or non unique) index as non-clustered - as needed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 14, 2010 at 12:03 pm
Thanks, I'll try that.
April 14, 2010 at 12:04 pm
gregory.anderson (4/14/2010)
Thanks, I'll try that.
Please let us know how it goes.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 14, 2010 at 12:12 pm
gregory.anderson (4/14/2010)
Ok, I'll try and post the sql stuff...this is going to be long...
How about some (fake) sample dta to play with?
Viewing 15 posts - 1 through 15 (of 112 total)
You must be logged in to reply to this topic. Login to reply