Multiple level nested Corelated query

[Zero.NULL]

My multiple level nested corelated query is not fetching correct
result. It work fine on small set of data, but fails on larger set of
data. Any clue?

Explaining data storing and discussing design would be tough for me
here, still to show you how complex I have created my life, here is the
query:

select
(
SELECT Top 1 RowNSBranchID FROM AssoExtBranchToNSBranchMstM AM
-- MMM
WHERE AM.RowExtSysID IN
(
SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =
(
SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' AND
RowID =
(
SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2 AND
ExtSysCode = UM.SystemCode
)
)
)
AND RowExtBranchID IN
(
SELECT RowID FROM ExternalBranchMstM
WHERE ExtBranchID =
(
SELECT ExtBranchID FROM ExternalBranchMstM
WHERE ROWID =
(
SELECT RowID FROM ExternalBranchMstM
WHERE ROWID =
(
SELECT MAX(ROWID) FROM ExternalBranchMstM WHERE MCStatus = 2 AND
ExtBranchCode = UM.UpBranchCode
AND RowExtSysID IN
(
SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =
(
SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' AND
RowID =
(
SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2
AND ExtSysCode = UM.SystemCode
)
)
)
)
AND (SF = 'Active')
)
)
)
AND AM.SF = 'Active'
order by AssoID desc,TrackID desc
) nsbranchid, UM.*

from
TmpInProcessData062005MstM UM

[Hugo Kornelis]

On 30 Sep 2005 04:11:34 -0700, Zero.NULL wrote:

My multiple level nested corelated query is not fetching correct
result. It work fine on small set of data, but fails on larger set of
data. Any clue?

Explaining data storing and discussing design would be tough for me
here, still to show you how complex I have created my life, here is the
query:

(snip)

Hi Zero.NULL,

Wow! I mean, like, WOW!!!!

Sheesh, you sure know how to make simple things complicated.
After staring in amazement at this code -no, make that: this disaster
waiting to happen- for a while, I can only conclude that I'm very sure
that it's possible to simplify this code considerably. But not without
knowing more. At the very least, please post the table structure of all
tables included in the query, posted as CREATE TABLE statements. You can
omit columns that are irrelevant for the query, but do include all
constraints (esp. primary keys, unique, nullability, and foreign keys
are very important).

If possible, include some rows of sample data (as INSERT statements) to
help clarify what the query is supposed to do, and/or a briefe
explanation of the business problem you're trying to solve.

Check out www.aspfaq.com/5006 for more details on the best ways to aks
for help in these groups.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Zero.NULL]

Hugo,

I accept your critisism in this case, as I have already accepted that I
have made my life complicated. I think you can give your valuable
comment on this. I will construct my design points to post here and
will discuss with you soon on this.

[Hugo Kornelis]

On 2 Oct 2005 23:07:33 -0700, Zero.NULL wrote:

Hugo,

I accept your critisism in this case, as I have already accepted that I
have made my life complicated. I think you can give your valuable
comment on this. I will construct my design points to post here and
will discuss with you soon on this.

Hi Zero.NULL,

Re-reading my message, I feel that I have to apologize. I tried to get a
message across, but I chose the wrong tone. What can I say? It was
friday, and it was late at night - but still. I now wish I had re-read
my message before clicking the Send button.

I'm looking forward to your next post - I hope I can help you sort
things out and improve your code!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[jsfromynr]

Hi Hugo,
As always you are being a gentleman and willing to provide
help. The above query can be converted into an inner join.
Something like this :--
Select * From EmpMst Where deptid in
( Select deptid from deptmst where deptname='acc')
-----------------
into This ----
--------------
Select * From EmpMst EM
Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'

I have one problem while optimizing the sql query .For a few rows the
query works perfectly ,but as the number of rows increases it works but
gives wrong result.
I am using nested queries W/O aliasing . So what I assume is that Query
Optimizer is trying to flatten the query (converting it into joins)
and in the process ,because of no Alias Name takes a long time .

What might be the reasons for performance debacle? Following is what I
am using
1. Views (using *) ----------
2. Indexing (Clustered)
3. History Data (2 Billion Rows)
4. No Indexed views
5. Scalar Functions ( a bit For format Checking like All
alphabets,digits etc)
6. No Cursor
7. Updating a permanent temp table for intermediate results.

With Warm regards
Jatinder Singh

[Hugo Kornelis]

On 4 Oct 2005 01:33:08 -0700, jsfromynr wrote:

Hi Hugo,
As always you are being a gentleman and willing to provide
help. The above query can be converted into an inner join.
Something like this :--
Select * From EmpMst Where deptid in
( Select deptid from deptmst where deptname='acc')
-----------------
into This ----
--------------
Select * From EmpMst EM
Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc' (snip)

Hi Jatinder,

My apologies for the delayed reply. Real life and other obligations have
been interfering.

The two queries above are not exactly equivalent.

First, the SELECT * (which should never be used in production code,
unless as part of an EXISTS subquery) will produce more columns in the
second query.

Second, the second query might also produce more rows. This will NOT
happen if you have a PRIMARY KEY or UNIQUE constraint on DeptMst.DeptID,
but it will happen if you have no such constraint. If there are three
rows in DeptMst with the same DeptID value, then each row from EmpMst
with that value in EmpMst.DeptID will be tripled in the second query; it
will still be output only once in the first.

I have one problem while optimizing the sql query .For a few rows the
query works perfectly ,but as the number of rows increases it works but
gives wrong result.
If the explanation above does not apply, then I'd have to see a repro
script in order to comment. See further below.

I am using (...)

(snip)

The description of your problem is too vague for me to comment on.
Please post actual table structures (as CREATE TABLE statements,
including all constraints and properties but excluding irrelevant extra
columns), some rows of sample data (as INSERT statements - and not all 2
billioin of'em, please - just enough to demonstrate the situation), the
actual query you've been using, the output you needed and the output you
actually got.

Also check out www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)