SEO

hi!!!

the following is the sql which is veri slow cos of the 'Not In' clause,
would appreciate if u anyone can suggest any other way to bring about
the same result

SELECT Id, LOC, AGENCY, BATCH
FROM tblRows
WHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCY
FROM tblRows AS A, tblRows AS B
WHERE A.LOC = "B"
AND B.AGENCY = A.AGENCY
AND B.BATCH = A.BATCH)

tblRows
ID LOC AGENCY BATCH
1 B 1000 WAD
2 R 1000 WAD
3 B 1010 QAD
4 B 1020 WAD
5 R 1020 WAD
6 R 1030 RRR
7 I 1030 RRR
8 V 1030 RRR
9 B 1040 UIA
10 R 1040 UIA
11 I 1040 UIA
12 V 1040 UIA

the subquery is to return the rows with LOC = B. the above query as the
whole should return the rows where LOC <> b and also must exclude rows
belong to the LOC = B subset (that is for example the first two rows
with ID 1 and 2. the first row with ID 1 has LOC as B, Agency as 1000
and BATCH as WAD. the second row with Id 2 has LOC as R as the same
AGENCY and BATCH as first row with ID 1 so is the subset of first row.
similarly row with Id's 4 and 5.

the above query must return the following the rows (that is we can say
the orphan rows which doesn't have LOC AS B nor belongs to the B's
subset

6 R 1030 RRR
7 I 1030 RRR
8 V 1030 RRR

hope i am clear in my explanation and would appreciate if someone can
point me in the right direction. the reason for posting this in ms
access group is because this is going to be a query in MS Access.

regards
bala

Consinder using NOT EXISTS and creating helpful indexes. For example:

CREATE TABLE tblRows
(
ID int NOT NULL,
LOC char(1) NOT NULL,
AGENCY int NOT NULL,
BATCH char(3) NOT NULL
)
INSERT INTO tblRows VALUES(1,'B',1000,'WAD')
INSERT INTO tblRows VALUES(2,'R',1000,'WAD')
INSERT INTO tblRows VALUES(3,'B',1010,'QAD')
INSERT INTO tblRows VALUES(4,'B',1020,'WAD')
INSERT INTO tblRows VALUES(5,'R',1020,'WAD')
INSERT INTO tblRows VALUES(6,'R',1030,'RRR')
INSERT INTO tblRows VALUES(7,'I',1030,'RRR')
INSERT INTO tblRows VALUES(8,'V',1030,'RRR')
INSERT INTO tblRows VALUES(9,'B',1040,'UIA')
INSERT INTO tblRows VALUES(10,'R',1040,'UIA')
INSERT INTO tblRows VALUES(11,'I',1040,'UIA')
INSERT INTO tblRows VALUES(12,'V',1040,'UIA')
GO

ALTER TABLE tblRows
ADD CONSTRAINT PK_tblRows PRIMARY KEY(ID)
GO

CREATE INDEX Index1 ON tblRows(LOC, AGENCY, BATCH)
GO

SELECT Id, LOC, AGENCY, BATCH
FROM tblRows B
WHERE NOT EXISTS
(
SELECT *
FROM tblRows AS A
WHERE A.LOC = 'B'
AND B.AGENCY = A.AGENCY
AND B.BATCH = A.BATCH
)


--
Hope this helps.

Dan Guzman
SQL Server MVP

"bala" <balkiir@gmail.com> wrote in message
news:1110334059.973692.269180@z14g2000cwz.googlegr oups.com...
> hi!!!
>
> the following is the sql which is veri slow cos of the 'Not In' clause,
> would appreciate if u anyone can suggest any other way to bring about
> the same result
>
> SELECT Id, LOC, AGENCY, BATCH
> FROM tblRows
> WHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCY
> FROM tblRows AS A, tblRows AS B
> WHERE A.LOC = "B"
> AND B.AGENCY = A.AGENCY
> AND B.BATCH = A.BATCH)
>
> tblRows
> ID LOC AGENCY BATCH
> 1 B 1000 WAD
> 2 R 1000 WAD
> 3 B 1010 QAD
> 4 B 1020 WAD
> 5 R 1020 WAD
> 6 R 1030 RRR
> 7 I 1030 RRR
> 8 V 1030 RRR
> 9 B 1040 UIA
> 10 R 1040 UIA
> 11 I 1040 UIA
> 12 V 1040 UIA
>
> the subquery is to return the rows with LOC = B. the above query as the
> whole should return the rows where LOC <> b and also must exclude rows
> belong to the LOC = B subset (that is for example the first two rows
> with ID 1 and 2. the first row with ID 1 has LOC as B, Agency as 1000
> and BATCH as WAD. the second row with Id 2 has LOC as R as the same
> AGENCY and BATCH as first row with ID 1 so is the subset of first row.
> similarly row with Id's 4 and 5.
>
> the above query must return the following the rows (that is we can say
> the orphan rows which doesn't have LOC AS B nor belongs to the B's
> subset
>
> 6 R 1030 RRR
> 7 I 1030 RRR
> 8 V 1030 RRR
>
> hope i am clear in my explanation and would appreciate if someone can
> point me in the right direction. the reason for posting this in ms
> access group is because this is going to be a query in MS Access.
>
> regards
> bala
>

hi dan

thanx for the response. i did try with 'WHERE NOT EXISTS' but didn't
return any row. i am stumped now

thanx buddy
bala

I get the following results when I run the script I posted so I'm not sure
how your environment differs:

Id LOC AGENCY BATCH
----------- ---- ----------- -----
7 I 1030 RRR
6 R 1030 RRR
8 V 1030 RRR

--
Hope this helps.

Dan Guzman
SQL Server MVP

"bala" <balkiir@gmail.com> wrote in message
news:1110368212.091319.272270@l41g2000cwc.googlegr oups.com...
> hi dan
>
> thanx for the response. i did try with 'WHERE NOT EXISTS' but didn't
> return any row. i am stumped now
>
> thanx buddy
> bala
>

Your problem is a common generic performance problem

-- old syntax used before SQL 92 outer joins available
SELECT *
FROM A
WHERE A.id NOT IN (SELECT id FROM B)

-- new syntax using SQL 92 joins - can runs 100/1000s of times faster
SELECT *
FROM A LEFT JOIN B ON B.id = A.id
WHERE B.id IS NULL

The theory is simple do an outer join using all rows from table A and filter
them to show only rows where B.id is NULL (= no corresponding row in B)

In your example

SELECT Id, LOC, AGENCY, BATCH
FROM tblRows R
LEFT JOIN (SELECT DISTINCT B.AGENCY
FROM tblRows AS A, tblRows AS B
WHERE A.LOC = "B"
AND B.AGENCY = A.AGENCY
AND B.BATCH = A.BATCH) R2
ON R.AGENCY = R2.AGENCY
WHERE R2.AGENCY IS NULL

NOTE: I did not test the queries so - if it does not work it just means that
you need a RIGHT JOIN, as I do not always remember which way round the
syntax goes - the logic however is correct.

Hope this help
Julian 8^)

Change to a JOIN,
"bala" <balkiir@gmail.com> wrote in message
news:1110334059.973692.269180@z14g2000cwz.googlegr oups.com...
> hi!!!
>
> the following is the sql which is veri slow cos of the 'Not In' clause,
> would appreciate if u anyone can suggest any other way to bring about
> the same result
>
> SELECT Id, LOC, AGENCY, BATCH
> FROM tblRows
> WHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCY
> FROM tblRows AS A, tblRows AS B
> WHERE A.LOC = "B"
> AND B.AGENCY = A.AGENCY
> AND B.BATCH = A.BATCH)
>
> tblRows
> ID LOC AGENCY BATCH
> 1 B 1000 WAD
> 2 R 1000 WAD
> 3 B 1010 QAD
> 4 B 1020 WAD
> 5 R 1020 WAD
> 6 R 1030 RRR
> 7 I 1030 RRR
> 8 V 1030 RRR
> 9 B 1040 UIA
> 10 R 1040 UIA
> 11 I 1040 UIA
> 12 V 1040 UIA
>
> the subquery is to return the rows with LOC = B. the above query as the
> whole should return the rows where LOC <> b and also must exclude rows
> belong to the LOC = B subset (that is for example the first two rows
> with ID 1 and 2. the first row with ID 1 has LOC as B, Agency as 1000
> and BATCH as WAD. the second row with Id 2 has LOC as R as the same
> AGENCY and BATCH as first row with ID 1 so is the subset of first row.
> similarly row with Id's 4 and 5.
>
> the above query must return the following the rows (that is we can say
> the orphan rows which doesn't have LOC AS B nor belongs to the B's
> subset
>
> 6 R 1030 RRR
> 7 I 1030 RRR
> 8 V 1030 RRR
>
> hope i am clear in my explanation and would appreciate if someone can
> point me in the right direction. the reason for posting this in ms
> access group is because this is going to be a query in MS Access.
>
> regards
> bala
>

hey julian

thanx for the pointer, i have tried different combination of outer
joins but then it is kinda messy and not much difference in
performance. the best bet is start from the scratch.

regards
bala

Bala,

You could try using NOT EXISTS. For example:

SELECT Id, LOC, AGENCY, BATCH
FROM tblRows
WHERE NOT EXISTS (
SELECT 1
FROM tblRows A
WHERE A.Loc = 'B'
AND A.Agency = tblRows.Agency
AND A.Batch = tblRows.Batch
)

The query would benefit from a (clustered) index on (Loc,Agency,Batch).

Hope this helps,
Gert-Jan


bala wrote:
>
> hey julian
>
> thanx for the pointer, i have tried different combination of outer
> joins but then it is kinda messy and not much difference in
> performance. the best bet is start from the scratch.
>
> regards
> bala

Hi Bala,

As Dan pointed out to you. A index { Index1} would speedup your query.
Outer join will never work, It is nothing to do with syntax. Infact it
will change the logic of the query.

Try out creating index and let me know. Could you please set SET
STATISTICS IO ON and show me the stats before & after index creation.

Thank you
Raju