|
| I have 24 million 5 number combinatons (non repeating , 1 - 80) and I have
them loaded in a normalized table thats designed like
CREATE TABLE [Combinations_5] (
[CombinationId] [int] NOT NULL ,
& #91;CombinaitonIndex
] [int] NULL ,
& #91;CombinationValue
] [int] NOT NULL ,
CONSTRAINT & #91;PK_Combinations_
5] PRIMARY KEY CLUSTERED
(
[CombinationId],
& #91;CombinationValue
]
) ON [PRIMARY]
) ON [PRIMARY]
GO
When I first started to search I would have a sp that searched based upon
combinationindex and combinationvalue like so
( loaded the search string in to a temp table named #numbers)
select @TempDrawingNumber = CombinationId from Combinations_5 where
[value] = (select [value] from #numbers where [index] = 1)
and CombinationId in
(select CombinationId from Combinations_5 where [value] = (select
[value] from #numbers where [index] = 2) group by CombinationId)
and CombinationId in
(select CombinationId from Combinations_5 where [value] = (select
[value] from #numbers where [index] = 3) group by CombinationId)
and CombinationId in
(select CombinationId from Combinations_5 where [value] = (select
[value] from #numbers where [index] = 4) group by CombinationId)
and CombinationId in
(select CombinationId from Combinations_5 where [value] = (select
[value] from #numbers where [index] = 5) group by CombinationId)
is there a better way sql wise, it seems like there should be something
better.
Ok so searching took a while and my next idea was to create a hash key for
each combination and use that to reference the combination in the table. I
know I'm crossing into comp.programming land here but for those who do both
like I do might know a better way. But anyway, I tried SuperFastHash and got
some duplicates aka collisions and wasn't sure what try next other than
every other hash function I can find.
Anyway have any ideas or similiar experiences that can help ?
Thanks alot !!
Best Regards.
|
|