April 14, 2010 at 12:18 pm
PaulB-TheOneAndOnly (4/14/2010)
gregory.anderson (4/14/2010)
Thanks, I'll try that.Please let us know how it goes.
Initial test, without gathering actual statistics, it's about half as fast. The actual execution plan shows the load on the Update IARTS..PWDPermitConversionData statement (where it was doing the Clustered Index Update), now it is doing a table Scan on the IARTS..PWDPermitConversionData table. Also, the other one is when doing the insert into the IARTS..PreviousPermit table that has the big where clause (this is the one I wasn't sure about replacing with a cursor instead of doing the while loop), it is also doing a table scan.
Also, when sending this to DTA, the first suggestion is a clustered index, which is what I just got rid of...
Do you want actual statistics and timings, or can you work off of this? I'll try creating a unique index on the primary key now...
April 14, 2010 at 12:22 pm
I just added the unique index on the primary key, and unless I was way off on my previous tests, this appears to be around 5 times as fast...
Edit: Ok, not that much better....I really need to gather real stats...
April 14, 2010 at 12:43 pm
Ok, I modified the stored proc to add the @Today = GetDate() right before it does each insert into PWDPermit so I could gather some real timings, right now, (still running with no index at all on the primary key), it appears that with a unique index on the primary key, it will do about 10 records per 1 second, right now, with no index at all on the primary key, it is doing about 4 records for every 1 second. I will add back in the clustered primary key just so I can get that timing again to have a better comparison, but it looks like a unique index is the way to go so far...
April 14, 2010 at 12:54 pm
gregory.anderson (4/14/2010)
Ok, I modified the stored proc to add the @Today = GetDate() right before it does each insert into PWDPermit so I could gather some real timings, right now, (still running with no index at all on the primary key), it appears that with a unique index on the primary key, it will do about 10 records per 1 second, right now, with no index at all on the primary key, it is doing about 4 records for every 1 second. I will add back in the clustered primary key just so I can get that timing again to have a better comparison, but it looks like a unique index is the way to go so far...
non-clustered index; if process is bound to change values on indexed columns you don't want to have a clustered index.
_____________________________________
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 1:01 pm
At a first glance it looks like it's not required to do it row by row...
Beside the sample data I already asked for please provide the beginning of the procedure (variable declaration and initial values).
Currently the code is not runnable.
April 14, 2010 at 1:10 pm
PaulB-TheOneAndOnly (4/14/2010)
gregory.anderson (4/14/2010)
Ok, I modified the stored proc to add the @Today = GetDate() right before it does each insert into PWDPermit so I could gather some real timings, right now, (still running with no index at all on the primary key), it appears that with a unique index on the primary key, it will do about 10 records per 1 second, right now, with no index at all on the primary key, it is doing about 4 records for every 1 second. I will add back in the clustered primary key just so I can get that timing again to have a better comparison, but it looks like a unique index is the way to go so far...non-clustered index; if process is bound to change values on indexed columns you don't want to have a clustered index.
And these stats prove your point...
--With unique index on primary key, 6706 permits created in 582 seconds, which equals 11.5/sec
--With no index on the primary key, 2520 permits created in 645 seconds, which equals 3.9/sec
--With a clustered index on the primary key, 3479 permits created in 635 seconds, which equals 5.5/sec
April 14, 2010 at 1:11 pm
lmu92 (4/14/2010)
At a first glance it looks like it's not required to do it row by row...Beside the sample data I already asked for please provide the beginning of the procedure (variable declaration and initial values).
Currently the code is not runnable.
Can you give me an email address or something so I can send you some sample data, not sure how to post it in a thread when there is so much (even 10 rows would be very time-consuming).
April 14, 2010 at 1:37 pm
gregory.anderson (4/14/2010)
And these stats prove your point...--With unique index on primary key, 6706 permits created in 582 seconds, which equals 11.5/sec
--With no index on the primary key, 2520 permits created in 645 seconds, which equals 3.9/sec
--With a clustered index on the primary key, 3479 permits created in 635 seconds, which equals 5.5/sec
Glad to help.
One down perhaps a couple more to go. Please check where is time being spend now that f(&%-clustered issue is out of the picture 🙂
_____________________________________
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 1:46 pm
Ok,
here's what I found so far:
Data type mismatch: [PermitNo] [int] vs. [PermitNumber] [varchar](6)
Data type for @PermitType_Org, @PermitType_Ind, @PermitType_Temp, @PermitType_Sticker?
Any constraint on [Status] [varchar](2) to ensure 'RP','' or numeric values?
This is the general layout of how I would do it:
Block 1 (Insert the primary record data):
1) Before populating table [PWDPermitConversionData], add a clustered index on [PWDPermitConversionDataID]
2) populate table [PWDPermitConversionData]
3) perform a consistency check
[PermitNumber] LIKE '[^0-9]' --select non-numeric values
OR [PermitPrefix] NOT IN ('D','M','V','S','','P', 'H') -- invalid prefix
OR ([Status] not in('RP','') and [Status] LIKE '[^0-9]') -- invalid status
OR ExpirationDate NOT IN ('NONE','','NOE') AND ISDATE(ExpirationDate) =0 -- invalid ExpirationDate
Insert [PWDPermitConversionDataID] for those rows along with the first error code into a temp table (note: the current TRY ... CATCH block would work the same way: only show the first error)
4) Perform a set based insert (TRY .. CATCH block):
code follows as soon as all variables are known
5) update [PWDPermitConversionData] set [ConversionStatus] = 1, [ConversionErrorMessage]=NULL
WHERE ... AND NOT EXISTS (SELECT 1 FROM Bad_data)
This will take care of the about 200 lines of your current code.
Block 2 (PermitType = 'M'):
Insert Into IARTS..PWDPermit using CASE statement and CROSS APPLY function to run it max 10 times 10
This will eliminate the usage of IARTS..PreviousPermit and replaces your 160 lines loop
"While (@PreviousCounter <= 10 And @PermitNumber IS NOT NULL And LTRIM(RTRIM(@PermitNumber)) <> '')"
Block 3 (c.u.r.s.o.r. -- my fingers simply won't type that word)
Currently under investigation...
Would be great to have some sample data to play with...
Regarding my e-mail address: It is: Never.Ever.post.email.address@in.public.de 😀
The easiest way would be to add it as an attachment (use the Post Options section below the window where you type your replies in). Best would be a txt file that contains some INSERT INTO ... SELECT ... UNION ALL statements.
And, like I mentioned before: make sure the data are fake.
Also, it would be great if you could add the missing variable declaration as well as the initial values.
April 14, 2010 at 1:48 pm
gregory.anderson (4/14/2010)
PaulB-TheOneAndOnly (4/14/2010)
gregory.anderson (4/14/2010)
Ok, I modified the stored proc to add the @Today = GetDate() right before it does each insert into PWDPermit so I could gather some real timings, right now, (still running with no index at all on the primary key), it appears that with a unique index on the primary key, it will do about 10 records per 1 second, right now, with no index at all on the primary key, it is doing about 4 records for every 1 second. I will add back in the clustered primary key just so I can get that timing again to have a better comparison, but it looks like a unique index is the way to go so far...non-clustered index; if process is bound to change values on indexed columns you don't want to have a clustered index.
And these stats prove your point...
--With unique index on primary key, 6706 permits created in 582 seconds, which equals 11.5/sec
--With no index on the primary key, 2520 permits created in 645 seconds, which equals 3.9/sec
--With a clustered index on the primary key, 3479 permits created in 635 seconds, which equals 5.5/sec
Let's see how this works:
342475M6159481437877999999999888xx8888blahblahblahb19270914704 Chatterbox DROmahaCO12345641919220031216NONE10N0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342476M6159493306656999999999888xx8888blahblahblahb19180617704 Chatterbox DROmahaCO12345641919220031217NONE10Y0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342477M6159502609984999999999888xx8888blahblahblahb19430131704 Chatterbox DROmahaCO12345641919220031218NONE10Y0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342478M615951762287999999999888xx8888blahblahblahb19430921704 Chatterbox DROmahaCO12345641919120031021NONE10N0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342479M615952762287999999999888xx8888blahblahblahb19430921704 Chatterbox DROmahaCO12345641919120031021NONE10N0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342480M6159535682501999999999888xx8888blahblahblahb19180804704 Chatterbox DROmahaCO12345791919120031029NONE6M3631884YNONEM3631894YNONEN0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342481M6159545682501999999999888xx8888blahblahblahb19180804704 Chatterbox DROmahaCO12345791919120031029NONE10M3631884YNONEM3631894YNONEN0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342482M6159554079132999999999888xx8888blahblahblahb19260307704 Chatterbox DROmahaCO12345641919120031030NONE10Y0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342483M6159564079132999999999888xx8888blahblahblahb19260307704 Chatterbox DROmahaCO12345641919120031030NONE10Y0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342484M6159573117461999999999888xx8888blahblahblahb19311003704 Chatterbox DROmahaCO12345641919120031105NONE10Y0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342485M6159585682502999999999888xx8888blahblahblahb19121023704 Chatterbox DROmahaCO12345641919120031106NONE8Y0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342486M6159601058564999999999888xx8888blahblahblahb19180929704 Chatterbox DROmahaCO12345861919120031107NONE10M3718541NNONEM3718551NNONEV65507NNONEV65497N19980208N0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342487M6159611058564999999999888xx8888blahblahblahb19180929704 Chatterbox DROmahaCO12345861919120031107NONE10M3718541NNONEM3718551NNONEV65507NNONEV65497N19980208N0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
342488M6159621944069999999999888xx8888blahblahblahb19200621704 Chatterbox DROmahaCO12345641919120031107NONE10Y0NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
Don't remember how much I pasted before, but this is everything up until the first "Select Top 1" statement
USE [IARTS]
GO
Set NoCount On
Declare@True Bit,
@False Bit,
@User varchar(8),
@Today DateTime,
@PermitType_Org Int,
@PermitType_Ind Int,
@PermitType_Temp Int,
@PermitType_Sticker Int,
@PermitStatus_RU Int,
@PermitDeviceType_Placard Int,
@PermitDeviceType_Sticker Int,
@PermitDeviceType_Unknown Int,
@MinDate DateTime,
@MaxDate DateTime,
@DateMinus1Year DateTime,
@ParentIssueDate DateTime,
@Error Int,
@ErrorMessage varchar(2000)
Set@True = 0x1
Set@False = 0x0
Set@User = 'PWDConv'
Set@Today = GetDate()
Select@PermitType_Org = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
Select@PermitType_Ind = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
Select@PermitType_Temp = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
Select@PermitType_Sticker = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
Select@PermitStatus_RU = PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
Select@PermitDeviceType_Placard = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
Select@PermitDeviceType_Sticker = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
Select@PermitDeviceType_Unknown = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
Set@MinDate = Convert(DateTime, '1901-01-01')
Set@MaxDate = Convert(DateTime, '2100-01-01')
Set@DateMinus1Year = DateAdd(yy, -1, @Today)
Set@ErrorMessage = Null
Declare@ID int,
@PermitNumber varchar(10),
@CustomerNumber int,
@PermitType varchar(1),
@PermitStatus varchar(2),
@Location varchar(3),
@Examiner varchar(3),
@Station varchar(3),
@IssueDate varchar(8),
@ExpirationDate varchar(8),
@InvalidDL varchar(1),
@Returned varchar(1)
--------------------------------------------------------------------------------------------------------
-- Create a real table instead of using a #temp table
--------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PreviousPermit]') AND type in (N'U'))
DROP TABLE [dbo].[PreviousPermit]
Create Table IARTS.dbo.PreviousPermit
(
PWDPermitConversionDataID Int,
CustomerNumber Int,
PrevPermPref Varchar(1),
PrevPermNum Varchar(8),
PrevPermStatus Varchar(2),
PrevPermReturn Varchar(1),
PrevPermExpiration Varchar(8)
)
April 14, 2010 at 1:59 pm
Data type mismatch: [PermitNo] [int] vs. [PermitNumber] [varchar](6)
I did this because the previous conversion to get to the mainframe/VSAM version caused some bad data to be created. There are records that exist that have a PermitNumber = '8N1234', so I wanted that to fail in the Begin try so I can write it to my conversion errors table where the users will update the data and attempt to convert again.
Data type for @PermitType_Org, @PermitType_Ind, @PermitType_Temp, @PermitType_Sticker?
I just pasted the stored proc code that I originally left out...
Any constraint on [Status] [varchar](2) to ensure 'RP','' or numeric values?
Not fully understanding what you want here, but there can be ANY kind of data in the staging table, if it doesn't meet the rules in the destination table, it will error and go to the conversion errors table.
I'm still reading through the rest of your suggestion about the set-based proc...
April 14, 2010 at 2:17 pm
gregory.anderson (4/14/2010)
Let's see how this works:
bad sample data
Don't remember how much I pasted before, but this is everything up until the first "Select Top 1" statement
Well, this way of posting sample data is not really helpful...
I'd have to spend quite some time to transform it into ready to use sample data. Would you create those data in a ready to use format, please?
I also had to add the following variables to make the code runable:
@Returned char(1),
@InvalidDL char(1),
@ExpirationDate char(8),
@IssueDate char(8),
@Station char(2),
@Examiner char(3),
@Location char(3),
@PermitStatus char(2),
@PermitType char(1),
@CustomerNumber INT,
@PermitNumber varchar(6),
@ID INT,
Please provide intial values.
April 14, 2010 at 2:24 pm
lmu92 (4/14/2010)
gregory.anderson (4/14/2010)
Let's see how this works:
bad sample data
Don't remember how much I pasted before, but this is everything up until the first "Select Top 1" statement
Well, this way of posting sample data is not really helpful...
I'd have to spend quite some time to transform it into ready to use sample data. Would you create those data in a ready to use format, please?
I also had to add the following variables to make the code runable:
@Returned char(1),
@InvalidDL char(1),
@ExpirationDate char(8),
@IssueDate char(8),
@Station char(2),
@Examiner char(3),
@Location char(3),
@PermitStatus char(2),
@PermitType char(1),
@CustomerNumber INT,
@PermitNumber varchar(6),
@ID INT,
Please provide intial values.
USE [IARTS]
GO
Set NoCount On
Declare@True Bit,
@False Bit,
@User varchar(8),
@Today DateTime,
@PermitType_Org Int,
@PermitType_Ind Int,
@PermitType_Temp Int,
@PermitType_Sticker Int,
@PermitStatus_RU Int,
@PermitDeviceType_Placard Int,
@PermitDeviceType_Sticker Int,
@PermitDeviceType_Unknown Int,
@MinDate DateTime,
@MaxDate DateTime,
@DateMinus1Year DateTime,
@ParentIssueDate DateTime,
@Error Int,
@ErrorMessage varchar(2000)
Set@True = 0x1
Set@False = 0x0
Set@User = 'PWDConv'
Set@Today = GetDate()
Select@PermitType_Org = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Organization'
Select@PermitType_Ind = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Permanent'
Select@PermitType_Temp = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Temporary'
Select@PermitType_Sticker = PWDPermitTypeID From IARTS..PWDPermitType Where LookupValue = 'Sticker'
Select@PermitStatus_RU = PWDPermitStatusID From IARTS..PWDPermitStatus Where LookupValue = 'RtrndUnDel'
Select@PermitDeviceType_Placard = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Placard'
Select@PermitDeviceType_Sticker = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Sticker'
Select@PermitDeviceType_Unknown = PWDPermitDeviceTypeID From IARTS..PWDPermitDeviceType Where LookupValue = 'Unknown'
Set@MinDate = Convert(DateTime, '1901-01-01')
Set@MaxDate = Convert(DateTime, '2100-01-01')
Set@DateMinus1Year = DateAdd(yy, -1, @Today)
Set@ErrorMessage = Null
Declare@ID int,
@PermitNumber varchar(10),
@CustomerNumber int,
@PermitType varchar(1),
@PermitStatus varchar(2),
@Location varchar(3),
@Examiner varchar(3),
@Station varchar(3),
@IssueDate varchar(8),
@ExpirationDate varchar(8),
@InvalidDL varchar(1),
@Returned varchar(1)
--------------------------------------------------------------------------------------------------------
-- Create a real table instead of using a #temp table
--------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PreviousPermit]') AND type in (N'U'))
DROP TABLE [dbo].[PreviousPermit]
Create Table IARTS.dbo.PreviousPermit
(
PWDPermitConversionDataID Int,
CustomerNumber Int,
PrevPermPref Varchar(1),
PrevPermNum Varchar(8),
PrevPermStatus Varchar(2),
PrevPermReturn Varchar(1),
PrevPermExpiration Varchar(8)
)
--------------------------------------------------------------------------------------------------------
-- Load the very first record before entering the while loop
--------------------------------------------------------------------------------------------------------
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
Set@Today = GetDate()
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
Set@Today = GetDate()
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
Set@Today = GetDate()
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
Drop Table IARTS..PreviousPermit
Does excel/csv format work?
April 14, 2010 at 2:28 pm
gregory.anderson (4/14/2010)
Data type mismatch: [PermitNo] [int] vs. [PermitNumber] [varchar](6)
I did this because the previous conversion to get to the mainframe/VSAM version caused some bad data to be created. There are records that exist that have a PermitNumber = '8N1234', so I wanted that to fail in the Begin try so I can write it to my conversion errors table where the users will update the data and attempt to convert again.
Any constraint on [Status] [varchar](2) to ensure 'RP','' or numeric values?
Not fully understanding what you want here, but there can be ANY kind of data in the staging table, if it doesn't meet the rules in the destination table, it will error and go to the conversion errors table.
I'm still reading through the rest of your suggestion about the set-based proc...
Your answers do make sense. So we have to for valid data inside the proc. I was hoping to avoid that... We'll take care of it in block 1, step 3 ("perform a consistency check") not that big of a deal though.
A few more questions:
Do we have to deal with receiving new data into the staging table while processing? (If so, we'd need to consider it).
Is it possible for you to change the data type of your tables? I'm talking especially abaout changing VARCHAR() to CHAR(). This would reduce the table size. Example: to store VARCHAR(1) data you'd need 3 bytes (actual length of data + 2 bytes) whereas a CHAR(1) would only require 1 byte. If you have 1 mill rows, we're talking about 2MB just for this one column...
April 14, 2010 at 2:55 pm
Viewing 15 posts - 16 through 30 (of 112 total)
You must be logged in to reply to this topic. Login to reply