Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Understanding the difference between Join and Where filters - SQL 2008R2 Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 9:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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'


Post #1364154
Posted Tuesday, September 25, 2012 2:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 246, Visits: 620
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/
Post #1364293
Posted Wednesday, September 26, 2012 1:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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
Post #1364486
Posted Wednesday, September 26, 2012 1:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #1364491
Posted Wednesday, September 26, 2012 8:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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?

 
Post #1364745
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse