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 >= @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!
Aucun commentaire:
Enregistrer un commentaire