Month: October 2019

The root cause of the slow search was that 90 MILLION stored postcodes were being retrieved and manipulated (to remove spaces) before being compared with ONE search input.

--- OLD CODE -----------------

DECLARE @PostCode VARCHAR(8)
SELECT Forename,
       Surname,
       AccountNumber AS CustomerNo,
       AccountStartDate,
       AddressLine2 AS Address,
       PostCode,
       DateOfBirth
FROM [dbo].[SV_Customers]
WHERE (CountryCode = 'GB')
  AND (REPLACE(Postcode, ' ', '') = @PostCode);

My insight was to manipulate just the ONE input postcode before comparing it TWICE (with and without a space) to the un-manipulated postcodes stored in the database.

The first task then, was to split the input postcode into two parts. In all formats the last 3 characters were number, letter, letter.

So after saving the last part of the postcode separately, it was easy to deduce that the first part must be the whole thing minus the last part.

--- NEW CODE -----------------

DECLARE @PostCode VARCHAR(8);
DECLARE @pc2 CHAR(3) = RIGHT(@PostCode, 3);
DECLARE @pc1 VARCHAR(4) = REPLACE(@PostCode, @pc2, '');

SELECT Forename,
       Surname,
       AccountNumber AS CustomerNo,
       AccountStartDate,
       AddressLine2 AS Address,
       Postcode,
       DateOfBirth
FROM [dbo].[SV_Customers]
WHERE CountryCode = 'GB'
  AND (PostCode = @pc1 + @pc2         -- without space
   OR  PostCode = @pc1 + ' ' + @pc2); -- or with space