Coalesce increasing performance but Why?

This past wend I tried out a tool called Toad for Sql and it had a query
optimizer in it. I tossed in my slow performing query, walked away and 2
hours later had 50+ alternative queries.
One of the results had a 99.83% improvement. Logical Reads went from
62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
now took 35 seconds.
There were 5 left joins in the query. It simple replaced two of them (see
below), everything else stayed the same. im very happy with the results but
Id like to understand why COALESCE would make the query run almost 100%
faster.
LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
ac.boardID)
and
LEFT OUTER JOIN question_std_answer qsa
ON (qsa.cobrandID=ac.cobrandID
AND qsa.masterID=ac.masterID
AND qsa.accountID=ac.accountID
AND qsa.positionID=ac.positionID
AND qsa.jsrUserID=ac.jsrUserID
AND qsa.questionID=10)
with
LEFT OUTER JOIN question_std_answer qsa
ON qsa.cobrandID = ac.cobrandID
AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
AND qsa.accountID = ac.accountID
AND qsa.positionID = ac.positionID
AND qsa.jsrUserID = ac.jsrUserID
AND qsa.questionID = 10I'm not sure but I am guessing that the use of coalesce negated the indexes
that were previously being used, allowing different indexes to be used.
Have you tried recreating statistics and comparing the two queries again?
It sounds like SQL server is making a mistake in its chosen plan, but using
this function is removing an option and making it default to a muich better
plan.
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past wend I tried out a tool called Toad for Sql and it had a
query
> optimizer in it. I tossed in my slow performing query, walked away and 2
> hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
but
> Id like to understand why COALESCE would make the query run almost 100%
> faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||I ran many times testing it against different date ranges always starting wi
th:
CHECKPOINT -- write dirty pages from data cache to disk
DBCC DROPCLEANBUFFERS -- clear the data cache
DBCC FREEPROCCACHE -- clear the procedure cache
Everytime is performed essentially the same give or take a second or two.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message news:etU6gJ7OGHA.916@.T
K2MSFTNGP10.phx.gbl...
> I'm not sure but I am guessing that the use of coalesce negated the indexe
s
> that were previously being used, allowing different indexes to be used.
> Have you tried recreating statistics and comparing the two queries again?
> It sounds like SQL server is making a mistake in its chosen plan, but usin
g
> this function is removing an option and making it default to a muich bette
r
> plan.
>
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> query
> but
>
>|||What does the Execution Plan tell you?
http://msdn.microsoft.com/library/d... />
1_5pde.asp
"Brian" <brian@.nospam.com> wrote in message
news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
> This past wend I tried out a tool called Toad for Sql and it had a
> query optimizer in it. I tossed in my slow performing query, walked away
> and 2 hours later had 50+ alternative queries.
> One of the results had a 99.83% improvement. Logical Reads went from
> 62,152 - 1,145 on a query of 2 days data. A full year query took 25 min,
> now took 35 seconds.
> There were 5 left joins in the query. It simple replaced two of them (see
> below), everything else stayed the same. im very happy with the results
> but Id like to understand why COALESCE would make the query run almost
> 100% faster.
> LEFT OUTER JOIN jobboard jb ON (jb.boardID=ac.boardID) with
> LEFT OUTER JOIN jobboard jb ON jb.boardID = COALESCE (ac.boardID ,
> ac.boardID)
> and
> LEFT OUTER JOIN question_std_answer qsa
> ON (qsa.cobrandID=ac.cobrandID
> AND qsa.masterID=ac.masterID
> AND qsa.accountID=ac.accountID
> AND qsa.positionID=ac.positionID
> AND qsa.jsrUserID=ac.jsrUserID
> AND qsa.questionID=10)
> with
> LEFT OUTER JOIN question_std_answer qsa
> ON qsa.cobrandID = ac.cobrandID
> AND qsa.masterID = COALESCE (ac.masterID , ac.masterID)
> AND qsa.accountID = ac.accountID
> AND qsa.positionID = ac.positionID
> AND qsa.jsrUserID = ac.jsrUserID
> AND qsa.questionID = 10
>|||Thanks for replying JT ... Im by no means a DBA .. what should I focus on
when looking at the Execution Plan?
Thanks!
"JT" <someone@.microsoft.com> wrote in message
news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> What does the Execution Plan tell you?
> http://msdn.microsoft.com/library/d...>
n_1_5pde.asp
> "Brian" <brian@.nospam.com> wrote in message
> news:OLlDHE7OGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server does not execute SQL. It interprets SQL, compiles an execution
plan, and then uses that plan to perform a sequence of physical operations
such as index scans, joins, spooling, etc. You can deterministically compare
the design merits of one query versus another similar query by studying
their execution plan. For example, does one query perform a full table scan
while an alternative query perform a more efficient index scan?
Below are a few articles describing in more detail how to gain useful
information from execution plans:
SQL Tuning Tutorial - Understanding a Database Execution Plan (1)
http://www.codeproject.com/cs/datab...-tutorial-1.asp
How to Select Indexes for Your SQL Server Tables
http://www.sql-server-performance.com/mr_indexing.asp
SQL Server Query Execution Plan Analysis
http://www.sql-server-performance.c...an_analysis.asp
"Brian" <brian@.nospam.com> wrote in message
news:eZz$s18OGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks for replying JT ... Im by no means a DBA .. what should I focus on
> when looking at the Execution Plan?
> Thanks!
> "JT" <someone@.microsoft.com> wrote in message
> news:%23eMsok8OGHA.2124@.TK2MSFTNGP14.phx.gbl...
>|||I would guess that this is based on the use of
coalesce to force the use of an index.This is
simply a *trick* that was put forward by
'Umachandar Jayachandran' then an MVP.The idea is
that an index can be forced for any data type
by using the datatype boundries.Although a boundry
is not being used here the construct is probably
enough to force the use of an index.
I don't think this trick is talked about in a kb
for obvious reasons:)
I refer the interested reader to these threads:
http://tinyurl.com/rpa2x
http://tinyurl.com/n2oql
http://tinyurl.com/ns9aq
http://tinyurl.com/n5v4y
$.02 from
www.rac4sql.net|||No, this is a different trick. This trick will have the result that the
optimizer will not consider certain (underperforming) access paths. The
trick that Umachandar introduced is not used for joins, but for
filtering expressions (i.e. the WHERE clause).
Gert-Jan
05ponyGT wrote:
> I would guess that this is based on the use of
> coalesce to force the use of an index.This is
> simply a *trick* that was put forward by
> 'Umachandar Jayachandran' then an MVP.The idea is
> that an index can be forced for any data type
> by using the datatype boundries.Although a boundry
> is not being used here the construct is probably
> enough to force the use of an index.
> I don't think this trick is talked about in a kb
> for obvious reasons:)
> I refer the interested reader to these threads:
> http://tinyurl.com/rpa2x
> http://tinyurl.com/n2oql
> http://tinyurl.com/ns9aq
> http://tinyurl.com/n5v4y
> $.02 from
> www.rac4sql.net|||This comes as a shock as I'm so rarely wrong:(
Wonder why we use the term *trick* and not *kludge* :)
Anyway this is just another illustration of turning *what* not *how*
on its head.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:440499B7.72531652@.toomuchspamalready.nl...
> No, this is a different trick. This trick will have the result that the
> optimizer will not consider certain (underperforming) access paths. The
> trick that Umachandar introduced is not used for joins, but for
> filtering expressions (i.e. the WHERE clause).
> Gert-Jan
>
> 05ponyGT wrote: