Anybody know if the COALESCE(NULLIF ...)...) can raise error itself, and if
it safely to use this combination of checking error status
Every time after i calling to some store procedure i use this:
---
DECLARE @.intError int
EXEC @.intError = myProcedure @.param1, @.param2 ....
SELECT @.intError = COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
IF(@.intError =0 )
.....
.....
---
my question is, can the @.@.ERROR variable have an error that COALESCE or
NULLIF function were raise?
Thanks.
P.S. if it's not good idea to use it after calling to stored procedures so
what can u advice me
Message posted via http://www.webservertalk.comHi
I'm not sure I understand you.
Why not just doing the following?
create proc myproc
@.par int
as
--do something here
if @.@.error <>0
return -1
else
resturn 1
go
declare @.err int
select @.err=exec myproc @.par
if @.err =-1
raiserror ('It was an error',16,1)
Read this great article
http://www.sommarskog.se/error-handling-I.html
"E B via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:dc61838570f642498321c727d4c5742b@.SQ
webservertalk.com...
> Anybody know if the COALESCE(NULLIF ...)...) can raise error itself, and
if
> it safely to use this combination of checking error status
> Every time after i calling to some store procedure i use this:
> ---
> DECLARE @.intError int
> EXEC @.intError = myProcedure @.param1, @.param2 ....
> SELECT @.intError = COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
> IF(@.intError =0 )
> .....
> .....
> ---
> my question is, can the @.@.ERROR variable have an error that COALESCE or
> NULLIF function were raise?
> Thanks.
> P.S. if it's not good idea to use it after calling to stored procedures so
> what can u advice me
> --
> Message posted via http://www.webservertalk.com|||However my question is, can the COALESCE or NULLIF functions change the
status of @.@.ERROR variable
Message posted via http://www.webservertalk.com|||Any statement *could* result in an error but that looks pretty unlikely
in this case. Your code is about as safe as any other error-handling
code can be. In general keep your error handling code as simple as
possible.
David Portas
SQL Server MVP
--|||so is it good idea to use COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
after caling to some stored procedure or function in sql
Thanks
Message posted via http://www.webservertalk.com|||what the way i need to check if any error occured after calling to stored
procedure?
Message posted via http://www.webservertalk.com|||An SP won't actually return a value of NULL but if the SP can't be run (mayb
e
it doesn't exist or you don't have EXEC permissions) then the result leaves
the value of @.interror unaffected (NULL in your case). In that situation you
r
code will assign the error code to @.interror, which seems reasonable enough.
David Portas
SQL Server MVP
--
"E B via webservertalk.com" wrote:
> so is it good idea to use COALESCE(NULLIF(@.intError, 0), @.@.ERROR)
> after caling to some stored procedure or function in sql
> Thanks
> --
> Message posted via http://www.webservertalk.com
>|||There is no error event in TSQL so the only way is to check the @.@.ERROR valu
e
after EVERY statement. This doesn't catch all errors though (see the article
that Uri posted). A system I'm using is to put error-handling in its own pro
c
and call that with @.@.ERROR as a parameter:
EXEC @.err = usp_error_handler @.@.ERROR, @.calling_proc, @.user_id
the proc redturns the @.@.ERROR value. If @.@.ERROR is zero the proc just
returns immediately without executing the handling code.
That's reasonable for processes that are long running but maybe not an
overhead you'll want in an SP that's called frequently. TSQL in SQL Server
2000 provides very little scope for good error handling and much of the time
you may find it easier and better to catch errors in your calling code in VB
/ C# or whatever.
David Portas
SQL Server MVP
--
Showing posts with label coalesce. Show all posts
Showing posts with label coalesce. Show all posts
Wednesday, March 7, 2012
Coalesce with Sum
I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.
Below is my sample:
SELECT *
FROM dbo.Student w
LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID
Group BY q.StudentID
Having
(SUM(Coalesce(q.AbsenceValue),0) = 0.00)
COALESCE(SUM(q.AbsenceValue) = 0.00,0)
I have tried using the coalesce statement a couple of ways with no resolution, pls help!!
Change to this:
COALESCE( q.AbsenceValue, 0)
|||Ok, but how does that incorporate summing the column?|||
Try something like this: (in case you need the student name from your student table)
SELECT w.StudentID, w.StudentName, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue
FROM dbo.Student w
LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID
Group BY w.StudentID, w.StudentName
But you don't need to do the coalesce: SUM and AVG will skip the NULL value in the caculation.
The follwing should return the same result:
SELECT w.StudentID, w.StudentName, SUM(q.AbsenceValue) AS sumAbsenceValue
FROM dbo.Student w
LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID
Group BY w.StudentID, w.StudentName
|||Thanks for putting me on the right track. I actually got the result I needed by modifying your first example a little.
SELECT w.StudentID, SUM(Coalesce(q.AbsenceValue,0) ) AS sumAbsenceValue
FROM dbo.Student w
LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID
Group BY w.StudentID
Having SUM(Coalesce(q.AbsenceValue,0) ) = 0.00
This gets me the desired result. I still needed to compare the result of the sum so that it equalled 0.00.
Thanks for setting me straight, I was about an inch from pulling hairs 
COALESCE with parameters
I am trying to build a report table based on user supplied criteria at run time. The user may or may not enter criteria into one or more fields. I used the COLAESCE as follows (the temp vars may be passed valid data or left null by the user):
select * from dbo.employee
where LastName>=COALESCE(@.ln,lastname) andLastName<=COALESCE(@.ln2,lastname) andFirstName>=COALESCE(@.fn,firstname) andFirstName<=COALESCE(@.fn2,firstname) andhiredate>=COALESCE(@.hire,hiredate) andhiredate<=COALESCE(@.hire2,hiredate) andcheckdate>=COALESCE(@.chk,checkdate) andcheckdate<=COALESCE(@.chk2,checkdate)
The problem comes when I want to return rows that include columns that may be null. For example the CHECKDATE col might be the date the employee was reviewed and for new employees it may be null. I still want to return that row.
I had thought of creating default values for every column when the user adds a row to a table. I can set all char fields = ' ' and int fields = 0, but what is a valid default value for a date type col that won't cause problems when other procs try to grab the field and use it?
Or is there a better way to use the COALESCE function?
Thanks all!
I would add a third parameter to the coalesce function. For instance COALESCE(@.var,FieldName,'1/1/1900'). The third field would be the "default" value if the first two return null. HTH.
-Chris
|||If you couldn't choose some value as "empty", you could add additional parameter like @.ln_is_empty and then use something like (@.ln_is_empty=1 or (Lastname>=@.ln and @.ln_is_empty=0) )|||Assuming that the query is contained within a stored procedure then you would have to start by declaring another input parameter per search term to indicate whether the WHERE condition for the relevant column should check for NULL or whether the search term should be ignored (i.e. equal to itself), currently it seems that you have no way to distinguish between the two.
Once this has been done you can use:
WHERE ((checkdate >= COALESCE(@.chk,checkdate) and checkdate <= COALESCE(@.chk2,checkdate))
OR (@.checkdateisnull = 1 AND checkdate IS NULL))
As an aside, with a query such as the one you have presented you are unlikely to see great performance. It might be better to dynamically create and execute a SQL string inside the stored procedure, forget using COALESCE inside the SQL string and include only what's actually needed in the WHERE clause - using COALESCE in the way that you have done is likely to lead to table / index scans and gives little scope for performance improvements by indexing.
Chris
|||There are better ways to write this query than using COALESCE with column names. The above usage will negate use of any indexes on the columns. So you will be pretty much scanning the entire table for any combination of parameters. See the link below for various techniques that will help you solve the problem. Look for my name to see some techniques that use COALESCE/ISNULL but with better results. Erland also covers in detail other techniques that will help you get the best results.
http://www.sommarskog.se/dyn-search.html
|||Thanks!!!
I'll be studying that document for quite some time!
COALESCE to speed up queries?
Another general question. I was tweaking some other queries and I ran
across the following while trying to speed up an inner join. I re-wrote the
query as a sub-query using both IN and EXISTS, trying to force the query to
use the Clustered Primary Key on the second table (Case_Details). When I
run the following query:
SELECT cd.OffenderID,
cd.CaseID
FROM dbo.Case_Details cd
WHERE EXISTS (SELECT 'X'
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
AND od.OffenderID = cd.OffenderID)
I get this plan:
|--Parallelism(Gather Streams)
|--Hash Match(Right Semi Join,
HASH
[od].[OffenderID])=([cd].[OffenderID]),
RESIDUAL[od].[OffenderID]=[cd].[OffenderID]))
|--Bitmap(HASH[od].[OffenderID]), DEFINE[Bitmap1003]))
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS[od].[OffenderID]))
| |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK[od].[LName]='Smith' AND [od].[FName]='James' AND
[od].[DOB_Year]=1960) ORDERED FORWARD)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS[cd].[OffenderID]), WHEREPROBE([Bitmap1003])=TRUE))
|--Index
Scan(OBJECT[OffenderData].[dbo].[Case_Details].[s2] AS [cd]))
It seems to be scanning the Case_Details table Non-clustered Index instead
of using the Primary Key which consists of (OffenderID, CaseID) which are a
BIGINT NOT NULL and INT NOT NULL, respectively. The Primary Key on
Case_Details is Clustered. The Offender_Details PK is non-clustered, and
consists of (OffenderID) BIGINT NOT NULL. The Offender_Details PK and
Case_Details PK are related via the OffenderID. Foreign Key constraints are
in place.
This query takes 26,470 ms to complete.
Now all this is to say that when I change the query to the following:
SELECT cd.OffenderID,
cd.CaseID
FROM dbo.Case_Details cd
WHERE EXISTS (SELECT 'X'
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = COALESCE(1960 , 1960)
AND od.OffenderID = cd.OffenderID)
I get this query plan:
|--Nested Loops(Inner Join, OUTER REFERENCES[od].[OffenderID]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES[Expr1015], [Expr1016],
[Expr1017]))
| |--Compute Scalar(DEFINE[Expr1015]=Convert(If 1 then 1960 else
1960)-1, [Expr1016]=Convert(If 1 then 1960 else 1960)+1, [Expr1017]=If
(Convert(If 1 then 1960 else 1960)-1=NULL) then 0 else 6|If (Convert(If 1
then 1960 else 1960)+1=NULL) then
| | |--Constant Scan
| |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK[od].[LName]='Smith' AND [od].[FName]='James' AND
[od].[DOB_Year] > [Expr1015] AND [od].[DOB_Year] < [Expr1016]),
WHEREConvert([od]
|--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Case_Details].[PK_Case_Details] AS [cd]),
SEEK[cd].[OffenderID]=[od].[OffenderID]) ORDERED FORWARD)
The COALESCE() function appears to force Case_Details to (properly) use a
Clustered Index Seek instead of a Non-Clustered Index Scan.
This modified query runs in 656 ms.
Any ideas on why a COALESCE(x, x) forces the proper query plan in this
instance?
Thanks.
It seems paralellism affects the outcome. Have you tried adding 'maxdop' to
your query and see if it improves it.
Btw, an index scan is not always bad. There are cases where a single scan is
much better than doing thousands of seeks.
-oj
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Qud8e.2279$sG3.1410@.fe09.lga...
> Another general question. I was tweaking some other queries and I ran
> across the following while trying to speed up an inner join. I re-wrote
> the query as a sub-query using both IN and EXISTS, trying to force the
> query to use the Clustered Primary Key on the second table (Case_Details).
> When I run the following query:
> SELECT cd.OffenderID,
> cd.CaseID
> FROM dbo.Case_Details cd
> WHERE EXISTS (SELECT 'X'
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> AND od.OffenderID = cd.OffenderID)
> I get this plan:
> |--Parallelism(Gather Streams)
> |--Hash Match(Right Semi Join,
> HASH[od].[OffenderID])=([cd].[OffenderID]),
> RESIDUAL[od].[OffenderID]=[cd].[OffenderID]))
> |--Bitmap(HASH[od].[OffenderID]), DEFINE[Bitmap1003]))
> | |--Parallelism(Repartition Streams, PARTITION
> COLUMNS[od].[OffenderID]))
> | |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK[od].[LName]='Smith' AND [od].[FName]='James' AND
> [od].[DOB_Year]=1960) ORDERED FORWARD)
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS[cd].[OffenderID]), WHEREPROBE([Bitmap1003])=TRUE))
> |--Index
> Scan(OBJECT[OffenderData].[dbo].[Case_Details].[s2] AS [cd]))
> It seems to be scanning the Case_Details table Non-clustered Index instead
> of using the Primary Key which consists of (OffenderID, CaseID) which are
> a BIGINT NOT NULL and INT NOT NULL, respectively. The Primary Key on
> Case_Details is Clustered. The Offender_Details PK is non-clustered, and
> consists of (OffenderID) BIGINT NOT NULL. The Offender_Details PK and
> Case_Details PK are related via the OffenderID. Foreign Key constraints
> are in place.
> This query takes 26,470 ms to complete.
> Now all this is to say that when I change the query to the following:
> SELECT cd.OffenderID,
> cd.CaseID
> FROM dbo.Case_Details cd
> WHERE EXISTS (SELECT 'X'
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = COALESCE(1960 , 1960)
> AND od.OffenderID = cd.OffenderID)
> I get this query plan:
> |--Nested Loops(Inner Join, OUTER REFERENCES[od].[OffenderID]) WITH
> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES[Expr1015],
> [Expr1016], [Expr1017]))
> | |--Compute Scalar(DEFINE[Expr1015]=Convert(If 1 then 1960
> else 1960)-1, [Expr1016]=Convert(If 1 then 1960 else 1960)+1,
> [Expr1017]=If (Convert(If 1 then 1960 else 1960)-1=NULL) then 0 else 6|If
> (Convert(If 1 then 1960 else 1960)+1=NULL) then
> | | |--Constant Scan
> | |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK[od].[LName]='Smith' AND [od].[FName]='James' AND
> [od].[DOB_Year] > [Expr1015] AND [od].[DOB_Year] < [Expr1016]),
> WHEREConvert([od]
> |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Case_Details].[PK_Case_Details] AS
> [cd]), SEEK[cd].[OffenderID]=[od].[OffenderID]) ORDERED FORWARD)
> The COALESCE() function appears to force Case_Details to (properly) use a
> Clustered Index Seek instead of a Non-Clustered Index Scan.
> This modified query runs in 656 ms.
> Any ideas on why a COALESCE(x, x) forces the proper query plan in this
> instance?
> Thanks.
>
|||Thanks for the feedback. On the graphical query plan, the index scan
estimates 77 million rows; whereas the Index Seek estimates 226 rows. I
think the Index Seek might be marginally better in this case. The query
plan also shows that parallelism accounts for 4% and the Index Scan accounts
for 91% of the total plan. So I don't know that parallelism is a huge
problem in this case. Since the COALESCE version doesn't use Parallelism, I
don't know that maxdop will help.
Basically, by using COALESCE(x, x) in the example given, I've somehow
dropped the execution time from 26.5 seconds to about 650 ms. If anyone can
explain why this is so, I'd appreciate it. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e5NJDTuQFHA.2348@.tk2msftngp13.phx.gbl...
> It seems paralellism affects the outcome. Have you tried adding 'maxdop'
> to your query and see if it improves it.
> Btw, an index scan is not always bad. There are cases where a single scan
> is much better than doing thousands of seeks.
> --
> -oj
|||From the information here so far I can only speculate - most probably adding
the COALESCE changed the cardinality estimates of the filter predicate and
that influenced the optimizer to take completely different path.
Parallelism discrepancy may be a byproduct. What are the overall costs of
the two respective query plans?
Lubor
"Michael C#" <xyz@.abcdef.com> wrote in message
news:rOj8e.2075$ZQ1.854@.fe11.lga...
> Thanks for the feedback. On the graphical query plan, the index scan
> estimates 77 million rows; whereas the Index Seek estimates 226 rows. I
> think the Index Seek might be marginally better in this case. The query
> plan also shows that parallelism accounts for 4% and the Index Scan
accounts
> for 91% of the total plan. So I don't know that parallelism is a huge
> problem in this case. Since the COALESCE version doesn't use Parallelism,
I
> don't know that maxdop will help.
> Basically, by using COALESCE(x, x) in the example given, I've somehow
> dropped the execution time from 26.5 seconds to about 650 ms. If anyone
can[vbcol=seagreen]
> explain why this is so, I'd appreciate it. Thanks.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e5NJDTuQFHA.2348@.tk2msftngp13.phx.gbl...
scan
>
>
|||Surprisingly, the more COALESCEs I add, the lower the plan cost seems to go.
For the non-COALESCE query, the overall cost is 731.88400. For a single
COALESCE on LName field, it goes down to 265.74600. For A COALESCE on LName
and FName, it goes down to 29.86400.
Thanks.
"Lubor Kollar" <lubork@.online.microsft.com> wrote in message
news:O$s60p2QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> From the information here so far I can only speculate - most probably
> adding
> the COALESCE changed the cardinality estimates of the filter predicate and
> that influenced the optimizer to take completely different path.
> Parallelism discrepancy may be a byproduct. What are the overall costs of
> the two respective query plans?
> Lubor
|||Correction, it only works if I use COALESCE twice. If I add a third
COALESCE, the cost starts going back up. Strange.
Thanks.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:FFw8e.1935$V02.1216@.fe08.lga...
> Surprisingly, the more COALESCEs I add, the lower the plan cost seems to
> go. For the non-COALESCE query, the overall cost is 731.88400. For a
> single COALESCE on LName field, it goes down to 265.74600. For A COALESCE
> on LName and FName, it goes down to 29.86400.
> Thanks.
> "Lubor Kollar" <lubork@.online.microsft.com> wrote in message
> news:O$s60p2QFHA.2788@.TK2MSFTNGP09.phx.gbl...
>
|||The only explanation for what you see and with limited knowledge of your
schema I think that by adding the COALESCE the initial cost estimate of the
query is higher then without it. This initial cost determines how far will
the optimizer go searching all possible plans. Even if the initial plan
costs with and without the COALESCE may be very close to each other it may
be just enough to "discover" much cheaper plan simply by going a bit further
in the optimization when the COALESCE is used. SQL Server does not show you
the initial costs; the final cost of the query plan is usually lower than
the initial cost. It cannot be higher.
|||That makes sense, but it's a little surprising that a WHERE clause
containing [column]=COALESCE('x', 'x') could convince the optimizer to go
further steps than it normally would with [column]='x'; especially since
it's pretty obvious that these are equivalent comparisons. Ah well, it
works and I'm happy
Thanks
"Lubor" <Lubork@.online.microsoft.com> wrote in message
news:%23uBfO2WRFHA.3140@.tk2msftngp13.phx.gbl...
> The only explanation for what you see and with limited knowledge of your
> schema I think that by adding the COALESCE the initial cost estimate of
> the
> query is higher then without it. This initial cost determines how far will
> the optimizer go searching all possible plans. Even if the initial plan
> costs with and without the COALESCE may be very close to each other it may
> be just enough to "discover" much cheaper plan simply by going a bit
> further
> in the optimization when the COALESCE is used. SQL Server does not show
> you
> the initial costs; the final cost of the query plan is usually lower than
> the initial cost. It cannot be higher.
|||On Wed, 20 Apr 2005 15:10:43 -0400, Michael C# wrote:
>That makes sense, but it's a little surprising that a WHERE clause
>containing [column]=COALESCE('x', 'x') could convince the optimizer to go
>further steps than it normally would with [column]='x'; especially since
>it's pretty obvious that these are equivalent comparisons. Ah well, it
>works and I'm happy
Hi Michael,
Here's a thread in .programming that might explain this behaviour. The
start doesn't look similar to this one, but the explanation looks like
it's applicable here.
http://groups-beta.google.com/group/...ab60f02abb8ecd
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Interesting. Thanks for the pointer. I wonder if auto-parameterization
affects the number of estimated rows/number of rows - that was the major
difference between my queries/execution plans.
Thanks.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s1jd61p4gfp7so2dfkf1qb21mc0dct27u8@.4ax.com...
> On Wed, 20 Apr 2005 15:10:43 -0400, Michael C# wrote:
>
> Hi Michael,
> Here's a thread in .programming that might explain this behaviour. The
> start doesn't look similar to this one, but the explanation looks like
> it's applicable here.
> http://groups-beta.google.com/group/...ab60f02abb8ecd
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
across the following while trying to speed up an inner join. I re-wrote the
query as a sub-query using both IN and EXISTS, trying to force the query to
use the Clustered Primary Key on the second table (Case_Details). When I
run the following query:
SELECT cd.OffenderID,
cd.CaseID
FROM dbo.Case_Details cd
WHERE EXISTS (SELECT 'X'
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
AND od.OffenderID = cd.OffenderID)
I get this plan:
|--Parallelism(Gather Streams)
|--Hash Match(Right Semi Join,
HASH
[od].[OffenderID])=([cd].[OffenderID]),RESIDUAL
[od].[OffenderID]=[cd].[OffenderID]))|--Bitmap(HASH
[od].[OffenderID]), DEFINE[Bitmap1003]))| |--Parallelism(Repartition Streams, PARTITION
COLUMNS
[od].[OffenderID]))| |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]AS [od]), SEEK
[od].[LName]='Smith' AND [od].[FName]='James' AND[od].[DOB_Year]=1960) ORDERED FORWARD)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS
[cd].[OffenderID]), WHEREPROBE([Bitmap1003])=TRUE))|--Index
Scan(OBJECT
[OffenderData].[dbo].[Case_Details].[s2] AS [cd]))It seems to be scanning the Case_Details table Non-clustered Index instead
of using the Primary Key which consists of (OffenderID, CaseID) which are a
BIGINT NOT NULL and INT NOT NULL, respectively. The Primary Key on
Case_Details is Clustered. The Offender_Details PK is non-clustered, and
consists of (OffenderID) BIGINT NOT NULL. The Offender_Details PK and
Case_Details PK are related via the OffenderID. Foreign Key constraints are
in place.
This query takes 26,470 ms to complete.
Now all this is to say that when I change the query to the following:
SELECT cd.OffenderID,
cd.CaseID
FROM dbo.Case_Details cd
WHERE EXISTS (SELECT 'X'
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = COALESCE(1960 , 1960)
AND od.OffenderID = cd.OffenderID)
I get this query plan:
|--Nested Loops(Inner Join, OUTER REFERENCES
[od].[OffenderID]) WITHPREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES
[Expr1015], [Expr1016],[Expr1017]))
| |--Compute Scalar(DEFINE
[Expr1015]=Convert(If 1 then 1960 else1960)-1, [Expr1016]=Convert(If 1 then 1960 else 1960)+1, [Expr1017]=If
(Convert(If 1 then 1960 else 1960)-1=NULL) then 0 else 6|If (Convert(If 1
then 1960 else 1960)+1=NULL) then
| | |--Constant Scan
| |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]AS [od]), SEEK
[od].[LName]='Smith' AND [od].[FName]='James' AND[od].[DOB_Year] > [Expr1015] AND [od].[DOB_Year] < [Expr1016]),
WHERE
Convert([od]|--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Case_Details].[PK_Case_Details] AS [cd]),SEEK
[cd].[OffenderID]=[od].[OffenderID]) ORDERED FORWARD)The COALESCE() function appears to force Case_Details to (properly) use a
Clustered Index Seek instead of a Non-Clustered Index Scan.
This modified query runs in 656 ms.
Any ideas on why a COALESCE(x, x) forces the proper query plan in this
instance?
Thanks.
It seems paralellism affects the outcome. Have you tried adding 'maxdop' to
your query and see if it improves it.
Btw, an index scan is not always bad. There are cases where a single scan is
much better than doing thousands of seeks.
-oj
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Qud8e.2279$sG3.1410@.fe09.lga...
> Another general question. I was tweaking some other queries and I ran
> across the following while trying to speed up an inner join. I re-wrote
> the query as a sub-query using both IN and EXISTS, trying to force the
> query to use the Clustered Primary Key on the second table (Case_Details).
> When I run the following query:
> SELECT cd.OffenderID,
> cd.CaseID
> FROM dbo.Case_Details cd
> WHERE EXISTS (SELECT 'X'
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> AND od.OffenderID = cd.OffenderID)
> I get this plan:
> |--Parallelism(Gather Streams)
> |--Hash Match(Right Semi Join,
> HASH
[od].[OffenderID])=([cd].[OffenderID]),> RESIDUAL
[od].[OffenderID]=[cd].[OffenderID]))> |--Bitmap(HASH
[od].[OffenderID]), DEFINE[Bitmap1003]))> | |--Parallelism(Repartition Streams, PARTITION
> COLUMNS
[od].[OffenderID]))> | |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]> AS [od]), SEEK
[od].[LName]='Smith' AND [od].[FName]='James' AND> [od].[DOB_Year]=1960) ORDERED FORWARD)
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS
[cd].[OffenderID]), WHEREPROBE([Bitmap1003])=TRUE))> |--Index
> Scan(OBJECT
[OffenderData].[dbo].[Case_Details].[s2] AS [cd]))> It seems to be scanning the Case_Details table Non-clustered Index instead
> of using the Primary Key which consists of (OffenderID, CaseID) which are
> a BIGINT NOT NULL and INT NOT NULL, respectively. The Primary Key on
> Case_Details is Clustered. The Offender_Details PK is non-clustered, and
> consists of (OffenderID) BIGINT NOT NULL. The Offender_Details PK and
> Case_Details PK are related via the OffenderID. Foreign Key constraints
> are in place.
> This query takes 26,470 ms to complete.
> Now all this is to say that when I change the query to the following:
> SELECT cd.OffenderID,
> cd.CaseID
> FROM dbo.Case_Details cd
> WHERE EXISTS (SELECT 'X'
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = COALESCE(1960 , 1960)
> AND od.OffenderID = cd.OffenderID)
> I get this query plan:
> |--Nested Loops(Inner Join, OUTER REFERENCES
[od].[OffenderID]) WITH> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES
[Expr1015],> [Expr1016], [Expr1017]))
> | |--Compute Scalar(DEFINE
[Expr1015]=Convert(If 1 then 1960> else 1960)-1, [Expr1016]=Convert(If 1 then 1960 else 1960)+1,
> [Expr1017]=If (Convert(If 1 then 1960 else 1960)-1=NULL) then 0 else 6|If
> (Convert(If 1 then 1960 else 1960)+1=NULL) then
> | | |--Constant Scan
> | |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]> AS [od]), SEEK
[od].[LName]='Smith' AND [od].[FName]='James' AND> [od].[DOB_Year] > [Expr1015] AND [od].[DOB_Year] < [Expr1016]),
> WHERE
Convert([od]> |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Case_Details].[PK_Case_Details] AS> [cd]), SEEK
[cd].[OffenderID]=[od].[OffenderID]) ORDERED FORWARD)> The COALESCE() function appears to force Case_Details to (properly) use a
> Clustered Index Seek instead of a Non-Clustered Index Scan.
> This modified query runs in 656 ms.
> Any ideas on why a COALESCE(x, x) forces the proper query plan in this
> instance?
> Thanks.
>
|||Thanks for the feedback. On the graphical query plan, the index scan
estimates 77 million rows; whereas the Index Seek estimates 226 rows. I
think the Index Seek might be marginally better in this case. The query
plan also shows that parallelism accounts for 4% and the Index Scan accounts
for 91% of the total plan. So I don't know that parallelism is a huge
problem in this case. Since the COALESCE version doesn't use Parallelism, I
don't know that maxdop will help.
Basically, by using COALESCE(x, x) in the example given, I've somehow
dropped the execution time from 26.5 seconds to about 650 ms. If anyone can
explain why this is so, I'd appreciate it. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e5NJDTuQFHA.2348@.tk2msftngp13.phx.gbl...
> It seems paralellism affects the outcome. Have you tried adding 'maxdop'
> to your query and see if it improves it.
> Btw, an index scan is not always bad. There are cases where a single scan
> is much better than doing thousands of seeks.
> --
> -oj
|||From the information here so far I can only speculate - most probably adding
the COALESCE changed the cardinality estimates of the filter predicate and
that influenced the optimizer to take completely different path.
Parallelism discrepancy may be a byproduct. What are the overall costs of
the two respective query plans?
Lubor
"Michael C#" <xyz@.abcdef.com> wrote in message
news:rOj8e.2075$ZQ1.854@.fe11.lga...
> Thanks for the feedback. On the graphical query plan, the index scan
> estimates 77 million rows; whereas the Index Seek estimates 226 rows. I
> think the Index Seek might be marginally better in this case. The query
> plan also shows that parallelism accounts for 4% and the Index Scan
accounts
> for 91% of the total plan. So I don't know that parallelism is a huge
> problem in this case. Since the COALESCE version doesn't use Parallelism,
I
> don't know that maxdop will help.
> Basically, by using COALESCE(x, x) in the example given, I've somehow
> dropped the execution time from 26.5 seconds to about 650 ms. If anyone
can[vbcol=seagreen]
> explain why this is so, I'd appreciate it. Thanks.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e5NJDTuQFHA.2348@.tk2msftngp13.phx.gbl...
scan
>
>
|||Surprisingly, the more COALESCEs I add, the lower the plan cost seems to go.
For the non-COALESCE query, the overall cost is 731.88400. For a single
COALESCE on LName field, it goes down to 265.74600. For A COALESCE on LName
and FName, it goes down to 29.86400.
Thanks.
"Lubor Kollar" <lubork@.online.microsft.com> wrote in message
news:O$s60p2QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> From the information here so far I can only speculate - most probably
> adding
> the COALESCE changed the cardinality estimates of the filter predicate and
> that influenced the optimizer to take completely different path.
> Parallelism discrepancy may be a byproduct. What are the overall costs of
> the two respective query plans?
> Lubor
|||Correction, it only works if I use COALESCE twice. If I add a third
COALESCE, the cost starts going back up. Strange.
Thanks.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:FFw8e.1935$V02.1216@.fe08.lga...
> Surprisingly, the more COALESCEs I add, the lower the plan cost seems to
> go. For the non-COALESCE query, the overall cost is 731.88400. For a
> single COALESCE on LName field, it goes down to 265.74600. For A COALESCE
> on LName and FName, it goes down to 29.86400.
> Thanks.
> "Lubor Kollar" <lubork@.online.microsft.com> wrote in message
> news:O$s60p2QFHA.2788@.TK2MSFTNGP09.phx.gbl...
>
|||The only explanation for what you see and with limited knowledge of your
schema I think that by adding the COALESCE the initial cost estimate of the
query is higher then without it. This initial cost determines how far will
the optimizer go searching all possible plans. Even if the initial plan
costs with and without the COALESCE may be very close to each other it may
be just enough to "discover" much cheaper plan simply by going a bit further
in the optimization when the COALESCE is used. SQL Server does not show you
the initial costs; the final cost of the query plan is usually lower than
the initial cost. It cannot be higher.
|||That makes sense, but it's a little surprising that a WHERE clause
containing [column]=COALESCE('x', 'x') could convince the optimizer to go
further steps than it normally would with [column]='x'; especially since
it's pretty obvious that these are equivalent comparisons. Ah well, it
works and I'm happy
Thanks
"Lubor" <Lubork@.online.microsoft.com> wrote in message
news:%23uBfO2WRFHA.3140@.tk2msftngp13.phx.gbl...
> The only explanation for what you see and with limited knowledge of your
> schema I think that by adding the COALESCE the initial cost estimate of
> the
> query is higher then without it. This initial cost determines how far will
> the optimizer go searching all possible plans. Even if the initial plan
> costs with and without the COALESCE may be very close to each other it may
> be just enough to "discover" much cheaper plan simply by going a bit
> further
> in the optimization when the COALESCE is used. SQL Server does not show
> you
> the initial costs; the final cost of the query plan is usually lower than
> the initial cost. It cannot be higher.
|||On Wed, 20 Apr 2005 15:10:43 -0400, Michael C# wrote:
>That makes sense, but it's a little surprising that a WHERE clause
>containing [column]=COALESCE('x', 'x') could convince the optimizer to go
>further steps than it normally would with [column]='x'; especially since
>it's pretty obvious that these are equivalent comparisons. Ah well, it
>works and I'm happy
Hi Michael,
Here's a thread in .programming that might explain this behaviour. The
start doesn't look similar to this one, but the explanation looks like
it's applicable here.
http://groups-beta.google.com/group/...ab60f02abb8ecd
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Interesting. Thanks for the pointer. I wonder if auto-parameterization
affects the number of estimated rows/number of rows - that was the major
difference between my queries/execution plans.
Thanks.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s1jd61p4gfp7so2dfkf1qb21mc0dct27u8@.4ax.com...
> On Wed, 20 Apr 2005 15:10:43 -0400, Michael C# wrote:
>
> Hi Michael,
> Here's a thread in .programming that might explain this behaviour. The
> start doesn't look similar to this one, but the explanation looks like
> it's applicable here.
> http://groups-beta.google.com/group/...ab60f02abb8ecd
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
COALESCE to speed up queries?
Another general question. I was tweaking some other queries and I ran
across the following while trying to speed up an inner join. I re-wrote the
query as a sub-query using both IN and EXISTS, trying to force the query to
use the Clustered Primary Key on the second table (Case_Details). When I
run the following query:
SELECT cd.OffenderID,
cd.CaseID
FROM dbo.Case_Details cd
WHERE EXISTS (SELECT 'X'
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
AND od.OffenderID = cd.OffenderID)
I get this plan:
|--Parallelism(Gather Streams)
|--Hash Match(Right Semi Join,
HASH[od].[OffenderID])=([cd].[OffenderID]),
RESIDUAL[od].[OffenderID]=[cd].[OffenderID]))
|--Bitmap(HASH[od].[OffenderID]), DEFINE[Bitmap1003]))
| |--Parallelism(Repartition Streams, PARTITION
COLUMNS[od].[OffenderID]))
| |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Off
ender_Details]
AS [od]), SEEK[od].[LName]='Smith' AND [od].[FName]='J
ames' AND
[od].[DOB_Year]=1960) ORDERED FORWARD)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS[cd].[OffenderID]), WHEREPROBE([Bitmap1003])=TRUE))
|--Index
Scan(OBJECT[OffenderData].[dbo].[Case_Details].[s2] AS
1;cd]))
It seems to be scanning the Case_Details table Non-clustered Index instead
of using the Primary Key which consists of (OffenderID, CaseID) which are a
BIGINT NOT NULL and INT NOT NULL, respectively. The Primary Key on
Case_Details is Clustered. The Offender_Details PK is non-clustered, and
consists of (OffenderID) BIGINT NOT NULL. The Offender_Details PK and
Case_Details PK are related via the OffenderID. Foreign Key constraints are
in place.
This query takes 26,470 ms to complete.
Now all this is to say that when I change the query to the following:
SELECT cd.OffenderID,
cd.CaseID
FROM dbo.Case_Details cd
WHERE EXISTS (SELECT 'X'
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = COALESCE(1960 , 1960)
AND od.OffenderID = cd.OffenderID)
I get this query plan:
|--Nested Loops(Inner Join, OUTER REFERENCES[od].[OffenderID]) WIT
H
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES[Expr1015], [Expr1016]
,
[Expr1017]))
| |--Compute Scalar(DEFINE[Expr1015]=Convert(If 1 then 1960 else
1960)-1, [Expr1016]=Convert(If 1 then 1960 else 1960)+1, [Expr1017]=
If
(Convert(If 1 then 1960 else 1960)-1=NULL) then 0 else 6|If (Convert(If 1
then 1960 else 1960)+1=NULL) then
| | |--Constant Scan
| |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Off
ender_Details]
AS [od]), SEEK[od].[LName]='Smith' AND [od].[FName]='J
ames' AND
[od].[DOB_Year] > [Expr1015] AND [od].[DOB_Year] < [
Expr1016]),
WHEREConvert([od]
|--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Case_Details].[PK_Case_De
tails] AS [cd]),
SEEK[cd].[OffenderID]=[od].[OffenderID]) ORDERED FORWARD)
The COALESCE() function appears to force Case_Details to (properly) use a
Clustered Index Seek instead of a Non-Clustered Index Scan.
This modified query runs in 656 ms.
Any ideas on why a COALESCE(x, x) forces the proper query plan in this
instance?
Thanks.It seems paralellism affects the outcome. Have you tried adding 'maxdop' to
your query and see if it improves it.
Btw, an index scan is not always bad. There are cases where a single scan is
much better than doing thousands of seeks.
-oj
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Qud8e.2279$sG3.1410@.fe09.lga...
> Another general question. I was tweaking some other queries and I ran
> across the following while trying to speed up an inner join. I re-wrote
> the query as a sub-query using both IN and EXISTS, trying to force the
> query to use the Clustered Primary Key on the second table (Case_Details).
> When I run the following query:
> SELECT cd.OffenderID,
> cd.CaseID
> FROM dbo.Case_Details cd
> WHERE EXISTS (SELECT 'X'
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> AND od.OffenderID = cd.OffenderID)
> I get this plan:
> |--Parallelism(Gather Streams)
> |--Hash Match(Right Semi Join,
> HASH[od].[OffenderID])=([cd].[OffenderID]),
> RESIDUAL[od].[OffenderID]=[cd].[OffenderID]))
> |--Bitmap(HASH[od].[OffenderID]), DEFINE[Bitmap
1003]))
> | |--Parallelism(Repartition Streams, PARTITION
> COLUMNS[od].[OffenderID]))
> | |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_O
ffender_Details]
> AS [od]), SEEK[od].[LName]='Smith' AND [od].[FName]=
'James' AND
> [od].[DOB_Year]=1960) ORDERED FORWARD)
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS[cd].[OffenderID]), WHEREPROBE([Bitmap1003])=TRUE))
> |--Index
> Scan(OBJECT[OffenderData].[dbo].[Case_Details].[s2] AS &
#91;cd]))
> It seems to be scanning the Case_Details table Non-clustered Index instead
> of using the Primary Key which consists of (OffenderID, CaseID) which are
> a BIGINT NOT NULL and INT NOT NULL, respectively. The Primary Key on
> Case_Details is Clustered. The Offender_Details PK is non-clustered, and
> consists of (OffenderID) BIGINT NOT NULL. The Offender_Details PK and
> Case_Details PK are related via the OffenderID. Foreign Key constraints
> are in place.
> This query takes 26,470 ms to complete.
> Now all this is to say that when I change the query to the following:
> SELECT cd.OffenderID,
> cd.CaseID
> FROM dbo.Case_Details cd
> WHERE EXISTS (SELECT 'X'
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = COALESCE(1960 , 1960)
> AND od.OffenderID = cd.OffenderID)
> I get this query plan:
> |--Nested Loops(Inner Join, OUTER REFERENCES[od].[OffenderID])
WITH
> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES[Expr1015],
> [Expr1016], [Expr1017]))
> | |--Compute Scalar(DEFINE[Expr1015]=Convert(If 1 then 1960
> else 1960)-1, [Expr1016]=Convert(If 1 then 1960 else 1960)+1,
> [Expr1017]=If (Convert(If 1 then 1960 else 1960)-1=NULL) then 0 else 6
|If
> (Convert(If 1 then 1960 else 1960)+1=NULL) then
> | | |--Constant Scan
> | |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_O
ffender_Details]
> AS [od]), SEEK[od].[LName]='Smith' AND [od].[FName]=
'James' AND
> [od].[DOB_Year] > [Expr1015] AND [od].[DOB_Year] <
1;Expr1016]),
> WHEREConvert([od]
> |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Case_Details].[PK_Case_
Details] AS
> [cd]), SEEK[cd].[OffenderID]=[od].[OffenderID]) ORDE
RED FORWARD)
> The COALESCE() function appears to force Case_Details to (properly) use a
> Clustered Index Seek instead of a Non-Clustered Index Scan.
> This modified query runs in 656 ms.
> Any ideas on why a COALESCE(x, x) forces the proper query plan in this
> instance?
> Thanks.
>|||Thanks for the feedback. On the graphical query plan, the index scan
estimates 77 million rows; whereas the Index Seek estimates 226 rows. I
think the Index Seek might be marginally better in this case. The query
plan also shows that parallelism accounts for 4% and the Index Scan accounts
for 91% of the total plan. So I don't know that parallelism is a huge
problem in this case. Since the COALESCE version doesn't use Parallelism, I
don't know that maxdop will help.
Basically, by using COALESCE(x, x) in the example given, I've somehow
dropped the execution time from 26.5 seconds to about 650 ms. If anyone can
explain why this is so, I'd appreciate it. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e5NJDTuQFHA.2348@.tk2msftngp13.phx.gbl...
> It seems paralellism affects the outcome. Have you tried adding 'maxdop'
> to your query and see if it improves it.
> Btw, an index scan is not always bad. There are cases where a single scan
> is much better than doing thousands of seeks.
> --
> -oj|||From the information here so far I can only speculate - most probably adding
the COALESCE changed the cardinality estimates of the filter predicate and
that influenced the optimizer to take completely different path.
Parallelism discrepancy may be a byproduct. What are the overall costs of
the two respective query plans?
Lubor
"Michael C#" <xyz@.abcdef.com> wrote in message
news:rOj8e.2075$ZQ1.854@.fe11.lga...
> Thanks for the feedback. On the graphical query plan, the index scan
> estimates 77 million rows; whereas the Index Seek estimates 226 rows. I
> think the Index Seek might be marginally better in this case. The query
> plan also shows that parallelism accounts for 4% and the Index Scan
accounts
> for 91% of the total plan. So I don't know that parallelism is a huge
> problem in this case. Since the COALESCE version doesn't use Parallelism,
I
> don't know that maxdop will help.
> Basically, by using COALESCE(x, x) in the example given, I've somehow
> dropped the execution time from 26.5 seconds to about 650 ms. If anyone
can
> explain why this is so, I'd appreciate it. Thanks.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e5NJDTuQFHA.2348@.tk2msftngp13.phx.gbl...
scan[vbcol=seagreen]
>
>|||Surprisingly, the more COALESCEs I add, the lower the plan cost seems to go.
For the non-COALESCE query, the overall cost is 731.88400. For a single
COALESCE on LName field, it goes down to 265.74600. For A COALESCE on LName
and FName, it goes down to 29.86400.
Thanks.
"Lubor Kollar" <lubork@.online.microsft.com> wrote in message
news:O$s60p2QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> From the information here so far I can only speculate - most probably
> adding
> the COALESCE changed the cardinality estimates of the filter predicate and
> that influenced the optimizer to take completely different path.
> Parallelism discrepancy may be a byproduct. What are the overall costs of
> the two respective query plans?
> Lubor|||Correction, it only works if I use COALESCE twice. If I add a third
COALESCE, the cost starts going back up. Strange.
Thanks.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:FFw8e.1935$V02.1216@.fe08.lga...
> Surprisingly, the more COALESCEs I add, the lower the plan cost seems to
> go. For the non-COALESCE query, the overall cost is 731.88400. For a
> single COALESCE on LName field, it goes down to 265.74600. For A COALESCE
> on LName and FName, it goes down to 29.86400.
> Thanks.
> "Lubor Kollar" <lubork@.online.microsft.com> wrote in message
> news:O$s60p2QFHA.2788@.TK2MSFTNGP09.phx.gbl...
>|||The only explanation for what you see and with limited knowledge of your
schema I think that by adding the COALESCE the initial cost estimate of the
query is higher then without it. This initial cost determines how far will
the optimizer go searching all possible plans. Even if the initial plan
costs with and without the COALESCE may be very close to each other it may
be just enough to "discover" much cheaper plan simply by going a bit further
in the optimization when the COALESCE is used. SQL Server does not show you
the initial costs; the final cost of the query plan is usually lower than
the initial cost. It cannot be higher.|||That makes sense, but it's a little surprising that a WHERE clause
containing [column]=COALESCE('x', 'x') could convince the optimizer to g
o
further steps than it normally would with [column]='x'; especially since
it's pretty obvious that these are equivalent comparisons. Ah well, it
works and I'm happy
Thanks
"Lubor" <Lubork@.online.microsoft.com> wrote in message
news:%23uBfO2WRFHA.3140@.tk2msftngp13.phx.gbl...
> The only explanation for what you see and with limited knowledge of your
> schema I think that by adding the COALESCE the initial cost estimate of
> the
> query is higher then without it. This initial cost determines how far will
> the optimizer go searching all possible plans. Even if the initial plan
> costs with and without the COALESCE may be very close to each other it may
> be just enough to "discover" much cheaper plan simply by going a bit
> further
> in the optimization when the COALESCE is used. SQL Server does not show
> you
> the initial costs; the final cost of the query plan is usually lower than
> the initial cost. It cannot be higher.|||On Wed, 20 Apr 2005 15:10:43 -0400, Michael C# wrote:
>That makes sense, but it's a little surprising that a WHERE clause
>containing [column]=COALESCE('x', 'x') could convince the optimizer to
go
>further steps than it normally would with [column]='x'; especially sinc
e
>it's pretty obvious that these are equivalent comparisons. Ah well, it
>works and I'm happy
Hi Michael,
Here's a thread in .programming that might explain this behaviour. The
start doesn't look similar to this one, but the explanation looks like
it's applicable here.
http://groups-beta.google.com/group...0ab60f02abb8ecd
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Interesting. Thanks for the pointer. I wonder if auto-parameterization
affects the number of estimated rows/number of rows - that was the major
difference between my queries/execution plans.
Thanks.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s1jd61p4gfp7so2dfkf1qb21mc0dct27u8@.
4ax.com...
> On Wed, 20 Apr 2005 15:10:43 -0400, Michael C# wrote:
>
> Hi Michael,
> Here's a thread in .programming that might explain this behaviour. The
> start doesn't look similar to this one, but the explanation looks like
> it's applicable here.
> http://groups-beta.google.com/group...0ab60f02abb8ecd
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
across the following while trying to speed up an inner join. I re-wrote the
query as a sub-query using both IN and EXISTS, trying to force the query to
use the Clustered Primary Key on the second table (Case_Details). When I
run the following query:
SELECT cd.OffenderID,
cd.CaseID
FROM dbo.Case_Details cd
WHERE EXISTS (SELECT 'X'
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
AND od.OffenderID = cd.OffenderID)
I get this plan:
|--Parallelism(Gather Streams)
|--Hash Match(Right Semi Join,
HASH
[od].[OffenderID])=([cd].[OffenderID]),RESIDUAL
[od].[OffenderID]=[cd].[OffenderID]))|--Bitmap(HASH
[od].[OffenderID]), DEFINE[Bitmap1003]))| |--Parallelism(Repartition Streams, PARTITION
COLUMNS
[od].[OffenderID]))| |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK
[od].[LName]='Smith' AND [od].[FName]='James' AND
[od].[DOB_Year]=1960) ORDERED FORWARD)
|--Parallelism(Repartition Streams, PARTITION
COLUMNS
[cd].[OffenderID]), WHEREPROBE([Bitmap1003])=TRUE))|--Index
Scan(OBJECT
[OffenderData].[dbo].[Case_Details].[s2] AS 1;cd]))
It seems to be scanning the Case_Details table Non-clustered Index instead
of using the Primary Key which consists of (OffenderID, CaseID) which are a
BIGINT NOT NULL and INT NOT NULL, respectively. The Primary Key on
Case_Details is Clustered. The Offender_Details PK is non-clustered, and
consists of (OffenderID) BIGINT NOT NULL. The Offender_Details PK and
Case_Details PK are related via the OffenderID. Foreign Key constraints are
in place.
This query takes 26,470 ms to complete.
Now all this is to say that when I change the query to the following:
SELECT cd.OffenderID,
cd.CaseID
FROM dbo.Case_Details cd
WHERE EXISTS (SELECT 'X'
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = COALESCE(1960 , 1960)
AND od.OffenderID = cd.OffenderID)
I get this query plan:
|--Nested Loops(Inner Join, OUTER REFERENCES
[od].[OffenderID]) WITH
PREFETCH)
|--Nested Loops(Inner Join, OUTER REFERENCES
[Expr1015], [Expr1016],
[Expr1017]))
| |--Compute Scalar(DEFINE
[Expr1015]=Convert(If 1 then 1960 else1960)-1, [Expr1016]=Convert(If 1 then 1960 else 1960)+1, [Expr1017]=
If
(Convert(If 1 then 1960 else 1960)-1=NULL) then 0 else 6|If (Convert(If 1
then 1960 else 1960)+1=NULL) then
| | |--Constant Scan
| |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK
[od].[LName]='Smith' AND [od].[FName]='James' AND
[od].[DOB_Year] > [Expr1015] AND [od].[DOB_Year] < [
Expr1016]),
WHERE
Convert([od]|--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Case_Details].[PK_Case_Details] AS [cd]),
SEEK
[cd].[OffenderID]=[od].[OffenderID]) ORDERED FORWARD)The COALESCE() function appears to force Case_Details to (properly) use a
Clustered Index Seek instead of a Non-Clustered Index Scan.
This modified query runs in 656 ms.
Any ideas on why a COALESCE(x, x) forces the proper query plan in this
instance?
Thanks.It seems paralellism affects the outcome. Have you tried adding 'maxdop' to
your query and see if it improves it.
Btw, an index scan is not always bad. There are cases where a single scan is
much better than doing thousands of seeks.
-oj
"Michael C#" <xyz@.abcdef.com> wrote in message
news:Qud8e.2279$sG3.1410@.fe09.lga...
> Another general question. I was tweaking some other queries and I ran
> across the following while trying to speed up an inner join. I re-wrote
> the query as a sub-query using both IN and EXISTS, trying to force the
> query to use the Clustered Primary Key on the second table (Case_Details).
> When I run the following query:
> SELECT cd.OffenderID,
> cd.CaseID
> FROM dbo.Case_Details cd
> WHERE EXISTS (SELECT 'X'
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> AND od.OffenderID = cd.OffenderID)
> I get this plan:
> |--Parallelism(Gather Streams)
> |--Hash Match(Right Semi Join,
> HASH
[od].[OffenderID])=([cd].[OffenderID]),> RESIDUAL
[od].[OffenderID]=[cd].[OffenderID]))> |--Bitmap(HASH
[od].[OffenderID]), DEFINE[Bitmap1003]))
> | |--Parallelism(Repartition Streams, PARTITION
> COLUMNS
[od].[OffenderID]))> | |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK
[od].[LName]='Smith' AND [od].[FName]='James' AND
> [od].[DOB_Year]=1960) ORDERED FORWARD)
> |--Parallelism(Repartition Streams, PARTITION
> COLUMNS
[cd].[OffenderID]), WHEREPROBE([Bitmap1003])=TRUE))> |--Index
> Scan(OBJECT
[OffenderData].[dbo].[Case_Details].[s2] AS [cd]))
> It seems to be scanning the Case_Details table Non-clustered Index instead
> of using the Primary Key which consists of (OffenderID, CaseID) which are
> a BIGINT NOT NULL and INT NOT NULL, respectively. The Primary Key on
> Case_Details is Clustered. The Offender_Details PK is non-clustered, and
> consists of (OffenderID) BIGINT NOT NULL. The Offender_Details PK and
> Case_Details PK are related via the OffenderID. Foreign Key constraints
> are in place.
> This query takes 26,470 ms to complete.
> Now all this is to say that when I change the query to the following:
> SELECT cd.OffenderID,
> cd.CaseID
> FROM dbo.Case_Details cd
> WHERE EXISTS (SELECT 'X'
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = COALESCE(1960 , 1960)
> AND od.OffenderID = cd.OffenderID)
> I get this query plan:
> |--Nested Loops(Inner Join, OUTER REFERENCES
[od].[OffenderID]) WITH
> PREFETCH)
> |--Nested Loops(Inner Join, OUTER REFERENCES
[Expr1015],> [Expr1016], [Expr1017]))
> | |--Compute Scalar(DEFINE
[Expr1015]=Convert(If 1 then 1960> else 1960)-1, [Expr1016]=Convert(If 1 then 1960 else 1960)+1,
> [Expr1017]=If (Convert(If 1 then 1960 else 1960)-1=NULL) then 0 else 6
|If
> (Convert(If 1 then 1960 else 1960)+1=NULL) then
> | | |--Constant Scan
> | |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK
[od].[LName]='Smith' AND [od].[FName]='James' AND
> [od].[DOB_Year] > [Expr1015] AND [od].[DOB_Year] <
1;Expr1016]),
> WHERE
Convert([od]> |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Case_Details].[PK_Case_Details] AS
> [cd]), SEEK
[cd].[OffenderID]=[od].[OffenderID]) ORDERED FORWARD)
> The COALESCE() function appears to force Case_Details to (properly) use a
> Clustered Index Seek instead of a Non-Clustered Index Scan.
> This modified query runs in 656 ms.
> Any ideas on why a COALESCE(x, x) forces the proper query plan in this
> instance?
> Thanks.
>|||Thanks for the feedback. On the graphical query plan, the index scan
estimates 77 million rows; whereas the Index Seek estimates 226 rows. I
think the Index Seek might be marginally better in this case. The query
plan also shows that parallelism accounts for 4% and the Index Scan accounts
for 91% of the total plan. So I don't know that parallelism is a huge
problem in this case. Since the COALESCE version doesn't use Parallelism, I
don't know that maxdop will help.
Basically, by using COALESCE(x, x) in the example given, I've somehow
dropped the execution time from 26.5 seconds to about 650 ms. If anyone can
explain why this is so, I'd appreciate it. Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e5NJDTuQFHA.2348@.tk2msftngp13.phx.gbl...
> It seems paralellism affects the outcome. Have you tried adding 'maxdop'
> to your query and see if it improves it.
> Btw, an index scan is not always bad. There are cases where a single scan
> is much better than doing thousands of seeks.
> --
> -oj|||From the information here so far I can only speculate - most probably adding
the COALESCE changed the cardinality estimates of the filter predicate and
that influenced the optimizer to take completely different path.
Parallelism discrepancy may be a byproduct. What are the overall costs of
the two respective query plans?
Lubor
"Michael C#" <xyz@.abcdef.com> wrote in message
news:rOj8e.2075$ZQ1.854@.fe11.lga...
> Thanks for the feedback. On the graphical query plan, the index scan
> estimates 77 million rows; whereas the Index Seek estimates 226 rows. I
> think the Index Seek might be marginally better in this case. The query
> plan also shows that parallelism accounts for 4% and the Index Scan
accounts
> for 91% of the total plan. So I don't know that parallelism is a huge
> problem in this case. Since the COALESCE version doesn't use Parallelism,
I
> don't know that maxdop will help.
> Basically, by using COALESCE(x, x) in the example given, I've somehow
> dropped the execution time from 26.5 seconds to about 650 ms. If anyone
can
> explain why this is so, I'd appreciate it. Thanks.
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e5NJDTuQFHA.2348@.tk2msftngp13.phx.gbl...
scan[vbcol=seagreen]
>
>|||Surprisingly, the more COALESCEs I add, the lower the plan cost seems to go.
For the non-COALESCE query, the overall cost is 731.88400. For a single
COALESCE on LName field, it goes down to 265.74600. For A COALESCE on LName
and FName, it goes down to 29.86400.
Thanks.
"Lubor Kollar" <lubork@.online.microsft.com> wrote in message
news:O$s60p2QFHA.2788@.TK2MSFTNGP09.phx.gbl...
> From the information here so far I can only speculate - most probably
> adding
> the COALESCE changed the cardinality estimates of the filter predicate and
> that influenced the optimizer to take completely different path.
> Parallelism discrepancy may be a byproduct. What are the overall costs of
> the two respective query plans?
> Lubor|||Correction, it only works if I use COALESCE twice. If I add a third
COALESCE, the cost starts going back up. Strange.
Thanks.
"Michael C#" <xyz@.abcdef.com> wrote in message
news:FFw8e.1935$V02.1216@.fe08.lga...
> Surprisingly, the more COALESCEs I add, the lower the plan cost seems to
> go. For the non-COALESCE query, the overall cost is 731.88400. For a
> single COALESCE on LName field, it goes down to 265.74600. For A COALESCE
> on LName and FName, it goes down to 29.86400.
> Thanks.
> "Lubor Kollar" <lubork@.online.microsft.com> wrote in message
> news:O$s60p2QFHA.2788@.TK2MSFTNGP09.phx.gbl...
>|||The only explanation for what you see and with limited knowledge of your
schema I think that by adding the COALESCE the initial cost estimate of the
query is higher then without it. This initial cost determines how far will
the optimizer go searching all possible plans. Even if the initial plan
costs with and without the COALESCE may be very close to each other it may
be just enough to "discover" much cheaper plan simply by going a bit further
in the optimization when the COALESCE is used. SQL Server does not show you
the initial costs; the final cost of the query plan is usually lower than
the initial cost. It cannot be higher.|||That makes sense, but it's a little surprising that a WHERE clause
containing [column]=COALESCE('x', 'x') could convince the optimizer to g
o
further steps than it normally would with [column]='x'; especially since
it's pretty obvious that these are equivalent comparisons. Ah well, it
works and I'm happy
Thanks
"Lubor" <Lubork@.online.microsoft.com> wrote in message
news:%23uBfO2WRFHA.3140@.tk2msftngp13.phx.gbl...
> The only explanation for what you see and with limited knowledge of your
> schema I think that by adding the COALESCE the initial cost estimate of
> the
> query is higher then without it. This initial cost determines how far will
> the optimizer go searching all possible plans. Even if the initial plan
> costs with and without the COALESCE may be very close to each other it may
> be just enough to "discover" much cheaper plan simply by going a bit
> further
> in the optimization when the COALESCE is used. SQL Server does not show
> you
> the initial costs; the final cost of the query plan is usually lower than
> the initial cost. It cannot be higher.|||On Wed, 20 Apr 2005 15:10:43 -0400, Michael C# wrote:
>That makes sense, but it's a little surprising that a WHERE clause
>containing [column]=COALESCE('x', 'x') could convince the optimizer to
go
>further steps than it normally would with [column]='x'; especially sinc
e
>it's pretty obvious that these are equivalent comparisons. Ah well, it
>works and I'm happy
Hi Michael,
Here's a thread in .programming that might explain this behaviour. The
start doesn't look similar to this one, but the explanation looks like
it's applicable here.
http://groups-beta.google.com/group...0ab60f02abb8ecd
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Interesting. Thanks for the pointer. I wonder if auto-parameterization
affects the number of estimated rows/number of rows - that was the major
difference between my queries/execution plans.
Thanks.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:s1jd61p4gfp7so2dfkf1qb21mc0dct27u8@.
4ax.com...
> On Wed, 20 Apr 2005 15:10:43 -0400, Michael C# wrote:
>
> Hi Michael,
> Here's a thread in .programming that might explain this behaviour. The
> start doesn't look similar to this one, but the explanation looks like
> it's applicable here.
> http://groups-beta.google.com/group...0ab60f02abb8ecd
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)