I have a query taking a long time to execute and it appears to be from using between conditions on a join. Here is an extremely scaled back example
CREATE TABLE dbo.CustomerInfo (
CustomerID INT
,Income DECIMAL(10, 2)
,Dependents INT
,Age INT
)
INSERT INTO dbo.CustomerInfo
VALUES (1, 50000, 2, 29)
,(2, 125000, 4, 52)
,(3, 20000, 0, 18)
,(4, 98000, 5, 31)
,(5, 47000, 3, 66)
CREATE TABLE dbo.GradeLookup (
MinIncome DECIMAL(10, 2)
,MaxIncome DECIMAL(10, 2)
,MinDependents INT
,MaxDependents INT
,MinAge INT
,MaxAge INT
,Grade VARCHAR(1)
)
INSERT INTO dbo.GradeLookup
VALUES (0,25000,0,1,0,24,'F')
,(0,25000,0,1,25,30,'F')
,(0,25000,0,1,31,55,'F')
,(0,25000,0,1,56,99,'F')
,(0,25000,2,4,0,24,'F')
,(0,25000,2,4,25,30,'F')
,(0,25000,2,4,31,55,'F')
,(0,25000,2,4,56,99,'F')
,(0,25000,5,7,0,24,'F')
,(0,25000,5,7,25,30,'F')
,(0,25000,5,7,31,55,'F')
,(0,25000,5,7,56,99,'F')
,(0,25000,8,12,0,24,'F')
,(0,25000,8,12,25,30,'F')
,(0,25000,8,12,31,55,'F')
,(0,25000,8,12,56,99,'F')
,(25001,75000,0,1,0,24,'A')
,(25001,75000,0,1,25,30,'B')
,(25001,75000,0,1,31,55,'D')
,(25001,75000,0,1,56,99,'D')
,(25001,75000,2,4,0,24,'C')
,(25001,75000,2,4,25,30,'D')
,(25001,75000,2,4,31,55,'F')
,(25001,75000,2,4,56,99,'F')
,(25001,75000,5,7,0,24,'E')
,(25001,75000,5,7,25,30,'F')
,(25001,75000,5,7,31,55,'F')
,(25001,75000,5,7,56,99,'F')
,(25001,75000,8,12,0,24,'F')
,(25001,75000,8,12,25,30,'F')
,(25001,75000,8,12,31,55,'F')
,(25001,75000,8,12,56,99,'F')
,(75001,100000,0,1,0,24,'A')
,(75001,100000,0,1,25,30,'B')
,(75001,100000,0,1,31,55,'B')
,(75001,100000,0,1,56,99,'B')
,(75001,100000,2,4,0,24,'A')
,(75001,100000,2,4,25,30,'C')
,(75001,100000,2,4,31,55,'E')
,(75001,100000,2,4,56,99,'E')
,(75001,100000,5,7,0,24,'C')
,(75001,100000,5,7,25,30,'D')
,(75001,100000,5,7,31,55,'F')
,(75001,100000,5,7,56,99,'F')
,(75001,100000,8,12,0,24,'D')
,(75001,100000,8,12,25,30,'E')
,(75001,100000,8,12,31,55,'F')
,(75001,100000,8,12,56,99,'F')
,(100001,250000,0,1,0,24,'A')
,(100001,250000,0,1,25,30,'A')
,(100001,250000,0,1,31,55,'A')
,(100001,250000,0,1,56,99,'B')
,(100001,250000,2,4,0,24,'A')
,(100001,250000,2,4,25,30,'B')
,(100001,250000,2,4,31,55,'C')
,(100001,250000,2,4,56,99,'C')
,(100001,250000,5,7,0,24,'C')
,(100001,250000,5,7,25,30,'C')
,(100001,250000,5,7,31,55,'D')
,(100001,250000,5,7,56,99,'D')
,(100001,250000,8,12,0,24,'C')
,(100001,250000,8,12,25,30,'E')
,(100001,250000,8,12,31,55,'F')
,(100001,250000,8,12,56,99,'F')
SELECT a.CustomerID
,b.Grade
FROM dbo.CustomerInfo a
LEFT JOIN dbo.GradeLookup b ON a.Income BETWEEN b.MinIncome
AND b.MaxIncome
AND a.Dependents BETWEEN b.MinDependents
AND b.MaxDependents
AND a.Age BETWEEN b.MinAge
AND b.MaxAge
You can see I'm given information about a customer and using a lookup table to return a grade that is set based on all conditions together. When looking at the execution plan, there are 320 records that come out of the dbo.GradeLookup table scan and that increases by 64 every time a new customer gets added. I mentioned this example is scaled back so it doesn't appear to be a big issue but in my environment that scan is bringing back 84,000,000+ records on a query that ultimately results in about 500,000 records. Is there a better way to write this query so that it results in better estimates and performance?
Purely from a performance perspective, I think you'd be better off using another table -- CustomerRanges -- to reduce each range to a single number for the final lookup in a redesigned table -- GradeLookupNew -- similar to below.
DROP TABLE dbo.CustomerInfo;
DROP TABLE dbo.GradeLookup;
DROP TABLE dbo.CustomerRanges;
DROP TABLE dbo.GradeLookupNew;
GO
CREATE TABLE dbo.CustomerInfo (
CustomerID INT NOT NULL
,Income DECIMAL(10, 2) NOT NULL
,Dependents INT NOT NULL
,Age INT NOT NULL
)
INSERT INTO dbo.CustomerInfo
VALUES (1, 50000, 2, 29)
,(2, 125000, 4, 52)
,(3, 20000, 0, 18)
,(4, 98000, 5, 31)
,(5, 47000, 3, 66)
CREATE TABLE dbo.GradeLookup (
MinIncome DECIMAL(10, 2)
,MaxIncome DECIMAL(10, 2)
,MinDependents INT
,MaxDependents INT
,MinAge INT
,MaxAge INT
,Grade VARCHAR(1)
)
INSERT INTO dbo.GradeLookup
VALUES (0,25000,0,1,0,24,'F')
,(0,25000,0,1,25,30,'F')
,(0,25000,0,1,31,55,'F')
,(0,25000,0,1,56,99,'F')
,(0,25000,2,4,0,24,'F')
,(0,25000,2,4,25,30,'F')
,(0,25000,2,4,31,55,'F')
,(0,25000,2,4,56,99,'F')
,(0,25000,5,7,0,24,'F')
,(0,25000,5,7,25,30,'F')
,(0,25000,5,7,31,55,'F')
,(0,25000,5,7,56,99,'F')
,(0,25000,8,12,0,24,'F')
,(0,25000,8,12,25,30,'F')
,(0,25000,8,12,31,55,'F')
,(0,25000,8,12,56,99,'F')
,(25001,75000,0,1,0,24,'A')
,(25001,75000,0,1,25,30,'B')
,(25001,75000,0,1,31,55,'D')
,(25001,75000,0,1,56,99,'D')
,(25001,75000,2,4,0,24,'C')
,(25001,75000,2,4,25,30,'D')
,(25001,75000,2,4,31,55,'F')
,(25001,75000,2,4,56,99,'F')
,(25001,75000,5,7,0,24,'E')
,(25001,75000,5,7,25,30,'F')
,(25001,75000,5,7,31,55,'F')
,(25001,75000,5,7,56,99,'F')
,(25001,75000,8,12,0,24,'F')
,(25001,75000,8,12,25,30,'F')
,(25001,75000,8,12,31,55,'F')
,(25001,75000,8,12,56,99,'F')
,(75001,100000,0,1,0,24,'A')
,(75001,100000,0,1,25,30,'B')
,(75001,100000,0,1,31,55,'B')
,(75001,100000,0,1,56,99,'B')
,(75001,100000,2,4,0,24,'A')
,(75001,100000,2,4,25,30,'C')
,(75001,100000,2,4,31,55,'E')
,(75001,100000,2,4,56,99,'E')
,(75001,100000,5,7,0,24,'C')
,(75001,100000,5,7,25,30,'D')
,(75001,100000,5,7,31,55,'F')
,(75001,100000,5,7,56,99,'F')
,(75001,100000,8,12,0,24,'D')
,(75001,100000,8,12,25,30,'E')
,(75001,100000,8,12,31,55,'F')
,(75001,100000,8,12,56,99,'F')
,(100001,250000,0,1,0,24,'A')
,(100001,250000,0,1,25,30,'A')
,(100001,250000,0,1,31,55,'A')
,(100001,250000,0,1,56,99,'B')
,(100001,250000,2,4,0,24,'A')
,(100001,250000,2,4,25,30,'B')
,(100001,250000,2,4,31,55,'C')
,(100001,250000,2,4,56,99,'C')
,(100001,250000,5,7,0,24,'C')
,(100001,250000,5,7,25,30,'C')
,(100001,250000,5,7,31,55,'D')
,(100001,250000,5,7,56,99,'D')
,(100001,250000,8,12,0,24,'C')
,(100001,250000,8,12,25,30,'E')
,(100001,250000,8,12,31,55,'F')
,(100001,250000,8,12,56,99,'F')
CREATE TABLE dbo.CustomerRanges (
RangeID TINYINT NOT NULL
,RangeType CHAR(1) NOT NULL
,MinValue sql_variant NOT NULL
,MaxValue sql_variant NOT NULL
)
INSERT INTO dbo.CustomerRanges VALUES
(01, 'I', CAST(0.00 AS decimal(9, 2)), CAST(25000.00 AS decimal(9, 2))),
(02, 'I',CAST(25001.00 AS decimal(9, 2)), CAST(75000.00 AS decimal(9, 2))),
(03, 'I',CAST(75001.00 AS decimal(9, 2)), CAST(100000.00 AS decimal(9, 2))),
(04, 'I',CAST(100001.00 AS decimal(9, 2)) ,CAST(250000.00 AS decimal(9, 2))),
(11, 'D', CAST(0 AS int), 1),
(12, 'D', CAST(2 AS int), CAST(4 AS int)),
(13, 'D', CAST(5 AS int), CAST(7 AS int)),
(14, 'D', CAST(8 AS int), CAST(12 AS int)),
(21, 'A', CAST(0 AS int), CAST(24 AS int)),
(22, 'A', CAST(25 AS int), CAST(30 AS int)),
(23, 'A', CAST(31 AS int), CAST(55 AS int)),
(24, 'A', CAST(56 AS int), CAST(99 AS int))
CREATE TABLE dbo.GradeLookupNew (
RangeID_Income tinyint NOT NULL,
RangeID_Dependents tinyint NOT NULL,
RangeID_Age tinyint NOT NULL,
Grade CHAR(1) NOT NULL
)
INSERT INTO dbo.GradeLookupNew
SELECT CR_I.RangeID, CR_D.RangeID, CR_A.RangeID, GL.Grade
FROM dbo.GradeLookup GL
INNER JOIN dbo.CustomerRanges CR_I ON CR_I.RangeType = 'I' AND CR_I.MinValue = GL.MinIncome AND CR_I.MaxValue = GL.MaxIncome
INNER JOIN dbo.CustomerRanges CR_D ON CR_D.RangeType = 'D' AND CR_D.MinValue = GL.MinDependents AND CR_D.MaxValue = GL.MaxDependents
INNER JOIN dbo.CustomerRanges CR_A ON CR_A.RangeType = 'A' AND CR_A.MinValue = GL.MinAge AND CR_A.MaxValue = GL.MaxAge
SELECT *
FROM dbo.GradeLookupNew
SELECT CI.CustomerID
,GL.Grade
FROM dbo.CustomerInfo CI
LEFT JOIN dbo.CustomerRanges CR_I ON CR_I.RangeType = 'I'
AND CI.Income BETWEEN CR_I.MinValue AND CR_I.MaxValue
LEFT JOIN dbo.CustomerRanges CR_D ON CR_D.RangeType = 'D'
AND CI.Dependents BETWEEN CR_D.MinValue AND CR_D.MaxValue
LEFT JOIN dbo.CustomerRanges CR_A ON CR_A.RangeType = 'A'
AND CI.Age BETWEEN CR_A.MinValue AND CR_A.MaxValue
LEFT JOIN dbo.GradeLookupNew GL ON GL.RangeID_Income = CR_I.RangeID
AND GL.RangeID_Dependents = CR_D.RangeID
AND GL.RangeID_Age = CR_A.RangeID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 14, 2021 at 12:33 pm
I just had a chance to get back to this and I can tell you it made a huge difference. Thanks, Scott!
October 14, 2021 at 5:10 pm
You're welcome. I'm very glad it helped. And thanks for the follow-up and nice feedback!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply