March 29, 2011 at 7:28 am
I have a situation where I'm creating a drawing pool. The person in first place gets 100 entries, 2nd gets 99, etc. I've created a sort of "number table" where I have the ID of the person and the count of the number of entries they need to have in the final table.
My problem is the only way I can think of to insert multiple entries (i.e. 100 for 1st) is to cursor through the above table, get the count and then run a while loop with an insert to insert the appropriate number of rows in the final table. I'm dealing with several thousand participants so this takes a few minutes to run.
I'm wondering if anyone has a thought about how to do this without a cursor/while loop. I can eliminate the cursor and replace that with an outer while loop, but that's just a different flavor of the same thing. I can think of any other way to do, for example 100 inserts into a table without looping.
Can you?
TIA
:unsure:
March 29, 2011 at 7:45 am
;WITH CTE_Names (name, Rank, Tickets) AS (
SELECT TOP 100
name
, ROW_NUMBER() OVER ( ORDER BY Name ) AS Rank
, 101 - ROW_NUMBER() OVER ( ORDER BY Name ) AS Tickets
FROM
[master].sys.objects
ORDER BY
Name
)
--SELECT * FROM CTE_Names
SELECT * FROM CTE_Names Nms INNER JOIN dbo.Tally N ON N.N <= Nms.Tickets
ORDER BY name, Rank, Nms.Tickets, N
March 29, 2011 at 7:48 am
I think I see where you're going. Two things:
1. What is dbo.Tally?
2. Do I just add an Insert above the Select from the CTE to actually perform the insert?
March 29, 2011 at 7:57 am
It's a table with only 1 column. I have all numbers from 1 to 30 000 in there.
March 29, 2011 at 7:58 am
And yes you need to had insert into before the final select.
I consider that step gravy since I don't know your data and not 100% sure that this is what you wanted to do.
Hope you have all you need to finish this project.
March 29, 2011 at 7:59 am
Looks like it might do the trick. I'll give it a shot.
Thanks.
March 29, 2011 at 8:34 am
Here's the final entire thing. Went from minutes to milliseconds. Much thanks.
Declare @counts table (ID int, Entries int)
truncate table dbo.DrawingPool2
-- Get players and compute # entries based on position (1=100, 2=99, etc)
INSERT INTO @counts
SELECT [Score_ID],
case when score_contest_id=9 and Score_Position>100 then 1
when score_contest_id=9 and score_position<=100 then 101-Score_position
when score_contest_id=10 and Score_Position>50 then 1
when score_contest_id=10 and score_position<=50 then 51-Score_position
end as Entries
FROM [NCAAIPD].[dbo].[Scoring]
where score_contest_id in (9,10) and
score_id in (select validatedid from validatedplayers)
-- Insert "n" rows into pool based on count
;WITH CTE_Names (ID, Rank, Entries) AS (
SELECT TOP 100 PERCENT
ID
, ROW_NUMBER() OVER ( ORDER BY ID ) AS Rank
, Entries
FROM
@counts
ORDER BY
ID
)
INSERT INTO dbo.DrawingPool2
SELECT ID
FROM CTE_Names Nms INNER JOIN dbo.Tally N ON N.N <= Nms.Entries
ORDER BY Nms.Entries desc, N
Probably could have done the entire thing in the CTE but this fixes my performance issue and after this year someone else will be responsible. I think this is less complex for them to understand.
March 29, 2011 at 8:38 am
Seems clear enough to me. As longs as it runs in ms now I think you'll be fine. I wouldn't tune this any further unless this query ran a couple 1000 times an hour, or a t least per day.
March 29, 2011 at 8:40 am
Runs once a year
:w00t:
Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply