April 7, 2016 at 7:59 pm
What you actually search for is a string pattern within a number represented as a string using decimal notation.
Therefore you'll be better off having PartNumber stored as a string and using a varchar variable as a search pattern.
_____________
Code for TallyGenerator
April 7, 2016 at 8:11 pm
IF OBJECT_ID('tempdb..#PartsList', N'U') IS NOT NULL
BEGIN
DROP TABLE #PartsList;
END;
GO
CREATE TABLE #PartsList
(
PartId INT IDENTITY(1,1) NOT NULL
, CategoryId INT NOT NULL
, PartNumber INT NOT NULL CHECK (PartNumber > 0 AND PartNumber < 1000000)
, PartNumber1 AS CONVERT(CHAR(6), PartNumber)
, PartNumber2 AS SUBSTRING(CONVERT(CHAR(6), PartNumber), 2,6)
, PartNumber3 AS SUBSTRING(CONVERT(CHAR(6), PartNumber), 3,6)
, PartNumber4 AS SUBSTRING(CONVERT(CHAR(6), PartNumber), 4,6)
, PartNumber5 AS SUBSTRING(CONVERT(CHAR(6), PartNumber), 5,6)
, PartNumber6 AS SUBSTRING(CONVERT(CHAR(6), PartNumber), 6,6)
, UTCDateCreated DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME())
--, PRIMARY KEY CLUSTERED (PartId)
, PRIMARY KEY NONCLUSTERED (PartId)
, UNIQUE CLUSTERED (CategoryId, PartNumber)
);
--CREATE UNIQUE NONCLUSTERED INDEX uxParts
--ON #PartsList (CategoryId, PartNumber);
CREATE NONCLUSTERED INDEX ixPartNumber1 ON #PartsList (PartNumber1);
CREATE NONCLUSTERED INDEX ixPartNumber2 ON #PartsList (PartNumber2);
CREATE NONCLUSTERED INDEX ixPartNumber3 ON #PartsList (PartNumber3);
CREATE NONCLUSTERED INDEX ixPartNumber4 ON #PartsList (PartNumber4);
CREATE NONCLUSTERED INDEX ixPartNumber5 ON #PartsList (PartNumber5);
CREATE NONCLUSTERED INDEX ixPartNumber6 ON #PartsList (PartNumber6);
GO
Population script is the same - new computed columns are not involved.
Here is a common part for different search queries:
DECLARE @PartialPartNumber INT = 123;
DECLARE @SearchString VARCHAR(6);
DECLARE @CategoryId INT = 2;
SET @SearchString = CONVERT(VARCHAR(6), @PartialPartNumber) + '%'
And now let's compare our options:
SELECT pl.PartId , pl.CategoryId, pl.PartNumber --, s.*
FROM #PartsList AS pl
WHERE (PartNumber1 LIKE @SearchString
OR (LEN(@SearchString)<=6 AND PartNumber2 LIKE @SearchString)
OR (LEN(@SearchString)<=5 AND PartNumber3 LIKE @SearchString)
OR (LEN(@SearchString)<=4 AND PartNumber4 LIKE @SearchString)
OR (LEN(@SearchString)<=3 AND PartNumber5 LIKE @SearchString)
OR (LEN(@SearchString)<=2 AND PartNumber6 LIKE @SearchString)
)
--AND pl.CategoryId = @CategoryId
ORDER BY pl.PartNumber
Table '#PartsList__________________________________________________________________________________________________________00000000001A'. Scan count 1, logical reads 1956, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 967 ms, elapsed time = 1346 ms.
Next one selects only clustered index columns, which are included into every index, so there is not need for bookmark look-ups, so seeking NC indexes is more effective that scanning CI.
For the full set of columns I join the whole original table using WHERE EXISTS:
SELECT pl.*
FROM #PartsList pl
WHERE EXISTS (
SELECT *
FROM #PartsList AS pls
WHERE (PartNumber1 LIKE @SearchString
OR (LEN(@SearchString)<=6 AND PartNumber2 LIKE @SearchString)
OR (LEN(@SearchString)<=5 AND PartNumber3 LIKE @SearchString)
OR (LEN(@SearchString)<=4 AND PartNumber4 LIKE @SearchString)
OR (LEN(@SearchString)<=3 AND PartNumber5 LIKE @SearchString)
OR (LEN(@SearchString)<=2 AND PartNumber6 LIKE @SearchString)
)
AND pl.CategoryId = pls.CategoryId AND pl.PartNumber = pls.PartNumber
--AND pl.CategoryId = @CategoryId
)
ORDER BY pl.PartNumber
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#PartsList__________________________________________________________________________________________________________00000000001A'. Scan count 5, logical reads 3809, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 600 ms.
And, to compare apples to apples, the query from your post:
SELECT pl.PartId, pl.CategoryId, pl.PartNumber --, s.*
FROM #PartsList AS pl
CROSS APPLY ( -- TOP(LEN(pl.PartNumber)) and WHERE n.N <= (LEN(pl.PartNumber) - LEN(@PartialPartNumber) +1)
-- required to prevent DivideByZero errors
SELECT TOP (LEN(pl.PartNumber))
MatchThis = pl.PartNumber % POWER(10, LEN(pl.PartNumber) -n.N +1) / POWER(10, LEN(pl.PartNumber) -LEN(@PartialPartNumber) -n.N +1 )
--, MatchThisLength = LEN(@PartialPartNumber)
--, MatchThisStart = n.N
FROM (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS n(N)
WHERE n.N <= (LEN(pl.PartNumber) - LEN(@PartialPartNumber) +1)
AND pl.PartNumber % POWER(10, LEN(pl.PartNumber) -n.N +1) / POWER(10, LEN(pl.PartNumber) -LEN(@PartialPartNumber) -n.N +1 ) = @PartialPartNumber
) AS s
--WHERE pl.CategoryId = @CategoryId
ORDER BY PartNumber ;
Table '#PartsList__________________________________________________________________________________________________________00000000001A'. Scan count 1, logical reads 1956, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4478 ms, elapsed time = 4616 ms.
Almost 8 times faster - there's something to consider.
_____________
Code for TallyGenerator
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply