Click here to monitor SSC
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
newbie2
newbie2
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 45
I'm new to the TSQL world (coming from visual basic) and am working with large tables (125+ million rows) and having perfomance issues. This sp takes over an hour to run. Is there a better way to optimize this code? Table2 has 40,525,850 rows and Table1 ends up with 125,350,605 rows.

I'm creating two indexes within the code:
CREATE NONCLUSTERED INDEX IDX_Table2
ON Table2 (Batch,Flag,Num,Date1 )
INCLUDE (Id);

CREATE NONCLUSTERED INDEX IDX_Table1
ON Table1 (Batch,Flag,Num);

INSERT INTO Table5
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,
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 SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>'';

Also, will the following code produce the same result? Or will moving the filters to the where clause make the join take longer? How does SQL process the code?

INSERT INTO Table5
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,
FROM Table1 SD
JOIN Table2 CH
ON CH.Num=SD.Num
AND CH.Batch= SD.Batch
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 SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>''
AND CH.Flag= 0
AND SD.Batch = @Batch
AND SD.Flag= 0
laurie-789651
laurie-789651
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1271
1. Using

RTRIM(LTRIM(SD.Code ))<>''



will be very slow - if you've got an index on SD.Code it probably won't be used. Can you tidy the data in the table so you don't need RTRIM(LTRIM() & add an index?

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.

3. Not sure about the pros & cons of including the filters in the WHERE clause...
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47297 Visits: 44392
For inner joins there is no difference whatsoever. For outer joins, moving the filter from where to join changes the logic of the query. Hence it's not a matter of performance, it's a matter of which gives you the correct results.


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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47297 Visits: 44392
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


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


newbie2
newbie2
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 45
So your saying that this code still produces the same result in with the same efficiency? (I took out another join and moved it to the where clause)
INSERT INTO Table5
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,
FROM Table1 SD
JOIN Table2 CH
ON CH.Num=SD.Num
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 SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>''
AND CH.Batch = @Batch
AND CH.Flag= 0
AND SD.Batch = @Batch
AND SD.Flag= 0
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8558 Visits: 18143
You'll have better performance if you change the following statement
WHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>''


with
WHERE SD.Code <> ''


The results are the same and you can test them if you want.
Here's a script.

DECLARE @Table table(
mystring char(15))

INSERT @Table
VALUES( ''),( ' '),( ' '),
( 'a'),( ' b'),( 'c '),
( ' '), (NULL)

SELECT * FROM @Table
WHERE mystring <> ''




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Abu Dina
Abu Dina
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 3323
Here is a quick example of how moving the filter from the join to the WHERE clause can have an effect on the result of your queries (if you're using OUTER JOINS):

Although there is a filter in the first query on the Forename, SQL still includes the other records. This is because SQL server does the inner join internally and applies the filter then adds all missing rows from the left table.

Hope this makes sense.


CREATE TABLE #Employee (ID INT IDENTITY(1, 1), Forename VARCHAR(20))

INSERT INTO #Employee(Forename)
SELECT 'Abu Dina' UNION ALL
SELECT 'SQL4n00bs' UNION ALL
SELECT 'Test' UNION ALL
SELECT '1337'

CREATE TABLE #Location (ID INT IDENTITY(1, 1), Employee_ID INT, Location VARCHAR(50))

INSERT INTO #Location(Employee_ID, Location)
SELECT 1, 'UK' UNION ALL
SELECT 2, 'Manchester'


SELECT a.* , b.*
FROM #Employee AS a
LEFT JOIN #Location AS b
ON a.id = b.employee_id
and a.forename = 'Abu Dina'

SELECT a.* , b.*
FROM #Employee as a
LEFT JOIN #Location as b
ON a.id = b.employee_id
WHERE a.forename = 'Abu Dina'

DROP TABLE #Employeea
DROP TABLE #Location



---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47297 Visits: 44392
newbie2 (9/24/2012)
AND RTRIM(LTRIM(SD.Code ))<>'';


The RTRIM and LTRIM are unnecessary here. SQL ignores trailing spaces when checking string equality, hence '' = ' ', true no matter how many spaces you have.

That predicate can be reduced to AND SD.Code != ''. That != will also eliminate nulls, so you can remove the SD.Code IS NOT NULL as well.

Neither of the nonclustered indexes that you've created look optimal. Rather consider

Table1: Index key (Code, Batch, Flag, Num) Include (Type)
Table2: Index key (Flag, Batch, Num, Date1) Include (ID)


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


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16624 Visits: 17024
As previously stated this is high potential of the issue.

WHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>''



That renders your query nonSARGable. You could change that be simply:

WHERE SD.Code > ''



This will still find any value that is not '' and NULL will already be excluded.

--EDIT--
Had a desk meeting while posting and got pulled away. Seems that Gail already posted much the same as I did. :-P

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
newbie2
newbie2
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 45
Thank you all for your help!
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