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 Monday, September 24, 2012 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 11:25 AM
Points: 9, Visits: 31
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

Post #1363492
Posted Monday, September 24, 2012 8:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
1. Using

RTRIM(LTRIM(SD.Code ))&lt;&gt;''

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...
Post #1363497
Posted Monday, September 24, 2012 8:03 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 @ 7:41 AM
Points: 40,167, Visits: 36,553
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 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 #1363499
Posted Monday, September 24, 2012 8:06 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 @ 7:41 AM
Points: 40,167, Visits: 36,553
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 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 #1363505
Posted Monday, September 24, 2012 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 11:25 AM
Points: 9, Visits: 31
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
Post #1363510
Posted Monday, September 24, 2012 8:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 3,742, Visits: 8,390
You'll have better performance if you change the following statement
WHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))&lt;&gt;''

with
WHERE SD.Code &lt;&gt; ''

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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1363520
Posted Monday, September 24, 2012 8:39 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:54 AM
Points: 708, Visits: 3,290
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
Post #1363536
Posted Monday, September 24, 2012 8:42 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 @ 7:41 AM
Points: 40,167, Visits: 36,553
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 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 #1363538
Posted Monday, September 24, 2012 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:15 PM
Points: 13,077, Visits: 12,523
As previously stated this is high potential of the issue.

WHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))&lt;&gt;''

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.


_______________________________________________________________

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)
Post #1363554
Posted Monday, September 24, 2012 2:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 11:25 AM
Points: 9, Visits: 31
Thank you all for your help!
Post #1363722
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse