Like Statement With Parameters
-
I have a table :
Person(Id bigint , FirstName nvarchar(50) , LastName nvarchar(50),Address nvarchar(100))
and I have a sp for search data in "Person" table:
CREATE PROCEDURE SelectPerson
@First nvarchar(50) = NULL,
@Last nvarchar(50) = NULL,
@Address nvarchar(100) = NULL
AS
BEGIN
SELECT * FROM Person WHERE ([FirstName] LIKE N'%' + @First +'%' OR @First IS NULL) AND
([LastName] LIKE N'%' + @Last +'%' OR @Last IS NULL) AND
([Address ] LIKE N'%' + @Address +'%' OR @Address IS NULL)
END
GO
and there exists 500 record in Person.
I execute SelectPerson by this code:
"
DECLARE@return_value int
EXEC@return_value = [dbo].[SelectPerson ]
@First = 'Ψ§Ω ',
@Last = NULL,
@Address= NULL
SELECT'Return Value' = @return_value
GO
"
but result don't has any rows.why?
-
You have not qualified your string on the @firstname as nvarchar when passing it to the stored proc
EXEC@return_value = [dbo].[SelectPerson ]
@First = N'??',
@Last = NULL,
@Address= NULL
Also take a look at this link for catch all queries http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
-
Here is a quick solution that fits nicely for simple usage.
π
Test data set for demonstration
USE TEEST;/* Safe place to play in */
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Person') IS NOT NULL DROP TABLE dbo.Person;
CREATE TABLE dbo.Person
(
Id BIGINT NOT NULL
,FirstName NVARCHAR(50) NOT NULL
,LastName NVARCHAR(50) NOT NULL
,Address NVARCHAR(100) NOT NULL
);
DECLARE @SAMPLE_SIZE INT = 10000;
DECLARE @BASE_DATE DATETIME = CONVERT(DATETIME,'20150101',112);
;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
, NUMS(N) AS (SELECT TOP (@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,NAME_STRING(SXNAME) AS
(
SELECT
(SELECT
REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
SM.text
,NCHAR(37),N''),NCHAR(59),N''),NCHAR(32),N''),NCHAR(61),N''),NCHAR(39),N''),NCHAR(40),N''),NCHAR(45),N''),NCHAR(41),N''),NCHAR(46),N''),NCHAR(47),N''),NCHAR(44),N''),NCHAR(42),N''),NCHAR(58),N''),NCHAR(95),N''),NCHAR(34),N''),NCHAR(64),N'')
FROM sys.messages SM
WHERE SM.language_id = 1033
FOR XML PATH(''), TYPE).value('.[1]','NVARCHAR(4000)') AS SXNAME
)
,BASE_DATA AS
(
SELECT
NCHAR(65 + ABS(CHECKSUM(NEWID())) % 25)
+ LOWER(SUBSTRING(NS.SXNAME,ABS(CHECKSUM(NEWID())) % 3800,5 + ABS(CHECKSUM(NEWID())) % 10)) AS FirstName
,NCHAR(65 + ABS(CHECKSUM(NEWID())) % 25)
+ LOWER(SUBSTRING(NS.SXNAME,ABS(CHECKSUM(NEWID())) % 3800,5 + ABS(CHECKSUM(NEWID())) % 10)) AS LastName
,NCHAR(65 + ABS(CHECKSUM(NEWID())) % 25)
+ LOWER(SUBSTRING(NS.SXNAME,ABS(CHECKSUM(NEWID())) % 3800,7 + ABS(CHECKSUM(NEWID())) % 10)) + NCHAR(32) + CONVERT(NVARCHAR(3),1 + (ABS(CHECKSUM(NEWID())) % 100),0) AS Address
FROM NUMS NM
CROSS APPLY NAME_STRING NS
)
INSERT INTO dbo.Person (Id,FirstName,LastName,Address)
SELECT
ROW_NUMBER() OVER
(
ORDER BY (SELECT NULL)
) AS Id
,BD.FirstName
,BD.LastName
,BD.Address
FROM BASE_DATA BD
;
CREATE NONCLUSTERED INDEX DBO_PERSON_FIRSTNAME_LASTNAME_ADDRESS_INCL_ID ON dbo.Person (FirstName ASC,LastName ASC,Address ASC) INCLUDE (Id);
Search procedure
CREATE PROCEDURE dbo.USP_SEARCH_PERSON_BY_OPTIONAL_PARAMETERS(
@First nvarchar(50) = NULL
,@LastName nvarchar(50) = NULL
,@Address nvarchar(100) = NULL
)
AS
SELECT
PE.Id
,PE.FirstName
,PE.LastName
,PE.Address
FROM dbo.Person PE
WHERE PE.FirstName LIKE NCHAR(37) + ISNULL(@First ,N'') + NCHAR(37)
AND PE.LastName LIKE NCHAR(37) + ISNULL(@LastName,N'') + NCHAR(37)
AND PE.Address LIKE NCHAR(37) + ISNULL(@Address ,N'') + NCHAR(37)
;
Graphical Execution Plan representation
SHOWPLAN_TEXT Output
StmtText----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Nested Loops(Inner Join, OUTER REFERENCES: ([Expr1015], [Expr1016], [Expr1017]))
|--Compute Scalar(DEFINE: ([Expr1015]=LikeRangeStart(N'%'+isnull([@First],N'')+N'%'), [Expr1016]=LikeRangeEnd(N'%'+isnull([@First],N'')+N'%'), [Expr1017]=LikeRangeInfo(N'%'+isnull([@First],N'')+N'%')))
| |--Constant Scan
|--Index Seek(OBJECT: ([TEEST].[dbo].[Person].[DBO_PERSON_FIRSTNAME_LASTNAME_ADDRESS_INCL_ID] AS [PE]), SEEK: ([PE].[FirstName] > [Expr1015] AND [PE].[FirstName] < [Expr1016]), WHERE: ([TEEST].[dbo].[Person].[FirstName] as [PE].[FirstName] like N
-
Eirikur Eiriksson (10/1/2015)
Here is a quick solution that fits nicely for simple usage.π
Your plan shows a seek for a double-wildcarded parameter...must play with this at home. Thanks for posting, Eirikur.
βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (10/1/2015)
Eirikur Eiriksson (10/1/2015)
Here is a quick solution that fits nicely for simple usage.π
Your plan shows a seek for a double-wildcarded parameter...must play with this at home. Thanks for posting, Eirikur.
Hi Chris,
Appears that not all scans are made equal, some are more so than others (1944), interesting behavior though.
Digging into this a little, there are possibly some gains in using binary collation, here is a query peaking under the bonnet
π
DECLARE @First nvarchar(50) = N'va';DECLARE @LastName nvarchar(50) = N'st';
DECLARE @Address nvarchar(100) = N'th%1';
SELECT
PE.Id
,PE.FirstName
,PE.LastName
,PE.Address
FROM dbo.Person PE
WHERE PE.FirstName LIKE NCHAR(37) + ISNULL(@First ,N'') + NCHAR(37)
AND PE.LastName LIKE NCHAR(37) + ISNULL(@LastName,N'') + NCHAR(37)
AND PE.Address LIKE NCHAR(37) + ISNULL(@Address ,N'') + NCHAR(37)
OPTION (
RECOMPILE
,QUERYTRACEON 3604
,QUERYTRACEON 8607
)
;
Obviously the test set is somewhat naive, any suggestions there would be appreciated.
-
Eirikur Eiriksson (10/1/2015)
ChrisM@Work (10/1/2015)
Eirikur Eiriksson (10/1/2015)
Here is a quick solution that fits nicely for simple usage.π
Your plan shows a seek for a double-wildcarded parameter...must play with this at home. Thanks for posting, Eirikur.
Hi Chris,
Appears that not all scans are made equal, some are more so than others (1944), interesting behavior though.
Digging into this a little, there are possibly some gains in using binary collation, here is a query peaking under the bonnet
π
DECLARE @First nvarchar(50) = N'va';DECLARE @LastName nvarchar(50) = N'st';
DECLARE @Address nvarchar(100) = N'th%1';
SELECT
PE.Id
,PE.FirstName
,PE.LastName
,PE.Address
FROM dbo.Person PE
WHERE PE.FirstName LIKE NCHAR(37) + ISNULL(@First ,N'') + NCHAR(37)
AND PE.LastName LIKE NCHAR(37) + ISNULL(@LastName,N'') + NCHAR(37)
AND PE.Address LIKE NCHAR(37) + ISNULL(@Address ,N'') + NCHAR(37)
OPTION (
RECOMPILE
,QUERYTRACEON 3604
,QUERYTRACEON 8607
)
;
Obviously the test set is somewhat naive, any suggestions there would be appreciated.
Fascinating. Unfortunately I can't play to this extent here at work, but Saturday is unusually free of activity so far.
βWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.β - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Eirikur - I'm with Chris on this one... VERY slick solution.
I have to confess that I'm baffled by optimizer's ability to do an index seek with leading wildcards. That really is a beautiful thing!
Going to have to play around with this myself.
-
Jason A. Long (10/3/2015)
Eirikur - I'm with Chris on this one... VERY slick solution.I have to confess that I'm baffled by optimizer's ability to do an index seek with leading wildcards. That really is a beautiful thing!
Going to have to play around with this myself.
Jason, yes it looks intriguing but mind you that not all seeks are created equal;-)
π
-
Eirikur Eiriksson (10/3/2015)
Jason A. Long (10/3/2015)
Eirikur - I'm with Chris on this one... VERY slick solution.I have to confess that I'm baffled by optimizer's ability to do an index seek with leading wildcards. That really is a beautiful thing!
Going to have to play around with this myself.
Jason, yes it looks intriguing but mind you that not all seeks are created equal;-)
π
You're right, I did a little more playing around (no serious, timed, testing against a big dataset) and found that the code is tricking the optimizer into thinking it can do a seek, when it should be doing a scan.
Since seeking when it should be scanning is potentially worse than scanning when it should be seeking, my hopes are a wee bit dashed... (Only more testing will tell)
Adding OPTION(RECOMPILE) to the script shows that the optimizer, will in fact, opt to do a scan if given the opportunity know that the appended NCHARS are really wildcards at compile time.
I also played around with the syntax a little bit... I wanted the option to choose weather or not to use wildcards when setting the parameters rather than having them hard coded...
DECLARE
@FirstName NVARCHAR(50) = '%r%',
@LastName NVARCHAR(50) = '%k%o%',
@Address NVARCHAR(100) = NULL;
SELECT
PE.Id,
PE.FirstName,
PE.LastName,
PE.Address
FROM
dbo.Person PE
WHERE
PE.FirstName LIKE (ISNULL(@FirstName, N'%'))
AND PE.LastName LIKE (ISNULL(@LastName, N'%'))
AND PE.Address LIKE (ISNULL(@Address, N'%'))
OPTION(RECOMPILE)
;
It produces the same execution plan as the original but with the option to choose weather or not to search with wildcards.
(you'll need to comment out the OPTION(RECOMPILE) to actually get the same plan...)
I'll see if I can get some speed numbers together later today.
-
Alright... So instead of getting up a cutting the grass, I decided to do a little testing this morning...
For a test bench I used 1M rows generated by the script that Eirikur posted earlier. (Thanks for that too Eirikur :-))
I tested 3 versions of the code, all of which produced identical results for a given set of parameter values.
V1: Eirikur's original code posed earlier that got me interested in the 1st place.
DECLARE
@FirstName NVARCHAR(50) = 'r',
@LastName NVARCHAR(50) = 'k%o',
@Address NVARCHAR(100) = NULL,
@st DATETIME2(7);
SET @st = SYSDATETIME();
SELECT
PE.Id,
PE.FirstName,
PE.LastName,
PE.Address
FROM
dbo.Person PE
WHERE
PE.FirstName LIKE NCHAR(37) + ISNULL(@FirstName, N'') + NCHAR(37)
AND PE.LastName LIKE NCHAR(37) + ISNULL(@LastName, N'') + NCHAR(37)
AND PE.Address LIKE NCHAR(37) + ISNULL(@Address, N'') + NCHAR(37)
OPTION(RECOMPILE)
;
SELECT DATEDIFF(ms, @st, SYSDATETIME()) AS ExecTime;
V2: A variant of Eirikur's code what doesn't hard code the wildcards
DECLARE
@FirstName NVARCHAR(50) = '%r%',
@LastName NVARCHAR(50) = '%k%o%',
@Address NVARCHAR(100) = NULL,
@st DATETIME2(7);
SET @st = SYSDATETIME();
SELECT
PE.Id,
PE.FirstName,
PE.LastName,
PE.Address
FROM
dbo.Person PE
WHERE
PE.FirstName LIKE (ISNULL(@FirstName, N'%'))
AND PE.LastName LIKE (ISNULL(@LastName, N'%'))
AND PE.Address LIKE (ISNULL(@Address, N'%'))
OPTION(RECOMPILE)
;
SELECT DATEDIFF(ms, @st, SYSDATETIME()) AS ExecTime
V3: This is the old school way of doing optional parameters that usually takes flack for forcing index scans when not used with OPTION(RECOMPILE).
DECLARE
@FirstName NVARCHAR(50) = '%r%',
@LastName NVARCHAR(50) = '%k%o%',
@Address NVARCHAR(100) = NULL,
@st DATETIME2(7);
SET @st = SYSDATETIME()
SELECT
PE.Id,
PE.FirstName,
PE.LastName,
PE.Address
FROM
dbo.Person PE
WHERE
(PE.FirstName LIKE (@FirstName) OR @FirstName IS NULL)
AND (PE.LastName LIKE (@LastName) OR @LastName IS NULL)
AND (PE.Address LIKE (@Address) OR @Address IS NULL)
OPTION(RECOMPILE)
;
SELECT DATEDIFF(ms, @st, SYSDATETIME()) AS ExecTime
Each of the 3 versions was tested with & without OPTION(RECOMPILE) and with and without the leading wildcard (so, 4 tests per version).
All 12 execution plans are included in the attached zip file...
Just as an FYI... The leading wildcard versions each returned 9,151 rows and the non-leading wildcard versions each returned 688 rows.
The results are a follows... (all times are in milliseconds)
Leading wildcard...
V1 w RCV1 wo RCV2 w RCV2 wo RCV3 w RCV3 wo RC
1304941284940302296
2295946297920314312
3313963298949311314
4297939301935302307
5313951297938327314
6300963315935289314
7292952324925295306
8312950312923315290
9298945303922304286
10326952326964306311
Avgs305950.2305.7935.1306.5305
Without the leading Wildcard...
V1 w RCV1 wo RCV2 w RCV2 wo RCV3 w RCV3 wo RC
1384552495363
2355438514552
3513029293573
4313050293472
5493351352949
6343036465045
7354347375463
8354453365466
9505352395448
10524947293361
Avgs4141.145.53844.159.2
Conclusion:
When OPTION(RECOMPILE) is used, all 3 perform about the same. They are all able switch between index seeks and scans, as appropriate, based on the presence of a leading wildcard.
Note... To test V1 w/o the leading wild card it was necessary to delete "CCHAR(37) + " form the code itself...
It's when the OPTION(RECOMPILE) is removed that things get interesting...
V1 & V2 will cause the optimizer to opt for an index seek no matter if there is a leading wildcard or not.
V3 will cause the optimizer to opt for an index scan no matter if there is a leading wildcard or not.
So, in this round of testing, seeking when it should be scanning, caused the average execution times to increase by more that 300%.
On the other hand, scanning when it could be seeking caused the average execution times to increase by less than 50%.
-
Thanks Jason, nice job testing this.
π
-
Eirikur Eiriksson (10/4/2015)
Thanks Jason, nice job testing this.π
Thank you sir! π
One thing I did fail to point out that's probably worth mentioning...
It's not always desirable (or even acceptable) to do a recompile with every execution.
If that the case AND you don't plan on allowing a leading wildcard search.
(Not unreasonable considering that a user will typically know at least the 1st few letters of the string they are searching)
Something like the following will blow the doors off the "Old school V3"...
DECLARE
@FirstName NVARCHAR(50) = 'Obeingaggr',
@LastName NVARCHAR(50) = 'Ktementcontai',
@Address NVARCHAR(100) = NULL,
@st DATETIME2(7);
SELECT
@FirstName = CASE WHEN LEFT(@FirstName, 1) = N'%' THEN SUBSTRING(@FirstName, 2, 50) ELSE @FirstName END,
@LastName = CASE WHEN LEFT(@LastName, 1) = N'%' THEN SUBSTRING(@LastName, 2, 50) ELSE @LastName END,
@Address = CASE WHEN LEFT(@Address, 1) = N'%' THEN SUBSTRING(@Address, 2, 100) ELSE @Address END;
SET @st = SYSDATETIME()
SELECT
PE.Id,
PE.FirstName,
PE.LastName,
PE.Address
FROM
dbo.Person PE
WHERE
PE.FirstName LIKE(ISNULL(@FirstName, N'%'))
AND PE.LastName LIKE(ISNULL(@LastName, N'%'))
AND PE.Address LIKE(ISNULL(@Address, N'%'))
;
SELECT DATEDIFF(ms, @st, SYSDATETIME()) AS ExecTime;
In this case, the code posted above is returning a single row on less than 1 ms.
The "old school" version... (without recompile)
...
WHERE
(PE.FirstName = @FirstName OR @FirstName IS NULL)
AND (PE.LastName = @LastName OR @LastName IS NULL)
AND (PE.Address = @Address OR @Address IS NULL)
;
... Is returning the same row in 70 to 80 ms.
So I think that's worth keeping mind before taking the previous test results and writing off the V1/V2 approach...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply