Why not better performance? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Why not better performance?

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-02-22 : 05:28:06

Hi Team,

I am trying to learn query optimization using SQL Server 2005.

I had a notion that if we apply filtering at the time of JOIN (rather than on WHERE), we get better performance. Is it true?
In the batch, the Execution plans of the following two queries take exactly the same percentage (50 -50) . Should I look into any other tool to validate better performance?

SELECT *
FROM Center C
INNER JOIN Area A ON A.Area_ID = C.Area_ID
INNER JOIN Region R ON R.Region_ID = A.Region_ID
WHERE LKCenterStatusCode = 'CENTERSTATUSCLOSED'

SELECT *
FROM Center C
INNER JOIN Area A ON A.Area_ID = C.Area_ID AND LKCenterStatusCode = 'CENTERSTATUSCLOSED'
INNER JOIN Region R ON R.Region_ID = A.Region_ID

Please share your thougths

Thanks
Lijo Cheeran Joseph

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 05:34:43

We cannot see from which table "LKCenterStatusCode" is comming.
If it is coming from Center then there will be no better performance.
If it is coming from Area then there can be a better performance because the number of joining records will decreased BEFORE the WHERE clause is doing its job.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-02-22 : 05:38:56

In the previous case, LKCenterStatusCode is in Center table.

Following also gave the same % - 50 -50. In this case, the condition is frm Area table.

SELECT *
FROM Center C
INNER JOIN Area A ON A.Area_ID = C.Area_ID
INNER JOIN Region R ON R.Region_ID = A.Region_ID
WHERE A.AssociatedStateCode = 'VA'

SELECT *
FROM Center C
INNER JOIN Area A ON A.Area_ID = C.Area_ID AND A.AssociatedStateCode = 'VA'
INNER JOIN Region R ON R.Region_ID = A.Region_ID

Could you please explain why it behaves like this?

Thanks
Lijo

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-22 : 05:43:22

I don't think it'll make any difference.

If you produce the actual execution plan you should see no difference between the two as the query plan compiler is clever enough to optimise it for you.

I very complicated conditions it sometimes makes sub-optimal choices where you can change it's behaviour by messing with the join conditions but that is usually when you have lots of outer join conditions and for some reason the optimiser it choosing an implicit cross join and then filter.

Consider tyis


DECLARE @tab1 TABLE (
	[ID] INT PRIMARY KEY
	, [val] VARCHAR(5)
	)

DECLARE @tab2 TABLE (
	[ID] INT PRIMARY KEY
	, [tab1] INT
	, [val] VARCHAR(5)
	)

INSERT @tab1
SELECT 1, 'a'
UNION SELECT 2, 'b'

INSERT @tab2
SELECT 1, 1, 'A'
UNION SELECT 2, 2,'B'
UNION SELECT 3, 1, 'AA'
UNION SELECT 4, 2, 'BB'

SELECT
	t1.*
	, t2.*
FROM
	@tab1 t1
	JOIN @tab2 t2 ON t2.[tab1] = t1.[ID]
WHERE
	t2.[val] LIKE 'B%'

SELECT
	t1.*
	, t2.*
FROM
	@tab1 t1
	JOIN @tab2 t2 ON t2.[tab1] = t1.[ID] AND t2.[val] LIKE 'B%'

SELECT
	t1.*
	, t2.*
FROM
	@tab2 t2
	JOIN @tab1 t1 ON t1.[ID] = t2.[tab1] AND t2.[val] LIKE 'B%'

The execution plans of the three SELECT statements are identical

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-22 : 05:46:54

Turn on the include Actual execution plan (cntrl M) in management studio window -- you can then see the XML plan in a nice graphical way.

If you care about performance then you should look into your index strategy. Do you have good indices on all the join conditions and do you have a good covering index for the WHERE clause of your queries.

With the actual execution plan you can see and try to eliminate things like

Clustered Index Scan / Table Scan.

And later on try and aliminate

Key Lookup

By having a good covering index that covers alL the columns in your WHERE clause.

Sometimes that isn't possible and a KEY lookup is still generally a better performer than a CLUSTERED INDEX SCAN.

There are tons of posts here about performance - -just read through a few and you'll pick up a taste for it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-22 : 05:47:33

Like I said a couple of days before
performance tuning always depends on many things.
It is not a question of general notes on how to optimize a query.
It is a kind of Art.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-02-22 : 06:05:20

Thank you, all…

I have a checklist for better SQL, which I modified by adding a note as follows -

“Apply filtering conditions on the INNER condition itself. Don’t wait till WHERE clause to apply the filtering. [Note: Most of the times SQL engine will do it. But in some complex queries SQL engine will not. Hence always follow this best practice.]”

I understand that certain guidelines vary certain times .

quote:

<<There are tons of posts here about performance - -just read through a few and you'll pick up a taste for it.>>

Also could you please suggest some high quality posts?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-22 : 06:30:12

quote:
Originally posted by Lijo Cheeran Joseph

Thank you, all…

“Apply filtering conditions on the INNER condition itself. Don’t wait till WHERE clause to apply the filtering. [Note: Most of the times SQL engine will do it. But in some complex queries SQL engine will not. Hence always follow this best practice.]”

Don't do that!

The potential performance benefit is very small. Unless you have an absurdly complex query.

The readabillity of you code is much more important. Also this only applies to INNER JOINS. When you deal with OUTER JOINS then moving the where clause into the join actually changes the query results.

Your JOIN should ideally only establish the relationship between the tables. Use the Where clause to filter rows -- the optimiser is very good and you'll probably never come across a situation where it's chosen a terrible plan. The worst think that it does with any regularity is choose a sub-optimal index but that can be avoided with good indices.

quote:

quote:

<<There are tons of posts here about performance - -just read through a few and you'll pick up a taste for it.>>

Also could you please suggest some high quality posts?

This one has good links from GilaMonster:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128844&SearchTerms=optimise

PESO's blog generally has a lot of performance stuff on it:
http://weblogs.sqlteam.com/peterl/Default.aspx

General Links:
http://msdn.microsoft.com/en-us/library/ms979196.aspx

http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 08:43:09

" When you deal with OUTER JOINS then moving the where clause into the join actually changes the query results"

I would phrase that the other way round, no??


SELECT ...
FROM TableA AS A
    LEFT OUTER JOIN TableB AS B
        ON B.ID = A.ID
       AND B.SomeCol = 1
WHERE B.SomeCol = 1

The B.SomeCol = 1 in the Left Outer Join makes it part of the join, but putting it in the WHERE changes the Outer Join to an Inner Join.

For that reason I put all conditions relating to columns in a JOIN'd table in the JOIN conditions (i.e. makes it easy to swap INNER / OUTER Join

" Apply filtering conditions on the INNER condition itself. Don’t wait till WHERE clause to apply the filtering. [Note: Most of the times SQL engine will do it. But in some complex queries SQL engine will not. Hence always follow this best practice"

I think it will be very rare that the optimiser does not make the same query for either style.

However, my preference is to have the Conditions for the Joined table in the JOIN - both for the reason just described, but also because I think it helps with code-quality. We find that we notice discrepancies more easily if the Joined table's conditions are grouped together.

We also always put the Joined Table's column first / on the left side:


SELECT ...
FROM TableA AS A
    LEFT OUTER JOIN TableB AS B
        ON B.ID = A.ID
       AND B.Col2 = ...
       AND B.Col3 = ...

But this has nothing to do with performance, just minimising errors during code writing and, more importantly, during code maintenance and for consistency.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-22 : 10:49:38

quote:
" When you deal with OUTER JOINS then moving the where clause into the join actually changes the query results"

I would phrase that the other way round, no??

Yeah -- I didn't really say what I meant to there.

quote:

I think it will be very rare that the optimiser does not make the same query for either style.

It's only happened to me once.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129549

And that was a complicated query where the optimiser chose a cross join on hundreds of thousands of rows and then filtered the results unless you forced it.

Re code style.

In our shop for INNER JOIN's I'll just establish the general relationship of the table join in the FROM clauses and then filter more in the WHERE clause. As a rule of thumb ofc there will be exceptions.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-22 : 11:01:08

Having all the columns related to Joined table in the Join makes twiddling Inner/Outer Join easy - we seem to do that a lot in DEV (until we get around to ACTUALLY checking if there is REALLY an FKey enforcing the relationship!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources