July 31, 2007 at 4:03 pm
Hello All,
I need some of the expert advice on a situation I'm facing.
We have about 240K Customers in our database. We created a Customer Segmentation process to segment our Customers in their proper group and then do targetted marketing.
I have created a Stored Procedure called SP_CustomerProfiles_Main that has the functionality and makes use of 2 other stored procedures (sp_getnextlevel_single and SP_GetNextLevel_Multiple). This process use 6 tables and 6 Views and generates a table (called Customer_Profiles table) that contains the segmented data.
I'm using temporary tables throughout the process to speed up execution and have utilized the use of Cursors to operate on individual rows.
The job is scheduled to run every weekend on a dedicated database server and was taking 16 to 20 hours to execute when we had about 196K customers. But now the performance has significantly dropped to 55 to 70+ hours to process about 240K customers.
I have tried few minor things to optimize it but haven't seen any promising results.
Questions:
1) How can I optimize the performance? Please specify the best practices.
2) Is there any difference in executing a job in a database that has numbers of job scheduled to run and has lot more activity VS an isolated database just created to execute this job?
3) Is using Cursor the optimal approach, to operate on single row of data?
4) Any idea what may have caused to contribute in the performance drop? The system configuration is exactly the same when the process was originally created.
Any help in this matter will be greatly appreciated.
Best Regards,
JB
JB
July 31, 2007 at 4:07 pm
Your best bet here will be to post your table DDL and your code. SQL Server works best by operating on Sets of data, not row-by-row (look up RBAR...a Modenism on SSC). The use of the cursor here is bound to be your problem. Re-write your process (yes, you may have to start over) to work on Sets of data instead of rows. What takes a cursor 16-20 hours to finish may very well take a Set based solution minutes to complete.
July 31, 2007 at 4:19 pm
Please don't cross-post in multiple forums. Posting in a single forum will almost always get an answer.
That being said, John's right. Your cursor is the 900lb gorilla of an obvious problem here. Iterative processing techniques are very poor for performance in SQL Server. That being said, there may be other issues at stake. I'd break down the whole process and see what pieces are causing the most problems after rewriting the code to avoid cursors. There are good articles out there for how to optimize the use of temporary tables. You might also consider how you index the tables you use. Dropping indexes on tables which are updated/inserted/deleted frequently and recreating them after the procedure is done might help. While adding certain indexes to alleviate table scans could save in certain areas. Staging data might also be useful.
Unfortunately it's hard to come to a forum and post a relatively open-ended question like this and have someone be able to say "this is 100% your problem". But those are things I'd look at first.
Oh, and don't prefix your stored procedures with "sp_", it causes the processor to search the master database first for the procedure. Not a big deal, but just not a good practice.
July 31, 2007 at 4:33 pm
Thank you Joan and Aaron for your reply. To give you an idea & to anwer John's question I'm writing down the code & DDL here:
CREATE TABLE [dbo].[Customer_Profiles] (
[CustID] [int] NULL , [CRID] [varchar] (50) , [FirstName] [varchar] (50) ,
[LastName] [varchar] (50) , [AcqSegment] [varchar] (50) , [AcqDate] [datetime] NULL , [AcqLangCode] [varchar] (50) , [AcqLanguageName] [varchar] (50) , [AcqSource] [varchar] (50) , [CurLastPurchaseDate] [datetime] NULL , [CurLangCode] [varchar] (50) , [CurLanguageName] [varchar] (50) , [CurSegment] [varchar] (50) , [RecID] [int] NULL , [TestCell] [varchar] (50) , [EmailAddress] [varchar] (50) ) ON [PRIMARY]
1 st SP
CREATE PROCEDURE SP_Customer_Profile_Main
as
declare @CustomerNumber as int
declare @FirstName as varchar(50)
declare @LastName as varchar(50)
declare @AcqDate as datetime
declare @CurLanguage_Code as varchar(50)
declare @AcqLanguage_Code as varchar(50)
declare @CurLanguage as varchar(50)
declare @AcqLanguage as varchar(50)
declare @CurSegment as varchar(50)
declare @AcqSegment as varchar(50)
declare @AcqSource as varchar(50)
declare @AcqLastDateDiff as int
declare @RecencyID as int
declare @LastDate as datetime
declare @Test_Cell as varchar(50)
declare @Email as varchar(50)
declare @NoOfProducts_Last as int
declare @NoOfProducts_First as int
declare @icount as int
declare @customercount as int
declare @CustomerNo_Counter as int
declare @Level as varchar(16)
declare @NewCurSegment as varchar(50)
declare @NewLanguage as varchar(255)
declare @NewLanguage_Code as varchar(50)
--Copy Customer Table to a Temporary Table
select * into #t11 from Customer
--Copy Product Quantity View to a Temporary Table
select * into #t12 from Product_Quantity
--Copy Acq_Information_View to a Temporary Table
select * into #t13 from Acq_Information_View
-- customercount for the loop
-- select @customercount = (select count(*) from #t11)
select @customercount = (select MAX(CustomerNumber) from #t11)
set @CustomerNo_Counter = 1
-- Delete the Customer Profile Table to start with an empty table
delete from Customer_Profiles
-- AcqSource and TestCell are not currently being generated
set @AcqSource = NULL
set @Test_Cell = NULL
while @CustomerNo_Counter < @customercount + 1
begin
SET @CustomerNumber = 0
-- Assign the Customer Number to a variable
select @CustomerNumber = CustomerNumber from #t11 where CustomerNumber = @CustomerNo_Counter
IF (@CustomerNumber = 0)
begin
set @CustomerNo_Counter = @CustomerNo_Counter + 1
CONTINUE
end
-- Assign the First Name to a variable
select @FirstName = FirstName from #t11 where CustomerNumber = @CustomerNo_Counter
-- Assign the Last Name to a variable
select @LastName = LastName from #t11 where CustomerNumber =@CustomerNo_Counter
-- Assign the Email to a variable
select @Email = Email from #t11 where CustomerNumber = @CustomerNo_Counter
-- Assign the Start Date for the Customer and assign to a variable
select @AcqDate = MinOfOrderDate from #t13 where CustomerNumber = @CustomerNo_Counter
-- Assign the Last Date for the Customer and assign to a variable
select @LastDate = MaxOfOrderDate from #t13 where CustomerNumber = @CustomerNo_Counter
-- Grab the number of days between first and last date and assign to a variable
set @AcqLastDateDiff = DateDiff(dd, @AcqDate, @LastDate)
-- Calculate RecencyID, difference between today and LastDate. Assumes that the system the proc is running on is set to the correct date/time in the same time zone as the source tables.
-- Added
set @RecencyID = DateDiff(dd, @LastDate, GETDATE())
if @RecencyID < 0
begin
set @RecencyID = 0
end
-- Assign to number of products in the Customers last order and assign to a variable
select @NoOfProducts_Last = NumberOfProducts from #t12 where CustomerNumber = @CustomerNo_Counter and OrderDate = @LastDate
-- If the number of products in last order is 1, find the current segment by executing the sp_getnextlevel_single procedure
if @NoOfProducts_Last = 1
Begin
-- Assign the Level and Language Code to variables to be passed in the sp_getnextlevel_single procedure
select @CurLanguage_Code = LanguageCode, @CurLanguage = Convert(varchar(255),Language), @Level = Convert(varchar(16),Stk_Contents) from Customer_Order_Info_View where CustomerNumber = @CustomerNo_Counter and OrderDate = @LastDate
-- Print @Language
execute sp_getnextlevel_single @CurLanguage_Code, @Level, @return_Level = @CurSegment OUTPUT
End
-- If the number of products in last order is > 1, find the current segment by executing the sp_getnextlevel_multiple procedure
if @NoOfProducts_Last >1
Begin
execute sp_getnextlevel_multiple @CustomerNo_Counter, @LastDate, @CurSegment = @CurSegment OUTPUT, @Language = @CurLanguage OUTPUT,
@Language_Code = @CurLanguage_Code OUTPUT
End
-- Assign the Current Segment, Language, and Language Codes to insert into Customer_Profiles
set @NewCurSegment = convert(varchar(50), @CurSegment)
set @NewLanguage = convert(varchar(255), @CurLanguage)
set @NewLanguage_Code = convert(varchar(50), @CurLanguage_Code)
-- RecencyID = 0 means that only one order exists for this customer; Acq and Cur are all equal
if (@AcqLastDateDiff = 0)
begin
set @AcqSegment = @NewCurSegment
set @AcqLanguage = @NewLanguage
set @AcqLanguage_Code = @NewLanguage_Code
end else
begin
if (@NoOfProducts_First = 1)
begin
select @AcqLanguage_Code = LanguageCode, @AcqLanguage = Convert(varchar(50),Language), @Level = Convert(varchar(16),Stk_Contents) from Customer_Order_Info_View where CustomerNumber = @CustomerNo_Counter and OrderDate = @LastDate
execute sp_getnextlevel_single @AcqLanguage_Code, @Level, @return_Level = @AcqSegment OUTPUT
end else
begin
execute sp_getnextlevel_multiple @CustomerNo_Counter, @AcqDate, @CurSegment = @AcqSegment OUTPUT, @Language = @AcqLanguage OUTPUT,
@Language_Code = @AcqLanguage_Code OUTPUT
end
-- set @AcqSegment = NULL
-- set @AcqLanguage = NULL
-- set @AcqLanguage_Code = NULL
end
insert into Customer_Profiles (CustID, FirstName, LastName, AcqLangCode, AcqLanguageName, AcqSegment, AcqDate, AcqSource, CurLastPurchaseDate, CurLangCode, CurLanguageName, CurSegment, RecID, TestCell, EmailAddress)
values(@CustomerNo_Counter, @FirstName, @LastName, @AcqLanguage_Code, @AcqLanguage, @AcqSegment, @AcqDate, @AcqSource, @LastDate, @NewLanguage_Code, @NewLanguage, @NewCurSegment, @RecencyID, @Test_Cell, @Email)
set @CustomerNo_Counter = @CustomerNo_Counter + 1
end
2nd SP
CREATE PROCEDURE sp_getnextlevel_single
-- Parameters Language_Code and Leve
@Language_Code as varchar(50),
@Level as varchar(50),
@return_Level varchar(50) OUTPUT
AS
declare @STK_CODE as varchar(50)
declare @Next_Level as varchar(50)
declare @test-2 as varchar(50)
-- Create temporary table to hold FSTOCK_VIEW
select * into #t5 from FSTOCK_VIEW
-- If the input Level is 'Quick and Simple', return 'Quick and Simple' or 'EOC' as the return_Level
if @Level = 'QandS'
Begin
set @Next_Level = 'Level 1'
-- Search the FSTOCK_VIEW to see if the next level of the product exist
-- If it does not exist, return EOC, otherwise return Level 1
select @STK_CODE = STK_CODE from #t5 where (STK_CustomText1 like @Language_Code and STK_Contents like @Next_Level and STK_Medium like 'CD')
-- If query is empty return 'EOC'
if @STK_CODE = NULL or @STK_CODE = ''
Begin
set @return_Level = 'EOC'
return
End
else
Begin
set @return_Level = 'QandS'
return
End
End
-- If the input Level is 'Level 1', return 'Level 1' or 'EOC' as the return_Level
if @Level = 'Level 1'
Begin
set @Next_Level = 'Level 2'
-- Search the FSTOCK_VIEW to see if the next level of the product exist
-- If it does not exist, return EOC, otherwise return Level 2
select @STK_CODE = STK_CODE from #t5 where (STK_CustomText1 like @Language_Code and STK_Contents like @Next_Level and STK_Medium like 'CD')
--If query is empty, return 'EOC'
if @STK_CODE = NULL or @STK_CODE = ''
Begin
set @return_Level = 'EOC'
return
End
else
Begin
set @return_Level = 'Level 1'
return
End
End
-- If the input Level is 'Level 2', return 'Level 2' or 'EOC' as the return_Level
if @Level = 'Level 2'
Begin
set @Next_Level = 'Level 3'
-- Search the FSTOCK_VIEW to see if the next level of the product exist
-- If it does not exist, return EOC, otherwise return Level 3
select @STK_CODE = STK_CODE from #t5 where (STK_CustomText1 like @Language_Code and STK_Contents like @Next_Level and STK_Medium like 'CD')
-- If query is empty, return 'EOC'
if @STK_CODE = NULL or @STK_CODE = ''
Begin
set @return_Level = 'EOC'
return
End
else
Begin
set @return_Level = 'Level 2'
return
End
End
-- If the input Level is 'Level 3', return 'Level 3' as the return_Level
if @Level = 'Level 3'
Begin
set @return_Level = 'Level 3'
return
End
--If the input Level is Compact, return Compact as the return_Level
if @Level = 'Compact'
Begin
set @return_Level = 'Compact'
return
End
3rd SP
CREATE PROCEDURE SP_GetNextLevel_Multiple
@CustomerNo as int,
@Last_Date as datetime,
--Return variale
@CurSegment as varchar(50) output,
@Language as varchar(255) output,
@Language_Code as varchar(50) output
as
declare @no_of_prod as int
declare @no_of_prod2 as int
declare @Selected_Precidence as int
declare @i as int
declare @num as int
declare @IC as nvarchar(50)
declare @L as nvarchar(25)
declare @Lang as nvarchar(50)
declare @MP as int
declare @counter as int
declare @l-p-2 as int
declare @return_Level as nvarchar(25)
declare @return_CurSegment as varchar(50)
Begin
--Creating a temp table that will contain info for a single user for his last order
--select ItemCode, Stk_contents as [Level], Language, LanguageCode, Priority into #t1 from [Customer_Order_Info_View] where
--CustomerNumber = @CustomerNo and OrderDate = @Last_Date
select Customer_Order_Info_View_Convert_Group.Language1 as Language, Customer_Order_Info_View_Convert_Group.Priority, Customer_Order_Info_View_Convert_Group.LanguageCode1 as LanguageCode, [Level_Value_Table].Level_Name as [Level] into #t1 from [Customer_Order_Info_View_Convert_Group] inner join [Level_Value_Table] on Customer_Order_Info_View_Convert_Group.Max_Level_Value = [Level_Value_Table].Level_Value where
CustomerNumber = @CustomerNo and OrderDate = @Last_Date
--counts the total no. of products in the Customer's last order
select @no_of_prod = count(*) from #t1
--Creating a Cursor to work on each single row in the returned row(s)
Declare getData CURSOR Local Scroll
For
--Get the Level and the language code from temp table, this will used to get the data from the Product_Conflict_Table table
select [Level], LanguageCode from #t1
--Cursor opened
open getData
set @i=1
--Looping through all the records using the Cursor
while @i<= @no_of_prod
Begin
--Fetching the required row from the Cursor
Fetch Absolute @i from getData into @L, @Lang
if @L = 'QandS'
Begin
select @num = QandS from Product_Conflict_Table where Language_Name = @Lang
End
if @L = 'Level 1'
Begin
select @num = [Level 1] from Product_Conflict_Table where Language_Name = @Lang
End
if @L = 'Level 2'
Begin
select @num = [Level 2] from Product_Conflict_Table where Language_Name = @Lang
End
if @L = 'Level 3'
Begin
select @num = [Level 3] from Product_Conflict_Table where Language_Name = @Lang
End
if @L = 'Compact'
Begin
select @num = Compact from Product_Conflict_Table where Language_Name = @Lang
End
--Update the temp table with the priority no (no. of products left)
Update #t1 set #t1.[Priority] = @num where [Level] like @L and LanguageCode like @Lang
--Storing collected data into variables
--select @L as [Level]
--select @Lang as Language
--select @num as Number
set @i = @i+1
End
--Temp table is now filled, findout the highest priority language (most products left)
select @MP=max(Priority) from #t1
--Create logic if there are multiple languages with same @MP
select @counter=count(Priority) from #t1 where Priority= @MP
If @counter=1
--Execute the logic that handle single order with highest priority
Begin
--Get info for the Item code with highest priority
select @L = [Level], @Lang = LanguageCode from #t1 where Priority= @MP
End
Else
--Execute the logic that handle multiple orders with same priority
Begin
--Get info for the Item code with highest priority
select #t1.[Level], #t1.LanguageCode, #t1.Priority, LP.Precedence as Level_Precedence into #t2 from #t1
join Level_Precedence as LP on #t1.[Level] like (LP.[Level]) where Priority= @MP --and #t1.[Level] not like 'Compact'
--Get max precedence from all the rows
select @l-p-2 = max(Level_Precedence) from #t2
--count total rows in #t2
select @no_of_prod2=count(LanguageCode) from #t2 where Level_Precedence=@LP
If @no_of_prod2=1
Begin
--Get info for the Item code with highest priority
select @L = [Level], @Lang=LanguageCode from #t2 where Level_Precedence=@LP
End
If @no_of_prod2>1
--select * into #t33 from Precidence_Table
--select @L = [Level], @Lang = LanguageCode from #t2 where max(Level_Precedence)
--Conflict --Get the Precidence from the Precidence_Table, this will return the no.
select @Selected_Precidence = MIN(PT.Precidence) from #t2 join Precidence_Table as PT on
#t2.LanguageCode = PT.Language_Code where #t2.Level_Precedence=@LP
--select @Selected_Precidence = MIN(#t33.Precidence) from #t2 join #t33 on
--#t2.LanguageCode = #t33.Language_Code where #t2.Level_Precedence=@LP
--Use the Precidence no to get the language code
select @Lang=Language_Code from Precidence_Table where Precidence = @Selected_Precidence
--Use the @Lang variable to get the Level from #t2
select @L = [Level] from #t2 where LanguageCode = @Lang
End
--We now have the LanguageCode and its current Level, now Get the next level for the selected language
execute sp_getnextlevel_single @Lang, @L, @return_Level = @CurSegment OUTPUT
set @return_CurSegment = @CurSegment
set @Language_Code = @Lang
select @Language = Convert( varchar(255),Language) from #t1 where LanguageCode = @Lang
End
-- select * from #t1
-- select @no_of_prod as NoOfProducts
-- select @return_Level = @L
--set @CurSegment
return
-- select @MP as MaxPriority
-- select @counter as NoOfMaxPriorityProd
-- select @l-p-2 as Level_Precedence
-- select @Selected_Precidence as Selected_Precidence
-- select #t1.[Level], #t1.LanguageCode, #t1.Priority, LP.Precedence as Level_Precedence from #t1 join Level_Precedence as LP on #t1.[Level] like (LP.[Level]) where Priority= @MP
-- select #t3.Precidence from #t3 join #t2 ON #t2.LanguageCode = #t3.Language_Code where #t2.Priority = @l-p-2
-- select #t2.Level, #t2.LanguageCode, #t2.Priority, #t2.Level_Precedence, #t3.Precidence from #t2 join #t3 on #t2.LanguageCode = #t3.Language_Code where #t2.Level_Precedence=@LP
-- select Min(Precidence) from #t4
select * from #t2
GO
And John I have tried it without the SP prefix but no difference. Can you also guid me to a referrence.
thanks,
JB
JB
July 31, 2007 at 4:55 pm
Missing a key piece of information here... basically, what does all that code do? I see stuff like 'Level 3' in it... is it meant to produce some sort of hierarchical report or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2007 at 5:29 pm
Hi Jeff,
Well we have a product line of forign languages. Each language has potential levels (products). QandS is the lowest priced product where as Compact, Level 1, Leve2 and Level3 are high end products.
Customers who are buying a low end QandS product are considered to be in the QandS segment, we record their recency Id (days since last purchase), the acquisition date, acquisition segment, Name, Current segment. Once they are in the database we try to market them for the next available product in their current segment. Which in this case will be Level1, later when they buy Level1 we try to sell them Level2 and so on. So we move them through product in their segment.
This process utilize 11 tables to collect this data from and help us segment customer in to their respective segment. This process creates Customer_Profiles table and record this processed data there. We have tables that have list of products and their respective priority and precidence (incase if some one has multiple products in their last order). A customer can only have one product category as his/her current segment, so that we can market them the same the next time.
The 1 st SP [SP_Customer_Profile_Main] has the main logic to do the segmentation. It uses 2nd SP [sp_getnextlevel_single] to get current/acquisition segments of customer who only had single item in their last order. It also uses 3rd SP [SP_GetNextLevel_Multiple] to get the same for customers who have multiple products in their last purchased.
Does this make sense?
Thanks,
JB
JB
July 31, 2007 at 5:31 pm
It looks like the first procedure does a refresh of the Customer_Profiles table and then inserts records one at a time back in by calling the other two procedures to process data.
If it were me, I'd rewrite this whole thing with this in mind (bear in mind this is from a quick look-through and as Jeff said, virtually no understanding of what you're trying for)...
1. You've got subprocedures using cursors for one at a time processing. So you've got cursors in your cursors. This is going to suck your performance down significanly across the server. You could use set-based processing to speed things up, instead maknig a couple of passes across the data set.
2. CASE might be your friend in removing the procedure sp_getnextlevel_single. Instead of calling this procedure one at a time for many rows, you could issue an update to the temporary table you're working on with a series of case statements to evaluate conditional logic.
3. A lot of your logic is dependent upon correlating one value in one table with another value in another. You tend to assign these to variables and then insert them into the table later. You would do better to join the tables together based on your common keys and move all the rows at once rather than pluck them one at a time, decide how they should be classified and move the data over.
August 1, 2007 at 10:16 am
Thanks Aaron, Jeff and John for your input.
Let me try this and I'll let you know if I have any issue.
JB
JB
August 1, 2007 at 10:24 am
Also, you are using SELECT INTO to create temporary tables to use in your procedure. SELECT INTO copies the table structure in terms of columns, but indexes are not copied over. This means that you are doing a table scan on your temporary customer table for each iteration of your loop in your main SP. Furthermore, you are grabing your values into variables one at a time instead of all at once which means that you are going through multiple table scans of your customer temp table for each iteration of your loop.
At first glance, it looks like this whole process can be re-written as a set based process. I would suggest working down this route and post questions as they arise. If I get some time to help further with code, I'll do so. With Jeff on the scene here, you may get code help sooner rather than later.
August 1, 2007 at 3:08 pm
I am a bit confused by the logic in your sp_getnextlevel_single procedure as it appears to always return the same level that is passed into it. The comments inside the proc also contradict each other so I’m not sure what you are trying to do. For example, the Quick and Simple section will either return ‘EOC’ or ‘QandS’. Shouldn’t it return the next level? Shouldn’t it return either ‘EOC’ or ‘Level 1’?
August 1, 2007 at 3:55 pm
John, I apologize about the confusion there.
Actually each product category (Language in this case) doesn't necessarily contains all the products.
E.g.:
1) Product category; French, Contain QandS, Level1, Level2, and Level3.
2) Product category; Farsi, Contain QandS, Compact no other Level. Compact is a lower product than the Level1.
3) Product category; Irish contains only QandS
4) Product category; Swiss German only contains Compact
5) Product category; Arabic, contains QandS and Level1 only
Etc.
Now every customer is categorized in to a Segment (CurSegment) based on what he or she have purchased in their last order and what products are available in that category.
We have bit complicated business rules here. Let me try and explain this. So for a customer who purchased:
1) French QandS, their Current Segment should be “QandS”. And their next level is Level1 since French has that product available. If the Customer bought Level1 their Current segment is “Level1” and next level is Level2 since that is available. If Customer bought Level3 Current Segment is “Level3”. No next level
2) Farsi Compact, their Current Segment is “Compact”. They don’t have a next level available.
3) Irish QandS, their Current Segment is “EOC”. Since there is no Level1 exist. So they are at the End of their curriculum. May look wired but this is the business rule that we follow.
4) Swiss German Compact, their Current Segment is “Compact”. They don’t have a next level available.
5) Arabic Level1, their Current Segment is “EOC”. Since there is no Level2 or Level3 exist.
EOC -- Means End of Curriculum
Does this help.
Regards,
JB
JB
August 1, 2007 at 4:12 pm
Yes, that makes perfect sense to me. Amazingly, this is what I gathered from reading your code. But the problem is, your 2nd SP does not match the business rules you’ve just laid out. Let’s walk through your example #1 for the French purchase. If you pass ‘QandS’ into your SP, you should get ‘QandS’ back and not ‘Level 1’ as your business rule states. Look at the logic:
if @Level = 'QandS'
Begin
set @Next_Level = 'Level 1'
-- Search the FSTOCK_VIEW to see if the next level of the product exist
-- If it does not exist, return EOC, otherwise return Level 1
select @STK_CODE = STK_CODE from #t5 where (STK_CustomText1 like @Language_Code and STK_Contents like @Next_Level and STK_Medium like 'CD')
-- If query is empty return 'EOC'
if @STK_CODE = NULL or @STK_CODE = ''
Begin
set @return_Level = 'EOC'
return
End
else
Begin
set @return_Level = 'QandS'
return
End
End
You are assigning the ‘Level 1’ value to your @Next_Level parameter, but your return parameter @return_Level is getting set to ‘QandS’. This same logic flows through each of your @Level blocks. Am I missing something here?
After looking into the whole scenario a bit more, this is a relatively easy Set based re-write, but we need to understand a bit more why you are doing some of the things that you are doing.
August 1, 2007 at 4:12 pm
Yes, that makes perfect sense to me. Amazingly, this is what I gathered from reading your code. But the problem is, your 2nd SP does not match the business rules you’ve just laid out. Let’s walk through your example #1 for the French purchase. If you pass ‘QandS’ into your SP, you should get ‘QandS’ back and not ‘Level 1’ as your business rule states. Look at the logic:
if @Level = 'QandS'
Begin
set @Next_Level = 'Level 1'
-- Search the FSTOCK_VIEW to see if the next level of the product exist
-- If it does not exist, return EOC, otherwise return Level 1
select @STK_CODE = STK_CODE from #t5 where (STK_CustomText1 like @Language_Code and STK_Contents like @Next_Level and STK_Medium like 'CD')
-- If query is empty return 'EOC'
if @STK_CODE = NULL or @STK_CODE = ''
Begin
set @return_Level = 'EOC'
return
End
else
Begin
set @return_Level = 'QandS'
return
End
End
You are assigning the ‘Level 1’ value to your @Next_Level parameter, but your return parameter @return_Level is getting set to ‘QandS’. This same logic flows through each of your @Level blocks. Am I missing something here?
After looking into the whole scenario a bit more, this is a relatively easy Set based re-write, but we need to understand a bit more why you are doing some of the things that you are doing.
August 1, 2007 at 4:35 pm
John,
If you notice the example of our business rule:
1) French QandS, their Current Segment should be “QandS”. And their next level is Level1 since French has that product available. If the Customer bought Level1 their Current segment is “Level1” and next level is Level2 since that is available. If Customer bought Level3 Current Segment is “Level3”. No next level
2) Farsi Compact, their Current Segment is “Compact”. They don’t have a next level available.
3) Irish QandS, their Current Segment is “EOC”. Since there is no Level1 exist. So they are at the End of their curriculum. May look wired but this is the business rule that we follow.
4) Swiss German Compact, their Current Segment is “Compact”. They don’t have a next level available.
5) Arabic Level1, their Current Segment is “EOC”. Since there is no Level2 or Level3 exist.
To make it more clear, understand the 3 logical hierarchy of products we have :
a) QandS --> Level1 --> Level2 --> Level3
b) QandS -->Compact
c) Compact only
Now lets say you have a language that is under a) but doesn't have all products for that hierarchy it should return Cursegment as "EOC". But if the last purchase was for Level3 it should return Level3. For b) and c) it should return Compact if they bought compact last time.
Look at the code you'll realize this:
if @Level = 'QandS'
[
@Next_Level = 'Level 1' -- always since this is under heirarchy a)
It checks the table that has product details to see if this has next Level1 is available. If it does it sets @return_Level = 'QandS' else if doesn't have any level after QandS it set it to @return_Level = 'EOC'
]
if @Level = 'Level 1' -- always since this is under heirarchy a)
[
It checks the table that has product details to see if this has next Level2 is available. If it does it sets @return_Level = 'Level1' else if doesn't have any level after Level1 it set it to @return_Level = 'EOC'
]
if @Level = 'Level2'
[
It checks the table that has product detailto see if this has next Level 3 is available. If it does it sets @return_Level = 'Level2' else if doesn't have any level after Level1 it set it to @return_Level = 'EOC'
]
if @Level = 'Level3'
[
It doesn't check the table for next level product and sets If it does it sets @return_Level = 'Level3'
]
if @Level = 'Compact'
[
It doesn't check the table for next level product and sets If it does it sets @return_Level = 'Compact'
]
Hope this clarify this.
Best regards,
JB
JB
August 1, 2007 at 4:44 pm
Yes, I understand this part as the code is coded this way, but I thought the purpose of this SP was to get the next level if it exists. Wouldn't you want it to return 'Level 1' if you pass it in 'QandS' and there is a next level?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply