An Inner SubQuery Processing

Question

An Inner SubQuery Processing

Dilip Nagle

Old Hand

Points: 340
More actions
March 21, 2005 at 10:27 am

#68398

1. I have about 110,000 entries in my bills raised table called tbl_bill. I store cheques received against these bills in a separate table called tbl_cheqrecd which has about 65,000 entries. Obviously not all bills have corresponding entries in tbl_cheqrecd.
2. Now I want to find Bills pending for a specific client. And I have created following query which gives me correct result:
select a.docno,a.docdt,a.billamt,a.clientac,
(select Isnull(sum(cheqamt),0) from tbl_cheqrecd as b where a.docno = b.docno) as cheqamt
from tbl_bill as a
where a.clientac = @p_clientac and a.billamt > (select Isnull(sum(cheqamt),0) from tbl_cheqrecd as b where a.docno = b.docno)
[Note: @p_clientac is a parameter recd by stored procedure]
3. My question: How will this query get processed? i.e.
a) will it first filter records for the desired client and then resolve inner query for each row of the result set of outer query? OR
b) will it first resolve inner query for each row in bill file and then apply the where clause to give the result set?
This is important to know from performance point of view.
Regards,
Dilip Nagle
March 24, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 1

Hi Dilip,

I think that you should see the execution plan for the SP. Looking at the SQL statement, it seems to me that SQL Server will use nested loop, but it may not be correct.

Now, about the performance: you didn't post the full table structure, but I will try to write an equivalent SQL with (I hope) a more obvious execution plan:

SELECT
  tmp_a.docno, a.docdt, a.billamt, @p_clientac AS clientac
FROM
  (SELECT b.docno, SUM(cheqamt) AS check_amt
   FROM
     (SELECT docno FROM tbl_bill WHERE clientac = @p_client
     ) AS b
     LEFT OUTER JOIN
     tbl_cheqrecd AS c ON (c.docno = b.docno)
   GROUP BY
      b.docno
  ) AS tmp_a
  INNER JOIN tbl_bill AS a ON (a.docno = tmp_a.docno)
WHERE
  (a.billamt > check_amt)

So, the most inner query (aliased as b) filters all bills for the specified client, its enclosing query (aliased as tmp_a) returns a result set containing the docno and the sum of corresponding check amounts. The top level query joins tmp_a to the actual bill data, only to obtain the bill-related data and the billamt column, which is used to evaluate the filter.

I hope this helped,

Regards,

Goce.

Dilip Nagle Old Hand Points: 340 More actions · Answer 2

Dear Goce,

Thanks for your reply, I have tried it and it works faster than the query that I have written.

Dilip Nagle