Let's play lotto!
Ok. I have a
numbers table with 80 rows (numbers 1-80):
create table dbo.numbers (knum tinyint primary key identity);
I have a table with the
drawid, and each number drawn.
create table dbo.draws (drawid int, drawnumber tinyint);
Now, given that the
draws table may have data like:
drawid drawnumber 1 10 1 36 1 54 1 75 2 9 2 45 2 46 2 72
I want to find out when was the last time every possible three-number permutation occurred.
I'm using this:
declare @curdraw int select @curdraw = max( drawid)-100 from draws; select TOP 10 K1,K2,K3, @curdraw-max(d1.drawid) from THREES inner join DrawNumbers d1 WITH (NOLOCK) ON K1 = D1.DRAWNUMBER INNER JOIN DRAWNUMBERS D2 WITH (NOLOCK) ON K2 = D2.DRAWNUMBER AND D1.DRAWID = D2.DRAWID INNER JOIN DRAWNUMBERS D3 WITH (NOLOCK) ON K3 = D3.DRAWNUMBER AND D1.DRAWID = D3.DRAWID WHERE D1.DRAWID < @CURDRAW GROUP BY K1, K2, K3 ORDER BY @curdraw-max(d1.drawid) DESC
(Oh, and table
Threes is a table with all 492,000+ three-number combinations from 1-80)
Is there a better way to do what I'm trying to do here? This particular query is just very slow and I'm sure someone with better math/grouping skills could do better.
Answers to comments - clarifications:
It's for a keno game. Every draw has 20 numbers out of the 80. I just showed 4 each as an example.
Two years of accumulated draws, at 254 draws a day with 20 numbers per draw...
Drawnumbertable is currently 3.7 mil rows. Also, what I'm looking for specifically is "out of every 3-number combination possible can you give me a sorted list of how long it has been since any one of them has hit, ordered by that length".
top 10is merely because I don't need to see all 490K results.
The primary keys:
USE [OKD] GO ALTER TABLE [dbo].[DrawNumbers] ADD CONSTRAINT [PK_DrawNumbers] PRIMARY KEY CLUSTERED ( [DrawID] ASC, [DrawNumber] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] GO USE [OKD] GO ALTER TABLE [dbo].[threes] ADD PRIMARY KEY CLUSTERED ( [THREEID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] GO