June 8, 2010 at 10:09 am
It's 2am here and I've just finished reading this thread from start to finish. I've been dealing with a stored proc that does much the same as Greg's over the last week, though with a different problem domain (financial product).
My (inherited) 3000+ line stored procedure does the following:
Dataload from various excel and csv file formats. Builds some dynamic sql to parse rows into the appropriate columns of 2 temp tables. Runs a bunch of data validations flagging errors in the temp table with status codes. Runs (several) cursors over the 2 temp tables, inserting and updating various interrelated tables (calling a 300 line sproc to do so, triggering triggers as well).
The process is run daily by many clients of the business, and deals with files around 20000 records in about 12 minutes. (Inserts into several tables, the largest of which has 12M rows and a bunch of poor indexing choices).
This thread has given me a whole lot of optimism that I can turn around this process. My background is as a .NET dev, not a DBA so alot of this is fairly new to me, but I found it really easy to understand the process outlined in this thread. Thanks to all each of you that contributed. You guys rock.
February 18, 2011 at 9:33 am
Hey guys, I'm back! You all did such a wonderful job helping me with this one, that I have a new one that I'm working on and am close to getting, but not quite complete yet. If you don't mind helping me again, that would be great!!!
Here's the scenario, I have 2 tables that I'm worried about. Table A contains my "License", and Table B contains my "LicenseRestrictions". There may be multiple LicenseRestrictions per License. In addition to that, for each LicenseRestriction, I need to take my 1 comment field and break that out into 6 chunks (fields) of varchar(30) fields.
Of course, my original version of this stored procedure (which I wrote 4 years ago) contained 2 cursors, nested together, I have converted it over to a pivot table-type query, and it works great, except for when there are the multiple restrictions per license.
Here's the old version:
If Exists(Select name From Reporting..sysobjects Where name like 'NonCoded')
drop table [NonCoded]
Create Table dbo.[NonCoded]
(
[CustomerNumber] int Not Null,
[NCR] varchar(30) Not Null
)
Declare @CustomerNumber int
Declare @LicenseRestrictionID int
Declare @Length int
--------------------------------------------------------------------------------------------------------------
--Create the Cursor to hold all CustomerNumbers
--------------------------------------------------------------------------------------------------------------
Declare cur Cursor Local For
Selectcc.CustomerNumber
From IADS..LicenseRestriction lr
Inner Join IADS..License l on l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
Group By cc.CustomerNumber
Open cur
Fetch Next From cur Into @CustomerNumber
While (@@Fetch_Status = 0)
Begin
--------------------------------------------------------------------------------------------------------------
--Create the Cursor to hold all the LicenseRestrictionIDs for each CustomerNumber
--------------------------------------------------------------------------------------------------------------
Declare curLR Cursor Local For
Selectlr.LicenseRestrictionID
From IADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
Andcc.CustomerNumber = @CustomerNumber
Open curLR
Fetch Next From curLR Into @LicenseRestrictionID
While (@@Fetch_Status = 0)
Begin
Select @Length = Len(lr.Comment) From IADS..LicenseRestriction lr Where lr.LicenseRestrictionID = @LicenseRestrictionID
If @Length < 31
--First one gets added to the table--
Begin
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), lr.Comment)
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
End
If @Length > 30 And @Length < 61
--First one gets added to the table--
Begin
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), lr.Comment)
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Second one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 31, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
End
If @Length > 60 And @Length < 91
--First one gets added to the table--
Begin
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), lr.Comment)
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Second one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 31, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Third one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 61, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
End
If @Length > 90 And @Length < 121
--First one gets added to the table--
Begin
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), lr.Comment)
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Second one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 31, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Third one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 61, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Fourth one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 91, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
End
If @Length > 120 And @Length < 151
--First one gets added to the table--
Begin
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), lr.Comment)
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Second one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 31, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Third one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 61, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Fourth one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 91, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Fifth one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 121, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
End
If @Length > 150
--First one gets added to the table--
Begin
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), lr.Comment)
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Second one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 31, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Third one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 61, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Fourth one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 91, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Fifth one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 121, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
--Sixth one gets added to the table--
Insert Into dbo.NonCoded
Select[CustomerNumber] = @CustomerNumber,
[NCR] = Convert(varchar(30), Substring(lr.Comment, 151, 30))
FromIADS..LicenseRestriction lr
Wherelr.LicenseRestrictionID = @LicenseRestrictionID
End
Fetch Next From curLR Into @LicenseRestrictionID
End --Ends the curLR cursor
Close curLR
Deallocate curLR
Fetch Next From cur Into @CustomerNumber
End --Ends the cur cursor
Close cur
Deallocate cur
Now here's my converted version:
Declare@Today DateTime
Set@Today = GetDate()
Selecta.CustomerNumber,
a.CustomerCredentialID,
a.LicenseRestrictionID,
Max(Case When a.NCR_ID = 0 Then a.NCR Else Null End) As [NCR1],
Max(Case When a.NCR_ID = 1 Then a.NCR Else Null End) As [NCR2],
Max(Case When a.NCR_ID = 2 Then a.NCR Else Null End) As [NCR3],
Max(Case When a.NCR_ID = 3 Then a.NCR Else Null End) As [NCR4],
Max(Case When a.NCR_ID = 4 Then a.NCR Else Null End) As [NCR5],
Max(Case When a.NCR_ID = 5 Then a.NCR Else Null End) As [NCR6]
From
(
Selectcc.CustomerNumber,
cc.CustomerCredentialID,
lr.LicenseRestrictionID,
lr.Comment,
n As [NCR_ID],
Case n
When 0Then Convert(varchar(30), Substring(lr.Comment, 1, 30))
When 1Then Convert(varchar(30), Substring(lr.Comment, 31, 30))
When 2Then Convert(varchar(30), Substring(lr.Comment, 61, 30))
When 3Then Convert(varchar(30), Substring(lr.Comment, 91, 30))
When 4Then Convert(varchar(30), Substring(lr.Comment, 121, 30))
When 5Then Convert(varchar(30), Substring(lr.Comment, 151, 30))
End As [NCR]
FromIADS..LicenseRestriction lr
Inner Join IADS..License l On l.LicenseID = lr.LicenseID
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = l.CustomerCredentialID
And (cc.EndDate IS NULL Or cc.EndDate > @Today)
And cc.CustomerCredentialDispositionID IS NULL
Cross Apply
(
Selectnumber As [n]
Frommaster..spt_values
Where[type] = 'P'
Andnumber >= 0
Andnumber < 6
) tally
Wherelr.RestrictionCodeID = -8
Andlr.Comment IS NOT NULL
) a
Group By a.CustomerNumber, a.CustomerCredentialID, a.LicenseRestrictionID
Order By a.CustomerNumber, a.CustomerCredentialID, a.LicenseRestrictionID
I'm sure the fix I have is something similar to what I've already done, but it's just not clicking in my head yet. Can someone kick-start my brain for me? 🙂
February 18, 2011 at 9:39 am
Here's the output how it works now:
Row 1: CustomerNumber 111111, CustomerCredentialID 111123, LicenseRestrictionID 12341234, NCR1 Hi, NCR2 How, NCR3 Are, NCR4 You
Row 2: CustomerNumber 111111, CustomerCredentialID 111123, LicenseRestrictionID 12341235, NCR1 You, NCR2 Are, NCR3 Not, NCR4 Done, NCR5 Yet
What probably needs to happen is take the "Comment" field from both LicenseRestrictionIDs and concatenate into one value, then take that result and split that into the 6 chucks/fields.
Any ideas?
February 18, 2011 at 2:41 pm
Been searching on how to concatenate rows into a single field and I found a link to a recursive CTE for unknown number of rows to concatenate, I incorporated that into what I need but when running the script I get the "The statement terminated. The maximum recursion 100 has beene exhausted before the statement completion." error. Here's the script that I ran:
;With CTE (CustomerNumber, CustomerCredentialID, NCRCombined, length)
As
(
Selectcc.CustomerNumber,
cc.CustomerCredentialID,
Cast('' As varchar(2000)),
0
FromIADS..CustomerCredential cc
Inner Join IADS..License l On l.CustomerCredentialID = cc.CustomerCredentialID
Inner Join IADS..LicenseRestriction lr On lr.LicenseID = l.LicenseID
And lr.Comment IS NOT NULL
Where cc.EndDate IS NULL
Group By cc.CustomerNumber, cc.CustomerCredentialID
Union All
Selectcc.CustomerNumber,
cc.CustomerCredentialID,
Cast(NCRCombined + Case When length = 0 Then '' Else ' ' End + lr.Comment As varchar(2000)),
length + 1
From CTE c
Inner Join IADS..CustomerCredential cc On cc.CustomerCredentialID = c.CustomerCredentialID
Inner Join IADS..License l On l.CustomerCredentialID = cc.CustomerCredentialID
Inner Join IADS..LicenseRestriction lr On lr.LicenseID = l.LicenseID
And lr.Comment IS NOT NULL
Wherecc.EndDate IS NULL
Andlr.Comment > c.NCRCombined
)
SelectCustomerNumber,
CustomerCredentialID,
NCRCombined
From
(
SelectCustomerNumber,
CustomerCredentialID,
NCRCombined,
RANK() OVER (Partition By CustomerCredentialID Order By length Desc)
FromCTE
) D (CustomerNumber, CustomerCredentialID, NCRCombined, rank)
Where rank = 1
I ran just a simple query to see what the count is for each CustomerNumber/CustomerCredentialID to see how many recursion steps are truly needed and the maximum I found is 4. Obviously something's wrong with my CTE.
February 18, 2011 at 5:17 pm
Hi Gregory,
please provide table def and sample data of all tables involved in a ready to use format. Also please include your expected result based on the sample data you provided.
Furthermore, remove all links to your original databases since we don't have those.
Make sure yor test code runs in any database (including tempDB).
As a side note: you might want to start a new thread ofr a new question. A thread is context related. Not user specific... 😉
February 21, 2011 at 3:02 pm
gregory.anderson (2/18/2011)
Been searching on how to concatenate rows into a single field and I found a link to a recursive CTE for unknown number of rows to concatenate,...
A recursive CTE to do such concatenation is hidden RBAR. Please see the following article for a high speed method to concatenate...
http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
Be sure to read the discussion that follows that article.
--Jeff Moden
February 28, 2011 at 7:36 am
Jeff Moden (2/21/2011)
gregory.anderson (2/18/2011)
Been searching on how to concatenate rows into a single field and I found a link to a recursive CTE for unknown number of rows to concatenate,...A recursive CTE to do such concatenation is hidden RBAR. Please see the following article for a high speed method to concatenate...
http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
Be sure to read the discussion that follows that article.
Thanks for the link to the article Jeff, that's exactly what I need.
I have a question about the script, not sure if I should ask here or in the thread for that article, but here goes....
I get an error trying to run the script saying "Invalid column name 'Value'". I'm running SQLS 2k5. I tried running the script from Mgmt Studio 2008 R2, but that didn't work either. If you're still willing to help out, that'd be great!
February 28, 2011 at 5:46 pm
gregory.anderson (2/28/2011)
Jeff Moden (2/21/2011)
gregory.anderson (2/18/2011)
Been searching on how to concatenate rows into a single field and I found a link to a recursive CTE for unknown number of rows to concatenate,...A recursive CTE to do such concatenation is hidden RBAR. Please see the following article for a high speed method to concatenate...
http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
Be sure to read the discussion that follows that article.
Thanks for the link to the article Jeff, that's exactly what I need.
I have a question about the script, not sure if I should ask here or in the thread for that article, but here goes....
I get an error trying to run the script saying "Invalid column name 'Value'". I'm running SQLS 2k5. I tried running the script from Mgmt Studio 2008 R2, but that didn't work either. If you're still willing to help out, that'd be great!
"Value" has to be all lower case for XML. There was quite a discussion attached to that article about that very subject. Are you all set on it now?
--Jeff Moden
Viewing 8 posts - 106 through 112 (of 112 total)
You must be logged in to reply to this topic. Login to reply