query problem --with a view

I have a View which does what I need it too-

select distinct d.deptid, d.[description], d.supervisor, d.approvedby, case
when c1.cnt = c2.cnt then 'reviews are complete' else 'reviews still in progress' end as deptstatus
From department d
Left join (Select Count(*) cnt, deptid from employee group by deptid) c1 on (c1.deptid = d.deptid)
Left join (Select Count(*) cnt, deptid from employeeeval join employee
on (employeeeval.employeeid = employee.employeeid) group by deptid) c2 on (c2.deptid = d.deptid)

Brings back data as follows:

Cast - Cast - 00999 - 09246 - reviews still in progress
CMP- Copper Metal Products - 03315 - 09246 - reviews are complete
CNS- Copper Narrow Strip - 07530 - 09246 - reviews still in progress
CRW- Copper Rod and Wire - 01950 - 09246 - reviews still in progress

So I did the following:

select e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status, d.deptstatus
from vw_DeptStatus d Left OUTER JOIN Employee e ON e.deptid = d.deptid LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') -- and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

Now this isn't bringing back what I want--If run the query w/out joining it up w/the view it brings back the following:

Cast - Atkins, Carl - 09935 - Chris Burke - Not Started
Cast - Bridges, James - 09929 - Chris Burke - In Progress
CNS - Cunningham, Kenton - 02100 - Kahle Rummer - Not Started
CNS - Mitchell, Bill - 06200 - Kahle Rummer - In Progress

Now what I really need it to do is (below are the results from my View joined in with my query) -- return all the dept's--regardless if they are finished. If they are complete I only need ONE row, not all the rows for that dept. (highlighted in RED) I added these rows in-- Doesn't have to be NULL but maybe a blank string.

Cast - Charlton, Maurice - 01313 - Chris Burke - In Progress - reviews still in progress
Cast - Dorsey, Steve - 02455 - Chris Burke - In Progress - reviews still in progress
CMP - NULL - NULL - Fred Grove - NULL - reviews are complete
CNS - Bennett, Mark - 09911 - Kahle Rummer - In Progress - reviews still in progress
CNS - Buckingham, Mark - 00964 - Kahle Rummer - In Progress - reviews still in progress
CRW - Eubanks, Kellie - 07599 - Rick Cramer - In Progress - reviews still in progress
CRW - Luikart, Tyler- 09908 - Rick Cramer - In Progress - reviews still in progress
MicroMll - NULL - NULL - Tim Cross - NULL - reviews are complete

I hope this makes sense to someone -- if you have any questions just ask me. Another note I need to bring this back in a DS --for Crystal Reports. So the outline looks like this:

Dept ID- Supervisor - Reviews still in Progress
Any unfinished Reviews for that dept

Dept ID- Supervisor- Reviews Complete
No data shown since COMPELTE

Dept ID- Supervisor - Reviews still in Progress
Any unfinished Reviews for that dept

still not resolved -- just wondering if anyone had any ideas.|||

I just had the same problem. I am also having a problem with a left outer join. Reading your post led me to try testing with and without a view. I created two simple tables and did the LO join. That worked fine. Creating a select * view based on the first table then using that view in the LO join as the left table gave the erroneous result again. It is something to do with the fact that there is a view in there. I'll post again if I solve it.

Regards,

Raymond.

|||

Got it,

I stuck this in the top of the code for the view:

SELECT TOP 100 PERCENT *

This was instead of SELECT *. Replacing SELECT * with select colA, colB also worked.

I got the idea from the MS site:http://support.microsoft.com/kb/321541

Below is a snip of some of the text from the site. Putting DISTINCT in the select clause did not work BTW. Didn't try GROUP BY.

Cheers,

Raymond.

------snip-----

You can reproduce the behavior when all the following conditions exist:
? You use a query that contains an outer join, and you use a view (directly or indirectly) on the inner side of the outer join.
? The view contains an expression in its SELECT list that references one or more base table columns. Also, if all base table column values are NULL, the expression returns non-NULL results.
? The view is a simple view that does not contain a DISTINCT, TOP, or GROUP BY aggregate.

------snip-----

sql

query performance question regarding ISNULL

I have a sql statement:
SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5
,
A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST
,
A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2N
D,
A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH, A1.SUBPRICE
4TH,
A1.EXPDATE4TH
FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2 ON (A1.OPPORTUNITYID = A2.OPPORTU
NITYID)
INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
WHERE A4.ACCOUNTID = 'A6UJ9A0069NH'
AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN
')
AND A1.PROD_PR_GRP_C = 'C167'
ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
This query runs slower than:
SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5
,
A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST
,
A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2N
D,
A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH, A1.SUBPRICE
4TH,
A1.EXPDATE4TH
FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2 ON (A1.OPPORTUNITYID = A2.OPPORTU
NITYID)
INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
WHERE A4.ACCOUNTID = 'A6UJ9A0069NH'
AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
USERID = 'ADMIN')
AND A1.PROD_PR_GRP_C = 'C167'
ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
As you can see, the only difference is checking the field for null and conve
rting
it to a blank string. You'd think the second one involving "more work" woul
d
take longer. Can someone explain why this is so?
Thanks in advance.
Jiho Han
jihohan@.yahoo.comWhat do you mean, slower? Considerably, or minimally? Can you check the
plan, or post the plan using SET SHOWPLAN_TEXT ON? It will probably reveal
the reason if it is considerable.
You might also try rewriting

> AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
> USERID = 'ADMIN')
as
AND EXISTS ( SELECT 1
FROM C_USERMETROS
WHERE C_USERMETROS.USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO)
assuming that there is not a A3.METRO that actually equals ''
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>I have a sql statement:
> SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
> A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5
> A3_USERFIELD5,
> A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q,
> A1.DISTPRICE1ST,
> A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND,
> A1.EXPDATE2ND,
> A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH,
> A1.SUBPRICE4TH, A1.EXPDATE4TH FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2
> ON (A1.OPPORTUNITYID = A2.OPPORTUNITYID)
> INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
> INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
> WHERE A4.ACCOUNTID = 'A6UJ9A0069NH' AND A3.METRO IN (SELECT PRICINGMETRO
> FROM C_USERMETROS WHERE USERID = 'ADMIN')
> AND A1.PROD_PR_GRP_C = 'C167'
> ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
> This query runs slower than:
> SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
> A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5
> A3_USERFIELD5,
> A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q,
> A1.DISTPRICE1ST,
> A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND,
> A1.EXPDATE2ND,
> A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH,
> A1.SUBPRICE4TH, A1.EXPDATE4TH FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2
> ON (A1.OPPORTUNITYID = A2.OPPORTUNITYID)
> INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
> INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
> WHERE A4.ACCOUNTID = 'A6UJ9A0069NH' AND ISNULL(A3.METRO, '') IN (SELECT
> PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN') AND
> A1.PROD_PR_GRP_C = 'C167'
> ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
> As you can see, the only difference is checking the field for null and
> converting it to a blank string. You'd think the second one involving
> "more work" would take longer. Can someone explain why this is so?
> Thanks in advance.
> Jiho Han
> jihohan@.yahoo.com
>|||Hello Louis,
I am attaching the plan. It's the first time I've used that option so, I di
dn't know if this will be in the right format.
Also, I've tried the EXISTS approach and the result is same (I mean the perf
ormance). The plain option takes ~150ms(duration) vs ISNULL takes ~30ms. S
o it's not a huge difference but
it's significant enough that it's noticeable.
Thank you
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> What do you mean, slower? Considerably, or minimally? Can you check
> the plan, or post the plan using SET SHOWPLAN_TEXT ON? It will
> probably reveal the reason if it is considerable.
>
> You might also try rewriting
>
> as
> AND EXISTS ( SELECT 1
> FROM C_USERMETROS
> WHERE C_USERMETROS.USERID = 'ADMIN'
> AND PRICINGMETRO = A3.METRO)
> assuming that there is not a A3.METRO that actually equals ''
>
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>|||ISNULL (the function) is not equivalent to IS NULL (the syntax construct) in
SQL 2000 or 2005 to its optimizer.
You might try to use the latter since it is more completely supported.
Essentially, we're not reasoning about the output distribution on ISNULL().
That may cause suboptimal plans in complex queries.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b661698c806aa7485348c@.msnews.microsoft.com...
Hello Louis,
I am attaching the plan. It's the first time I've used that option so, I
didn't know if this will be in the right format.
Also, I've tried the EXISTS approach and the result is same (I mean the
performance). The plain option takes ~150ms(duration) vs ISNULL takes
~30ms. So it's not a huge difference but it's significant enough that it's
noticeable.
Thank you
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> What do you mean, slower? Considerably, or minimally? Can you check
> the plan, or post the plan using SET SHOWPLAN_TEXT ON? It will
> probably reveal the reason if it is considerable.
> You might also try rewriting
>
> as
> AND EXISTS ( SELECT 1
> FROM C_USERMETROS
> WHERE C_USERMETROS.USERID = 'ADMIN'
> AND PRICINGMETRO = A3.METRO)
> assuming that there is not a A3.METRO that actually equals ''
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>|||Ok, that one just went over me completely.
I understand that ISNULL and IS NULL may not be equivalent - I would hope
not.
What I wanted to know was why
A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
runs faster than
ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID
= 'ADMIN')
What I did notice when viewing the execution plan was that while using ISNUL
L
as above caused the query engine to use a hash match, the plain one caused
the engine to use a distinct sort followed by a nested loop.
I am very curious as to why it works this way and to determine whether I
need to employ ISNULL in all my other queries for query performance improvem
ent.
Thanks for your assistance!
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> ISNULL (the function) is not equivalent to IS NULL (the syntax
> construct) in SQL 2000 or 2005 to its optimizer.
> You might try to use the latter since it is more completely supported.
> Essentially, we're not reasoning about the output distribution on
> ISNULL(). That may cause suboptimal plans in complex queries.
> Conor Cunningham
> SQL Server Query Optimization Development Lead
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b661698c806aa7485348c@.msnews.microsoft.com... Hello Louis,
> I am attaching the plan. It's the first time I've used that option
> so, I didn't know if this will be in the right format.
> Also, I've tried the EXISTS approach and the result is same (I mean
> the performance). The plain option takes ~150ms(duration) vs ISNULL
> takes ~30ms. So it's not a huge difference but it's significant
> enough that it's noticeable.
> Thank you
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@.infinityinfo.com
> www.infinityinfo.com|||It would be interesting to see what the execution plan would by if you
changed that portion of the query to
A3.METRIO IN (SLECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'Admin'
AND PRICINGMETRO IS NOT NULL)
This is is just a wild guess, but maybe SQL is optimizing based on the fact
that ISNULL can't return a null result, and therefore can exclude null
results from the sub-query.
> What I wanted to know was why
> A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
> runs faster than
> ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERI
D
> = 'ADMIN')
> What I did notice when viewing the execution plan was that while using ISN
ULL
> as above caused the query engine to use a hash match, the plain one caused
> the engine to use a distinct sort followed by a nested loop.
> I am very curious as to why it works this way and to determine whether I
> need to employ ISNULL in all my other queries for query performance improvement.[/
color]|||Apologies - let me try to rephrase.
The query optimizer uses a tree model of operations that represent your SQL
statement. Additionally, statistical information (such as a histogram of
data for a column) is recorded for base tables and then pushed up through
the tree, being modified during each step. So, if you scan rows from a base
table and then filter, the filter operation would have an estimated output
distribution *after* the filter. This information is used to estimate
cardinality for each operator and to eventually cost various alternatives.
If the cardinality estimate is high, we may pick things like the hash join.
If it's low, we're more likely to pick a nested loops join. So, this
information is very important to picking an efficient plan.
Some constructs do not have full support in the optimizer. When we do not
have that information, we may not be able to come up with a good output
distribution for it, and thus the cardinality and cost may be incorrect.
ISNULL() is such an operator. When this happens, we may pick the loops join
when the hash join would have been better. This is when you see performance
issues.
In this case, you are using it to join with a subquery, and the distribution
is quite important here. I would recommend that you try to avoid this if
you are seeing plan issues. Once you start using the result of the function
is some other operation (a join, a subquery, a filter, etc), then I would
recommend that you consider avoiding it, if possible.
I hope that helps.
Thanks,
Conor
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b662c18c80742337e046d@.msnews.microsoft.com...
> Ok, that one just went over me completely.
> I understand that ISNULL and IS NULL may not be equivalent - I would hope
> not.
> What I wanted to know was why
> A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
> runs faster than
> ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
> USERID = 'ADMIN')
> What I did notice when viewing the execution plan was that while using
> ISNULL as above caused the query engine to use a hash match, the plain one
> caused the engine to use a distinct sort followed by a nested loop.
> I am very curious as to why it works this way and to determine whether I
> need to employ ISNULL in all my other queries for query performance
> improvement.
> Thanks for your assistance!
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@.infinityinfo.com
> www.infinityinfo.com
>
>|||The option you specified came back slower actually. ~250 ms.
I've done several variations using IS NULL:
A3.METRO IS NOT NULL AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS
WHERE USERID = 'ADMIN')
A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN'
AND PRICINGMETRO IS NOT NULL)
A3.METRO IS NOT NULL AND IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
USERID = 'ADMIN' AND PRICINGMETRO IS NOT NULL)
They are all slower and are around ~250 ms. It seems to me that by adding
IS NOT NULL check, it's actually adding more work.
Nothing beats:
ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID
= 'ADMIN')
which consistently executes around ~30ms.
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> It would be interesting to see what the execution plan would by if you
> changed that portion of the query to
> A3.METRIO IN (SLECT PRICINGMETRO FROM C_USERMETROS WHERE USERID =
> 'Admin' AND PRICINGMETRO IS NOT NULL)
> This is is just a wild guess, but maybe SQL is optimizing based on the
> fact that ISNULL can't return a null result, and therefore can exclude
> null results from the sub-query.
>|||Thanks Conor, that was very helpful although I can't say that I can absorb
everything you've said.
Just to be clear, and since I am wondering whether there is a misunderstandi
ng
here, I am reporting that the use of ISNULL() is performing better than the
lack of, or using IS NULL. If you see my other reply, you'll see some numbe
rs
I got trying to use IS NULL instead and they are all slower, even more so
than not using IS NULL.
Also, could you rephrase your last paragraph? Are you saying that I should
avoid using subqueries? or ISNULL()?
Are you suggesting that I use ISNULL() only in SELECT clause in your last
statement?
Thanks
Jiho
> Apologies - let me try to rephrase.
> The query optimizer uses a tree model of operations that represent
> your SQL statement. Additionally, statistical information (such as a
> histogram of data for a column) is recorded for base tables and then
> pushed up through the tree, being modified during each step. So, if
> you scan rows from a base table and then filter, the filter operation
> would have an estimated output distribution *after* the filter. This
> information is used to estimate cardinality for each operator and to
> eventually cost various alternatives. If the cardinality estimate is
> high, we may pick things like the hash join. If it's low, we're more
> likely to pick a nested loops join. So, this information is very
> important to picking an efficient plan.
> Some constructs do not have full support in the optimizer. When we do
> not have that information, we may not be able to come up with a good
> output distribution for it, and thus the cardinality and cost may be
> incorrect. ISNULL() is such an operator. When this happens, we may
> pick the loops join when the hash join would have been better. This
> is when you see performance issues.
> In this case, you are using it to join with a subquery, and the
> distribution is quite important here. I would recommend that you try
> to avoid this if you are seeing plan issues. Once you start using the
> result of the function is some other operation (a join, a subquery, a
> filter, etc), then I would recommend that you consider avoiding it, if
> possible.
> I hope that helps.
> Thanks,
> Conor
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b662c18c80742337e046d@.msnews.microsoft.com...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:587012
Jiho Han wrote:
> Thanks Conor, that was very helpful although I can't say that I can absorb
> everything you've said.
> Just to be clear, and since I am wondering whether there is a misunderstan
ding
> here, I am reporting that the use of ISNULL() is performing better than th
e
> lack of, or using IS NULL. If you see my other reply, you'll see some num
bers
> I got trying to use IS NULL instead and they are all slower, even more so
> than not using IS NULL.
> Also, could you rephrase your last paragraph? Are you saying that I shoul
d
> avoid using subqueries? or ISNULL()?
> Are you suggesting that I use ISNULL() only in SELECT clause in your last
> statement?
> Thanks
> Jiho
I am not Conor, but I will answer it anyway.
The advice of Conor is to avoid using functions/expressions in
combination with subqueries, because the optimizer will have better
information if you only use the column.
However, if I understand you correctly, in your case, the optimizer is
picking a faster plan for the ISNULL() query. If I undestand you
correctly, the query
AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS
WHERE USERID = 'ADMIN')
uses a hash match to finish in ~30ms
and the query
AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID =
'ADMIN')
uses a distinct sort and nested loop to finish in ~150ms
So although the optimizer has better information for the second query,
it actually picks a query plan that performs worse. Aparently, in your
situation, the hash match is the fastest solution. Maybe the optimizer
is unable to accurately estimate the output of the subquery (maybe it
returns more distinct values than expected).
You could also try to rewrite it differently, and see if that helps.
For example, you could try this:
AND EXISTS (
SELECT *
FROM C_USERMETROS
WHERE USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO
)
If the subquery returns unique values for PRICINGMETRO, then you could
also try this:
INNER JOIN C_USERMETROS
ON USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO
If the optimizer is actually misjudging the result from the subquery,
then it could help if you added an index on C_USERMETROS (USERID,
PRICINGMETRO).
HTH,
Gert-Jan