September 20, 2017 at 1:19 pm
I am creating promo codes. They are issued simply by inserting a record for the customer into the PromoCode table. I have a CTE (could also use a temp table) which looks like this:CustomerID(int)
ItemCode(string)
CodesOwed(int)
For each CustomerID/ItemCode, I need to create "CodesOwed" number of rows in the PromoCode table.
I know how to do this with nested loops, but I'm betting someone can think of a clever way to generate rows based on the number in the CodesOwed column, all in one query?
For now, I'm going to do it with nested loops because it needs to get done, but I thought this was a good opportunity to stump the community and learn something new!
Who can figure it out?
The destination table looks like this, but it should be irrelevant to figuring out the answer...CustomerID(int)
ItemCode(string)
PromoCode(string)
Expiration(date)
The promo code and expiration date are generated by functions, so inserting into that table looks like this:insert into PromoCode (CustomerID, ItemCode, PromoCode, Expiration)
select @CustomerID, @ItemCode, dbo.newPromoCode(), dbo.getExpirationDate(30)
But I basically need to execute that insert "CodesOwed" number of times, based on the CTE.
September 20, 2017 at 1:28 pm
First order of business. Does that newPromoCode function need to be involved in every individual insert? Meaning that if a record has a value of 20 for CodesOwed, then we need to execute that function that many times? I have a suggestion. Allow that function to take an input parameter indicating how many to generate, and turn it into an Inline Table-Valued Function, so that you can JOIN to it or SELECT FROM it. Provide the code for that function and hopefully I can turn it into an ITVF for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 20, 2017 at 1:39 pm
I am free to do whatever I want. Currently the function only generates one new "random" promo code (it's what the customer types in to the checkout to get their free item) - but it's all under my control so I can definitely make a function that returns multiple codes as a TVF. I can see where you're going with that, and I like it 🙂
September 20, 2017 at 1:53 pm
Jasmine D. Adamson - Wednesday, September 20, 2017 1:39 PMI am free to do whatever I want. Currently the function only generates one new "random" promo code (it's what the customer types in to the checkout to get their free item) - but it's all under my control so I can definitely make a function that returns multiple codes as a TVF. I can see where you're going with that, and I like it 🙂
Let me know if you need any help doing that. An INLINE TVF is the best option performance wise, but that would eliminate any kind of declared variable or looping code, and it's not always easy to convert from procedural code to set-based.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 20, 2017 at 2:17 pm
OK this is what I came up with... this works quite well.CREATE FUNCTION dbo.GetMultiplePromoCodes(@NumCodes int)
RETURNS @PromoCodes TABLE
(
PromoCode nvarchar(10)
)
AS
BEGIN
while (@NumCodes > 0) BEGIN
insert @PromoCodes (PromoCode) select dbo.fnGetPromoCode()
set @NumCodes = @NumCodes - 1
END
return
END
I just use this in a outer join to generate multiple rows. Works great! Thanks for the idea!
September 20, 2017 at 2:22 pm
Jasmine D. Adamson - Wednesday, September 20, 2017 2:17 PMOK this is what I came up with... this works quite well.CREATE FUNCTION dbo.GetMultiplePromoCodes(@NumCodes int)
RETURNS @PromoCodes TABLE
(
PromoCode nvarchar(10)
)
AS
BEGIN
while (@NumCodes > 0) BEGIN
insert @PromoCodes (PromoCode) select dbo.fnGetPromoCode()
set @NumCodes = @NumCodes - 1
ENDreturn
END
I just use this in a outer join to generate multiple rows. Works great! Thanks for the idea!
That wasn't quite what I had in mind. I'm talking about changing the actual fnGetPromoCode function to use a parameter, and generate the number of codes needed directly without using a loop. Without the code for that function it's hard to know if that's possible without doing the same thing you did, but it might be, and should be a lot faster than a WHILE loop.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 20, 2017 at 2:30 pm
We have to leave that function in place because other apps use it to generate single codes when needed. It is the approved method of generating codes and I didn't want to create a maintenance problem by having a second version of that process, so calling the original function in the new one kinda makes sense. I'm having a little trouble figuring out the last query though... this isn't working...select CustomerID, RewardItemCode, c.PromoCode
from CodesToProcess --(my CTE)
join (select PromoCode from WBCData_Sandbox.dbo.GetMultiplePromoCodes(CodesToProcess.NumOwed)) c on 1=1
Not sure how to figure out the right syntax for that? Maybe I need more coffee 🙂
September 20, 2017 at 2:46 pm
Jasmine D. Adamson - Wednesday, September 20, 2017 2:30 PMWe have to leave that function in place because other apps use it to generate single codes when needed. It is the approved method of generating codes and I didn't want to create a maintenance problem by having a second version of that process, so calling the original function in the new one kinda makes sense. I'm having a little trouble figuring out the last query though... this isn't working...select CustomerID, RewardItemCode, c.PromoCode
from CodesToProcess --(my CTE)
join (select PromoCode from WBCData_Sandbox.dbo.GetMultiplePromoCodes(CodesToProcess.NumOwed)) c on 1=1
Not sure how to figure out the right syntax for that? Maybe I need more coffee 🙂
Try this:SELECT CTP.CustomerID, CTP.RewardItemCode, C.PromoCode
FROM CodesToProcess AS CTP --(my CTE)
CROSS APPLY WBCData_Sandbox.dbo.GetMultiplePromoCodes(CTP.NumOwed) AS C;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 20, 2017 at 2:53 pm
Oh I see now! I'm going to have to spend the rest of the day learning CROSS APPLY. I've been doing SQL for 20 years and that one still confuses me a bit. Clearly it's pretty useful 🙂
Thanks very much for the help!
September 20, 2017 at 3:01 pm
Think of CROSS APPLY as a cartesian product creator, and roughly equivalent to an INNER JOIN on 1 = 1 between two tables separated by commas in the FROM clause. However, because of the APPLY operator, you can stack CROSS APPLY references and refer to any previous table reference and use a subquery that only references a VALUES clause, as those listed values can also refer to any previous table reference. OUTER APPLY just changes the join to OUTER instead of INNER. Does that help? Use your Google Fu to find a video made by Itzhik Ben-Gan on the APPLY operator. It's incredibly useful to help you understand how you can use it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 20, 2017 at 5:23 pm
Jasmine D. Adamson - Wednesday, September 20, 2017 2:53 PMOh I see now! I'm going to have to spend the rest of the day learning CROSS APPLY. I've been doing SQL for 20 years and that one still confuses me a bit. Clearly it's pretty useful 🙂
Thanks very much for the help!
Please see the following articles for a great description of how CROSS APPLY and OUTER APPLY work.
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2017 at 5:57 pm
It won't matter much for performance for your function because it calls a slow but required scalar function but, after 20 years of doing this, it's time to learn how to avoid writing loops and recursive CTEs, both of which are a form of RBAR (pronounced "ree-bar" like the metal rods stuck in cement forever and is a "Modenism" for "Row By Agonizing Row").
Please see the following article for an introduction to the method and then take a look at how I've rewritten the function you wrote. Although I'm not creating any sequential numbers with the rewritten function and I'm using very high performance constrained CROSS JOINS instead of a physical Tally Table, it does use the "presence of rows" to drive a "set based loop" or "pseudo-cursor".
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Here's your function rewritten. Remember that if a function contains the word BEGIN, it's not an iTVF (inline Table Value Function) and it's probably not all that it could be for performance or low resource usage.
CREATE FUNCTION dbo.GetMultiplePromoCodes(@NumCodes int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N)) --10^1 possible rows
,E6(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f) --10^6 possible rows
SELECT TOP (@NumCodes) --@NumCodes can be 0 up to 1 Million here
PromoCode = dbo.fnGetPromoCode()
FROM E6
;
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2017 at 6:03 pm
Thanks Jeff, those are very helpful! I like that way of describing what's going on. Good to see you also. I haven't been here in years, but I do remember you 🙂
September 20, 2017 at 8:43 pm
Jasmine D. Adamson - Wednesday, September 20, 2017 6:03 PMThanks Jeff, those are very helpful! I like that way of describing what's going on. Good to see you also. I haven't been here in years, but I do remember you 🙂
Thanks, Jasmine. I had a "testing artifact" with an "x" in it in the code I posted and your the required call to the scalar function was commented out. I've repaired that in the code above.
Any chance of you posting that scalar function just to see if we can make any performance improvements to it without changing its function or interface?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2017 at 12:38 pm
Jeff Moden - Wednesday, September 20, 2017 8:43 PMJasmine D. Adamson - Wednesday, September 20, 2017 6:03 PMThanks Jeff, those are very helpful! I like that way of describing what's going on. Good to see you also. I haven't been here in years, but I do remember you 🙂Thanks, Jasmine. I had a "testing artifact" with an "x" in it in the code I posted and your the required call to the scalar function was commented out. I've repaired that in the code above.
Any chance of you posting that scalar function just to see if we can make any performance improvements to it without changing its function or interface?
I would love to do that but I probably shouldn't. Don't want to give away the secret of how we generate the codes.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply