An approach to optimizing search routines for big data
MS SQL Server
Attempting to improve search performance when querying on multiple fields (such as First Name, Last Name, Address, City and State) can sometimes could be a bit tricky. Performance improvements can be achieved by splitting the address into separate Street Number and Street Name fields.
Say you are looking for "109 Lupine Ln." but the search yields no result. If you enter only the Street Name as Lupine Ln. then you will have a chance to find the actual address as "106 Lupine Ln." Since you simply entered the wrong street number this approach helps locate the record you are looking for. You may also desire to use "contains" or wildcard searches to help narrow your search results when you are not able to enter an exact match.
From that reason it is highly recommended to split the address field into StreetNumer and StreetName. You will need to create these additional fields in the Address table.
StreetNumber AS VARCHAR(20) StreetName AS VARCHAR(50)
The code below will attempt to find one space between the Street Number and the Street Name in the Address filed. If found it will be spilited into 2 fields.
There could an Address like PO Box 200 or PO BOX 500 Pinon Ct and this case it will be copied into the AddressName field.
Check our Custom Software Development Services.
You could also expand you algorithms to include splitting the address further Apartment and Street Direction (N , E , S or W). In most cases Street Number and Street Name are sufficient.
BEGIN SET NOCOUNT ON; UPDATE [Address] SET StreetNumber = CASE WHEN ISNUMERIC(LEFT(LTRIM(Address), CHARINDEX(' ', LTRIM(Address)))) = 1 THEN LEFT(LTRIM(Address), CHARINDEX(' ', LTRIM(Address))) ELSE NULL END , StreetName = CASE WHEN ISNUMERIC(LEFT(LTRIM(Address), CHARINDEX(' ', LTRIM(Address)))) = 1 THEN LTRIM(RTRIM(SUBSTRING(LTRIM(Address), CHARINDEX(' ', LTRIM(Address)) + 1, LEN(LTRIM(Address))))) ELSE LTRIM(RTRIM(Address)) END END
If you have need of processing large volumes of address data you may be interested in my article Processing hundreds of millions of records got much easier where I use batch processing approach.
Go Back to Our: Main Blog
Written by: Doron Farber - The Farber Consulting Group, Inc