Out of the two sql queries below , suggest which one is better one. Single query with join or two simple queries?

Assuming result of first query in A) (envelopecontrolnumber,partnerid,docfileid) = (000000400, 31,35)

A)

select envelopecontrolnumber, partnerid, docfileid 
from envelopeheader 
where envelopeid ='LT01ENV1107010000050';

select count(*) 
from envelopeheader 
where envelopecontrolnumber = '000000400' 
  and partnerid= 31 and docfileid<>35 ;

or

B)

select count(*)  
from envelopeheader a 
join envelopeheader b on a.envelopecontrolnumber = b.envelopecontrolnumber 
                       and a.partnerid= b.partnerid 
                       and a.envelopeid = 'LT01ENV1107010000050' 
                       and b.docfileid <> a.docfileid;

I am using the above query in a sql function. I tried the queries in pgAdmin(postgres), it shows 16ms for A) and B). When I tried queries from B) separately on pgadmin. It still shows 16 ms separately for each one - making 32ms for B) - Which is wrong because when you run both the queri开发者_JS百科es in one go from B), it shows 16 ms. Please suggest which one is better. I am using postgres database.

The time displayed includes time to :

• send query to server
• parse query
• plan query
• execute query
• send results back to client
• process all results

Try a simple query like "SELECT 1". You'll probably get 16 ms too.

It's quite likely you are simply measuring the ping time to your server.

If you want to know how much time on the server a query uses, you need EXPLAIN ANALYZE.

Option 1:

 Run query A.
 Get results.

 Use these results to create query B.

 Send query B.
 Get results.

Option 2:

 Run combined query AB.
 Get results.

So, if you are using this from a client, connecting to Postgres, use the second option. There is an overhead for sending a query to the db and getting results back.

If you are using it inside an SQL function or procedure, the difference is probably negligible. I would still use the second option though. And in either case, I would check that queries B or AB are optimized (checked query plan, if indexes are used, etc).

Go option 1: the two queries are unrelated, so more efficient to do them separately.

Option A will be faster since you are interested in the count.

The join will create a temporary structure for join the data based on conditions and then performs the counting operation. Hence option A is better and faster.