Performance of Combination lookup I've been looking into this most of the day and I'm still not getting far so I thought I'd ask on here.

Basically its a lottery system and what I need to do is check whether 4 of the 6 balls I have selected are already taken in the database.

The table I'm using to test looks like this

CREATE TABLE [dbo].[Lines](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Ball1] [int] NOT NULL DEFAULT ((0)),
   [Ball2] [int] NOT NULL DEFAULT ((0)),
   [Ball3] [int] NOT NULL DEFAULT ((0)),
   [Ball4] [int] NOT NULL DEFAULT ((0)),
   [Ball5] [int] NOT NULL DEFAULT ((0)),
   [Ball6] [int] NOT NULL DEFAULT ((0)))


I have a query that works but its slow, too slow for our requirements. We have estimated around a 20mil record count in this table. So far with just 5mill in our test db its taking ~2 seconds to run the query even with indexing. I see in the plan that this is due to a table scan but I cant figure out how to get around this.

The query I'm testing with is this.

DECLARE @matchCount INT = 4

DECLARE @ball1 INT = 2
DECLARE @ball2 INT = 11
DECLARE @ball3 INT = 16
DECLARE @ball4 INT = 28
DECLARE @ball5 INT = 36
DECLARE @ball6 INT = 39

SELECT
       count(*)
FROM Lines
WHERE 
       CASE WHEN [Ball1] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball2] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball3] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball4] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball5] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END +
       CASE WHEN [Ball6] IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) THEN 1 ELSE 0 END &gt;= @matchCount


Is there a better way to achieve what I am try to do? or do I have to throw more hardware at it? 

This is the execution plan from the above query.



Thanks in advance!
I would change your table layout. If you used something like:

(LineId int, BallSequence tinyint, BallVal int)

The combination of (LineId, Ballsequence) would be your PK.

Then your query would be:

SELECT COUNT(*) 
FROM 
( SELECT 1
  FROM dbo.Lines 
  GROUP BY LineID
  HAVING SUM( CASE WHEN 
              BallVal IN (@ball1, @ball2, @ball3, @ball4, @ball5, @ball6) 
              THEN 1 ELSE 0 END) &gt;= @matchCount
) AS t ;


...and it could be covered by an index in BallVal.