SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding the difference between Join and Where filters - SQL 2008R2


Understanding the difference between Join and Where filters - SQL 2008R2

Author
Message
Steven Willis
Steven Willis
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3491 Visits: 1721
Here's another option that moves the string manipulation and identification of NULLs to the SELECT statement instead of using it in a JOIN or WHERE clause where the 'SARG-ability' of the statement may be an issue. It sets blanks and NULLs to some value (in this example it's '0') and then the Where clause can exclude that value. This option would need to be tested against other options of course.



SELECT
[Desc]
,[Num]
,[Batch]
,[CodeId]
,[Type]
,[Id]
FROM
(
SELECT DISTINCT
CD.[Desc] AS [Desc]
,SD.[Num] AS [Num]
,SD.[Batch] AS [Batch]
,CD.[CodeId] AS [CodeId]
,SD.[Type] AS [Type]
,CH.[Id] AS [Id]
,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]
FROM
Table1 SD
JOIN
Table2 CH
ON CH.Num = SD.Num
AND CH.Batch = @Batch
AND CH.Flag = 0
AND SD.Batch = @Batch
AND SD.Flag = 0
JOIN
Table3 M
ON M.Code = SD.Code
AND (Map = 1
OR Map = @Map)
AND CH.Date1 BETWEEN M.Date2 AND M.Date3
JOIN
Table4 CD
ON M.CodeId = CD.CodeId
) AS Result
WHERE
[Code] <> '0'



tim_harkin
tim_harkin
SSC Eights!
SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)SSC Eights! (907 reputation)

Group: General Forum Members
Points: 907 Visits: 934
GilaMonster (9/24/2012)
laurie-789651 (9/24/2012)
2. You could consider using

FROM Table1 SD WITH (NOLOCK) JOIN Table2 WITH (NOLOCK) etc.



as long as the table isn't being updated - this saves time as no read locks are issued.


And incorrect results are possible, not just dirty reads, duplicate or missing rows. If there are no changes being made, there's little gain from nolock, the overhead of taking locks is not that high, and it introduces the potential for incorrect results when changes are being made.

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx


A quick little demo to show incorrect results from NOLOCK, even when the data you are reading is static.

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65944 Visits: 20214
Steven Willis (9/25/2012)
Here's another option that moves the string manipulation and identification of NULLs to the SELECT statement instead of using it in a JOIN or WHERE clause where the 'SARG-ability' of the statement may be an issue. It sets blanks and NULLs to some value (in this example it's '0') and then the Where clause can exclude that value. This option would need to be tested against other options of course.



SELECT
[Desc]
,[Num]
,[Batch]
,[CodeId]
,[Type]
,[Id]
FROM
(
SELECT DISTINCT
CD.[Desc] AS [Desc]
,SD.[Num] AS [Num]
,SD.[Batch] AS [Batch]
,CD.[CodeId] AS [CodeId]
,SD.[Type] AS [Type]
,CH.[Id] AS [Id]
,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]
FROM
Table1 SD
JOIN
Table2 CH
ON CH.Num = SD.Num
AND CH.Batch = @Batch
AND CH.Flag = 0
AND SD.Batch = @Batch
AND SD.Flag = 0
JOIN
Table3 M
ON M.Code = SD.Code
AND (Map = 1
OR Map = @Map)
AND CH.Date1 BETWEEN M.Date2 AND M.Date3
JOIN
Table4 CD
ON M.CodeId = CD.CodeId
) AS Result
WHERE
[Code] <> '0'






There's nothing wrong with your reasoning, Steven - it looks like a winner. However, I strongly suspect that the execution plan of your query would be identical to this:

   SELECT DISTINCT
CD.[Desc] AS [Desc]
,SD.[Num] AS [Num]
,SD.[Batch] AS [Batch]
,CD.[CodeId] AS [CodeId]
,SD.[Type] AS [Type]
,CH.[Id] AS [Id]
,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]
FROM
Table1 SD
JOIN
Table2 CH
ON CH.Num = SD.Num
AND CH.Batch = @Batch
AND CH.Flag = 0
AND SD.Batch = @Batch
AND SD.Flag = 0
JOIN
Table3 M
ON M.Code = SD.Code
AND (Map = 1
OR Map = @Map)
AND CH.Date1 BETWEEN M.Date2 AND M.Date3
JOIN
Table4 CD
ON M.CodeId = CD.CodeId
WHERE
ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') <> '0'



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For 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
Exploring Recursive CTEs by Example Dwain Camps
GilaMonster
GilaMonster
SSC Guru
SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)SSC Guru (371K reputation)

Group: General Forum Members
Points: 371106 Visits: 46970
Yes, those two will be identical.

While the string manipulation's in the select, that derived column is used in the where, so it's absolutely the same as having the string manipulation in the where.

Both of these, for example, are not SARGable and will simplify to the same query structure.

SELECT <columns> FROM SomeTable
WHERE Substring(SomeColumn,2,3) = 'abc'

SELECT * FROM (
SELECT <columns>, Substring(SomeColumn,2,3) as TrimmedString FROM SomeTable
) sub
WHERE sub.TrimmedString = 'abc'



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Steven Willis
Steven Willis
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3491 Visits: 1721
GilaMonster (9/26/2012)
Yes, those two will be identical.

While the string manipulation's in the select, that derived column is used in the where, so it's absolutely the same as having the string manipulation in the where.

Both of these, for example, are not SARGable and will simplify to the same query structure.

SELECT <columns> FROM SomeTable
WHERE Substring(SomeColumn,2,3) = 'abc'

SELECT * FROM (
SELECT <columns>, Substring(SomeColumn,2,3) as TrimmedString FROM SomeTable
) sub
WHERE sub.TrimmedString = 'abc'



Thanks for that explanation.

Just thinking out loud...could there ever be a benefit to having a calculated column that converts any nulls to something non-null for queries/indexes? I suppose the best solution is to not allow nulls but I import lots of data where I'm stuck with what I get. Would it be better to convert the data to a blank or some other value during insert/update even if the value is truly an unknown? What other options are there for avoiding an IsNull conversion or a 'WHERE col = val [or/and] col [is/is not] null"...or is that even something to avoid?

 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search