question

jhowe avatar image
jhowe asked

Most efficient way of doing customer search stored proc

Update : i've pasted the code without the ISNULL which seems to be faster. Update : i've come up with two different approaches for doing this i will edit my original code. Please let me know what is a better approach and if both are correct etc... Hi all, I've been tasked with creating a customer search stored proc that has the following fields. FirstName LastName City Postcode Country Name DateOfBirth Obviously for the user i would like them to search by ANY or ALL fields and have the wildcards bring back similar records i.e. search LastName Smith would bring back Smiths, Smithy, etc. No fields are mandatory. I'm struggling a bit with the DOB. The DOB field is datetime in the database how would i search for a partial DOB like 12/ or 2012/ for example? This is what i've come up with so far... DECLARE @FirstName NVARCHAR(50) = '', @LastName NVARCHAR(50) = '', @City NVARCHAR(50) = '', @Postcode NVARCHAR(10) = '', @Country AS NVARCHAR(2) = '', @Name AS NVARCHAR(50) = '', @DateOfBirth AS NVARCHAR(12) = '' SELECT TOP 100 CU.FirstName , CU.LastName , CU.City , CU.Postcode , CO.Name , CL.CompanyID , COALESCE(CN.Country, 'Unknown') AS Country , CU.CustomerID , CASE WHEN CU.PrimaryCustomer = '1' THEN 'Yes' ELSE 'No' END AS [Primary] , CU.MiddleName , COALESCE(REPLACE(CONVERT(NVARCHAR, DateOfBirth, 103), '/', '-'), 'Unknown') AS DateOfBirth , CASE WHEN CU.Active = '1' THEN 'Active' WHEN CU.Active = '0' THEN 'Inactive' ELSE 'Unknown' END AS ActiveStatus FROM crm.dbo.Customer CU JOIN crm.dbo.CompanyLevel CL ON cl.CompanyLevelID = cu.CompanyLevelID JOIN crm.dbo.Company CO ON co.CompanyID = cl.CompanyID LEFT OUTER JOIN dbo.Country CN ON CN.CountryCode = CU.Country WHERE ( @FirstName = '' OR FirstName LIKE @FirstName + '%' ) AND ( @LastName = '' OR LastName LIKE @LastName + '%' ) AND ( @City = '' OR City LIKE @City + '%' ) AND ( @PostCode = '' OR Postcode LIKE @PostCode + '%' ) AND ( @Country = '' OR CU.Country LIKE @Country + '%' ) AND ( @Name = '' OR CO.Name LIKE @Name + '%' ) AND ( @DateOfBirth = '' OR REPLACE(CONVERT(NVARCHAR, DateOfBirth, 103), '/', '-') = @DateOfBirth ) Please help! I'm doing this to fix the current search which isn't working properly so is fairly urgent! Thanks.
sql-server-2008sql-server-2008-r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
@jhowe if you are looking to cope with ANY or ALL fields, then your procedure is not catering this scenario with all the ANDs in there? As far as date search is concerned, if you are sure that the datepart would be dd-mm-yyyy, then there should also be assurance that the current stored format of the searched DOB column be dd-mm-yyyy as well. One option could be to search like ( SUBSTRING(DateOfBirth,1,2) = SUBSTRING(@DateOfBirth, 1, 2) OR -- COULD BE AND SUBSTRING(DateOfBirth,4,2) = SUBSTRING(@DateOfBirth, 3, 2) OR -- COULD BE AND SUBSTRING(DateOfBirth,7,4) = SUBSTRING(@DateOfBirth, 7, 4) ) Another option could be (Does not seems efficient though but search for more records) ( SUBSTRING(DateOfBirth,1,2) = SUBSTRING(@DateOfBirth, 1, 2) OR -- COULD BE AND SUBSTRING(DateOfBirth,1,2) = SUBSTRING(@DateOfBirth, 3, 2) OR -- COULD BE AND SUBSTRING(DateOfBirth,1,2) = SUBSTRING(@DateOfBirth, 9, 2) ) OR ( SUBSTRING(DateOfBirth,3,2) = SUBSTRING(@DateOfBirth, 1, 2) OR -- COULD BE AND SUBSTRING(DateOfBirth,3,2) = SUBSTRING(@DateOfBirth, 3, 2) OR -- COULD BE AND SUBSTRING(DateOfBirth,3,2) = SUBSTRING(@DateOfBirth, 9, 2) ) And same for the year part. But its all guesswork. You need to sit down with the stake holders and should come up with what could be the scenarios and how they need to be dealt with. EDIT: Assuming if any field is not meant to be searched, then the respective parameter would be NULL WHERE ( ISNULL(CU.FirstName, '') LIKE ( @FirstName + '%' ) OR @FirstName IS NULL) AND (ISNULL(CU.LastName, '') LIKE ( @LastName + '%' ) OR @LastName IS NULL) AND (ISNULL(CU.City, '') LIKE ( @City + '%' ) OR @City IS NULL) AND (ISNULL(CU.Postcode, '') LIKE ( @Postcode + '%' ) OR Postcode IS NULL) AND (ISNULL(CU.Country, '') LIKE ( @Country + '%' ) OR @Country IS NULL) AND (ISNULL(CO.Name, '') LIKE ( @Name + '%' ) OR @Name IS NULL) AND (ISNULL(CONVERT(NVARCHAR, DateOfBirth, 110), '') = (@dateofbirth) OR @dateofbirth IS NULL) EDIT1: Is it what you want ( ( @FirstName IS NULL OR FirstName LIKE @FirstName + '%' ) OR ( FirstName IS NULL AND @FirstName IS NOT NULL ) ) AND ( ( @LastName IS NULL OR LastName LIKE @LastName + '%' ) OR ( LastName IS NULL AND @LastName IS NOT NULL ) ) AND ( ( @City IS NULL OR City LIKE @City + '%' ) OR ( City IS NULL AND @City IS NOT NULL ) ) AND ( ( @PostCode IS NULL OR Postcode LIKE @PostCode + '%' ) OR ( PostCode IS NULL AND @Postcode IS NOT NULL ) ) AND ( ( @Country IS NULL OR Country LIKE @Country + '%' ) OR ( Country IS NULL AND @Country IS NOT NULL ) ) AND ( ( @Name IS NULL OR Name LIKE @Name + '%' ) OR ( Name IS NULL AND @Name IS NOT NULL ) ) AND ( ( @DateOfBirth IS NULL OR REPLACE(CONVERT(NVARCHAR, DateOfBirth, 103), '/', '-') = @DateOfBirth ) OR ( DateOfBirth IS NULL AND @DateOfBirth IS NOT NULL ) )
10 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
You're right USMAN. As the DOB is in a fixed format i have updated my question with the correct format the app needs. It is 110 style. You're also correct about AND not working. Replacing AND with OR doesn't work either. I need some sort of combination of the both? The app also has validation on it so that the full date in that format MUST be entered.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
@jhowe I have edited my answer for AND/OR thing.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
Hi Usman, I've posted two different queries can you let me know which one is best?
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
@jhowe That you have to test as it would depend. But I do not like COALESCE unless it is inevitable. Use ISNULL instead, that could be much faster. For e.g. CU.FirstName LIKE ISNULL(@FirstName + '%', CU.FirstName) Hope it helps.
0 Likes 0 ·
jhowe avatar image jhowe commented ·
@Usman i have posted my finished code. Please let me know if there is any reason why i should use one or the other technically...
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered
TBH I wouldn't try and do all this in one stored procedure. Can you push back on the application development team and see if they 'know' at search time what field is being passed through - that way you can tailor a sp for each 'type' of search. (This will also alleviate any bad plan issues cause by parameter sniffing) Then for DoB searches, the UI can give a date picker (with wildcards) instead of a string input. You would then have a segmented date coming through and can easily search using `datepart()`
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
@Kev Riley Excellent advice. +1.
1 Like 1 ·
jhowe avatar image jhowe commented ·
Hi Kev, Unfortunately the app is outsourced and changes have all been released etc. so i just have to make it work. The database is only a few gig, couple million records so it doesn't have to be massively efficient/complicated...
0 Likes 0 ·
jhowe avatar image jhowe commented ·
I've just found out that the DOB search input field has to be in format dd-mm-yyyy and it has a calendar picker... so that might make it a bit easier... They also have the option to manually enter a date.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
if the format is dd-mm-yyyy and comes from a picker then there should be no option to search for a partial date then?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
and sorry - just reread the question - being able to cope with ANY or ALL fields - scrub the suggestion about a tailored sp!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.