August 31, 2010 at 5:10 am
FYI SSC-Enthusiast is just a Forum Tag...not his/her handle.
August 31, 2010 at 5:49 am
Hello,
Lots of posts here about efficiency and COALESCE and ISNULL. The points are well taken, though that really wasn't the point of this little article.
The point was more meant to be the importance of understanding that there are almost always non-procedural ways to accomplish a query task, and that those solutions are generally more elegant in form.
I also personally think it is good for any query writer to know how to think purely mathematically, before using any syntax specially provided by the query language, such as COALESCE and ISNULL. I've seen way too many simply copy-and-paste these techniques without really understanding what was going on. Of course - if you have understanding, then by all means run with it!
As far as efficiency - in 14 years I've always found this technique to be acceptable in the realm of efficiency and (especially in the old days) was given weight over closing the potential security holes of dynamic SQL.
Of course, every scenario is different and I'm sure there are scenarios in which a different approach is preferable (there always are).
Again - I wanted to post something stressing an appreciation of fundamentalism over T-SQL in the learning process, I've always found you start with a mathematically elegant solution and can then generally increase its efficiency by taking advantage of whatever specialties the DBMS you are working in provides.
Thanks for the posts,
Tony
August 31, 2010 at 5:59 am
And I think you did a great job Tony.
[This is a paraphrase from a Teddy Roosevelt quote I have read somewhere here on SSC]
-- The glory belongs to those that are in the game not in the bleachers talking about the game.
<><
Livin' down on the cube farm. Left, left, then a right.
August 31, 2010 at 6:06 am
I don't claim to be an expert on the subject, but when faced with a query like this:
SELECT field
FROM table
WHERE (field LIKE '%A%') OR (field LIKE '%B%')
I find I get much better performance if I do this...
SELECT field
FROM table
WHERE (field LIKE '%A%')
UNION
SELECT field
FROM table
WHERE (field LIKE '%B%')
INTERSECT and EXCEPT can also be used, along with the judicious application of parentheses, to get the results you desire. It's worked quite well for me on a number of occasions, though I've never tried it with huge data sets.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
August 31, 2010 at 6:25 am
Hello together,
Despite it is a good idea using SQL in its pure descriptive nature, because it normally leads to generally better performance, one has to be aware of caveats in assuming that things are evaluated in an "intuitive order".
Neither the relatinonal nor the boolean algebra does explicitly defines an evaluation order.
In regard to this article it is assumed that the boolean subexpressions will be evaluated from right to left in an fail-fast-manner. Although this could be implemented like that it does not need to. Thus leading to unportable and/or bad performing queries. Just imagine that the default values for the positional parameters would have been changed to the empty string and the evaluation would be left-to-right. The query will do always a full scan over the possibly huge table with returning unwanted tuples.
Therefore one should never assume the natural order in SQL-Queries as long as These are not guaranteered by the DBS.
August 31, 2010 at 6:28 am
Rob Fisk (8/31/2010)
Very good points at the start of the article and I may do some refresher readin on some bits but for the specific example would something like this not be better?
CREATE PROCEDURE [spSearchPeople]@firstname nvarchar(50) = '',
@lastname nvarchar(50) = ''
AS
BEGIN
SELECT ID, FirstName, LastName FROM People
WHERE FirstName LIKE '%' + @firstname + '%'
OR LastName LIKE '%' + @lastname + '%'
END
This approach does not correctly answer the search question when @firstname and @lastname are not null.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 31, 2010 at 6:42 am
Let me start by saying that I quite agree with the initial point of the article. When confronted with a complex SQL problem you are better off thinking in Venn diagrams than logic trees. Now into the meat of the discussion:
There is a way to avoid dynamic SQL while still caching plans correctly, although most of the discussion above about cached plans ignores that the names are unqualified.
(simplified code)
[font="Courier New"]IF @FirstName IS NULL AND @LastName IS NULL
SELECT ID, FirstName, LastName FROM MyDB.dbo.People
ELSE IF @FirstName IS NULL AND @LastName IS NOT NULL
SELECT ID, FirstName, LastName FROM MyDB.dbo.People
WHERE LastName LIKE @LastName -- I usually let the caller supply wildcards
ELSE IF @FirstName IS NOT NULL AND @LastName IS NULL
SELECT ID, FirstName, LastName FROM MyDB.dbo.People
WHERE FirstName LIKE @FirstName -- I usually let the caller supply wildcards
ELSE -- NEITHER ARE NULL
SELECT ID, FirstName, LastName FROM MyDB.dbo.People
WHERE FirstName LIKE @FirstName
AND LastName LIKE @LastName [/font]
NOTE: Normally I use BEGIN and END for every conditional, even if there is only a single statement
In the past I have also checked for wild cards in the parameter and used separate queries with like and equal, but that is beyond the scope of this discussion.
So is that using sp_executesql and passing in parameters protects against most SQL injection.
August 31, 2010 at 6:48 am
opc.three (8/31/2010)
Rob Fisk (8/31/2010)
Very good points at the start of the article and I may do some refresher readin on some bits but for the specific example would something like this not be better?
CREATE PROCEDURE [spSearchPeople]@firstname nvarchar(50) = '',
@lastname nvarchar(50) = ''
AS
BEGIN
SELECT ID, FirstName, LastName FROM People
WHERE FirstName LIKE '%' + @firstname + '%'
OR LastName LIKE '%' + @lastname + '%'
END
This approach does not correctly answer the search question when @firstname and @lastname are not null.
The parameters are defaulted to an empty string rather than null.
OK, there is the front end caveat that you don't pass a parameter unless needed but given that there is no need to use ISNULL within the query.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
August 31, 2010 at 6:49 am
For those that have read this far, I'd like to suggest that you read this blog[/url] by Gail Shaw on working with this type of query (catch-all query).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 6:53 am
Toby Harman (8/30/2010)
Always good to see a firm grasp of logic being used in computing!If I may suggest a variation to this technique I have seen is to use ISNULL or COALESCE around the parameter
ALTER PROCEDURE [spSearchPeople]
@firstname nvarchar(50) = null,
@lastname nvarchar(50) = null
AS
BEGIN
SELECT ID, FirstName, LastName FROM People
WHERE
FirstName LIKE COALESCE('%' + @firstname + '%', FirstName)
END
If @firstname is NULL then the % + @firstname + % becomes NULL, so the table self matches
This should mean less confusion over indices and better query plan caching as the optimiser can determine that the column we are interested in is FirstName
The major drawback is it is less intuitive and probably T-SQL specific
The solution making use of COALESCE is more readable in my opinion however it is not logically equivalent to the solution presented in the article when the column being searched allows NULL. The article does not specify the DDL of the table being searched however many natural searches do go against columns that allow NULL, e.g. name parts, address parts, phone numbers, emails, etc.
NULL != NULL so unlike in the article when @firstname IS NULL the search criteria for First Name is short=circuited enabling columns with a NULL First Name to be returned, the solution making use of COALESCE will attempt to compare a NULL value in the table with itself resulting in that row being excluded from the resultset. Here is a re-runnable example demonstrating the point:
IF OBJECT_ID(N'tempdb..#addy') IS NOT NULL
DROP TABLE #addy
CREATE TABLE #addy
(
id INT,
firstname VARCHAR(20) NULL,
lastname VARCHAR(20) NULL
) ;
INSERT INTO #addy (id, firstname, lastname) VALUES (1,'Rick','Barnes')
INSERT INTO #addy (id, firstname, lastname) VALUES (2,'Rick',NULL)
DECLARE @firstname VARCHAR(20),
@lastname VARCHAR(20)
-- we are searching for all names with 'ic' in the first name
SELECT @firstname = 'ic',
@lastname = NULL
-- row with NULL lastname is excluded
SELECT *
FROM #addy
WHERE firstname LIKE COALESCE('%' + @firstname + '%', firstname)
AND lastname LIKE COALESCE('%' + @lastname + '%', lastname) -- NULL != NULL
-- row with NULL lastname is included
SELECT *
FROM #addy
WHERE (@firstname IS NULL OR firstname LIKE '%' + @firstname + '%')
AND (@lastname IS NULL OR lastname LIKE '%' + @lastname + '%')
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 31, 2010 at 7:00 am
Rob Fisk (8/31/2010)
opc.three (8/31/2010)
Rob Fisk (8/31/2010)
Very good points at the start of the article and I may do some refresher readin on some bits but for the specific example would something like this not be better?
CREATE PROCEDURE [spSearchPeople]@firstname nvarchar(50) = '',
@lastname nvarchar(50) = ''
AS
BEGIN
SELECT ID, FirstName, LastName FROM People
WHERE FirstName LIKE '%' + @firstname + '%'
OR LastName LIKE '%' + @lastname + '%'
END
This approach does not correctly answer the search question when @firstname and @lastname are not null.
The parameters are defaulted to an empty string rather than null.
OK, there is the front end caveat that you don't pass a parameter unless needed but given that there is no need to use ISNULL within the query.
The problem is not with the default value of the parameters it is with the boolean logic. If someone provide Jim for @firstname and Thorpe for @lastname your query will incorrectly return people with the name Jim Smith, Jim Williams, etc.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 31, 2010 at 7:02 am
I'd think (from a performance standpoint) using LIKE is the *last* thing you'd ever want to do. Especially the "Like %X%" construction, since at best it's going to do an index scan.
"LIKE X%" performs adequately since it lets the query optimizer at least limit the scan to a subset of the index, but both "LIKE %X%" and "LIKE %X" are horrible performance killers.
August 31, 2010 at 7:04 am
August 31, 2010 at 7:04 am
Here is another link from Gail Shaw relevant to this thread: http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
People, if you use this type of construct either do it with one of the 3 mechanisms covered by Gail or take my business card because performance will be "suboptimal". 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2010 at 7:05 am
It is nice to see people thinking outside the box. This was a short but nice article. As a sidenote another way of conditionally peforming a where is to utilize Not Null on the variables. I do this with queries that pass a multitude of parameters which may or may not have data in them such as:
Create procedure dbo.some_Procedure_Name
@var1 int Null
, @var2 char(3) null
, @var3 char(2) null
as
Select field1, field2, field3
from TableName
where
(@var1 is not null and Field1 = @var1)
and
(@var2 is not null and field2 like '%' + @var2)
and
(@var3 is not null and Field3 = @var3)
Now, we could Or these conditions together Or mix and and or depending on the desire results. As the author points out, this type of approach can get rather unwieldly very fast!
I like figuring this stuff out, makes the day go by faster! 🙂
Cheers - Dave
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply