change "count(*)" into "exists"

[VBmim]

Hello all,

Consider the following tables:
A: 1.000 records
B: 5.000.000+ records each referring to one A record
Pretty basic I think.

I have a query now that looks like this:

select A.c1, A.c2, A.c3
(select count(*) from B where B.id = A.idFromB
and B.c1 = somevalue and B.c2 = somevalue) computed1
from A

This query is performing rather badly because of the count(*). The only reason for using the count is to know IF there are records, not HOW MANY. So I wanted to change my count(*) into an exists, and there I am stuck.

I tried:
select A.c1, A.c2, A.c3
exists (select B.id from B where B.id = A.idFromB
and B.c1 = somevalue and B.c2 = somevalue) computed1
from A

select A.c1, A.c2, A.c3
if (exists (select B.id from B where B.id = A.idFromB
and B.c1 = somevalue and B.c2 = somevalue) true then 1 else 0 end computed1
from A

This all fails.

Is there a way to use exists in a subquery in the select clause? What other things could I consider to change the count(*) into something more performant?

Kind greetings,

Mim

 

[swampBoogie]

select A.c1, 
       A.c2, 
       A.c3,
       case when exists (select 1 
                           from B
                          where B.id = A.idFromB
                            and B.c1 = somevalue 
                            and B.c2 = somevalue) 
            then 1 else 0 end as computed1
  from A

 

[VBmim]

Fantastic!

I also tried the case before, but for some reason my syntax was wrong...

Thanks a lot.

Mim

 

[VBmim]

I have tried the above modifications and my query went from an average of 22.5 seconds to 3.5 seconds, which is exactly what I wanted.

But what I am not understanding is the query plan. For my count(*) query I have an estimated subtree cost of 0.284, for my exists query this value is 25.8 . With the client statistics I see that the wait time for my count(*) query is almost as big as the total time (22.4 s), so this is probably the reason why my original query wasn't performing well. Is this wait time not included in the execution plan?

Does anyone has a good reference where to find information about how to read the execution plan correctly?

 

[DjangMan]

Out of curiosity, does this run faster on your data? I did a test on some simple tables that returned only 2061 rows but ISNULL completed faster than using EXISTS.

SELECT A.c1,
       A.c2,
       A.c3,
       ISNULL((SELECT TOP 1 1
               FROM   B
               WHERE  B.id = A.idFromB
                      AND B.c1 = somevalue
                      AND B.c2 = somevalue), 0) AS computed1
FROM   A

 

[VBmim]

Hello,

On my database, this is hardly any faster. I see the same results for exists and isnull.

Mim