Query with Subqueries vs individual queries

[GoTerps88]

I have a query with about 5 subqueries I am using for counts. Some of these subqueries have an inner join or two.

I am then reading the results in a recordset on a web form.

Would this be considered as efficient or more than opening 5 queries with 5 recordsets and displaying results on the form. These individual queries would have their own inner join or two.

My hunch is that the one inclusive query would represent better performance, but I'd like to hear other thoughts.

 

[SantaMufasa]

One test is worth 100 expert opinions.

First, in SQL*Plus:

set timing on

Then run both versions of the code, and post the results here.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[GoTerps88]

Great idea...

Except that doesn't account for opening and closing recordsets on the client side, which in all probability would account for a sizeable chunk of the processing. I guess I could run similar type performance testing.

 

[SantaMufasa]

Then in addition to "SET TIMING ON", you could also (on the client side) "SET TIME ON" for an approximation of the elapsed clock time for each query (including recordset opening/closing and network latency).

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[GoTerps88]

So far the results have supported using the query containing subqueries over individual queries. The larger query ran nearly as fast as one individual query. We're talking milliseconds if that of a difference.

However this was working with a limited number of records. I will next run a test on a larger recordset.

 

[SantaMufasa]

Cool. We'll be interested in how things go with that.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]