By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,353 Members | 1,529 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,353 IT Pros & Developers. It's quick & easy.

SQL Statement Tuning

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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" <ba*****@gmail.com> wrote in message
news:11**********************@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

Jul 23 '05 #2

P: n/a
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

Jul 23 '05 #3

P: n/a
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" <ba*****@gmail.com> wrote in message
news:11**********************@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

Jul 23 '05 #4

P: n/a
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" <ba*****@gmail.com> wrote in message
news:11**********************@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

Jul 23 '05 #5

P: n/a
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

Jul 23 '05 #6

P: n/a
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

Jul 23 '05 #7

P: n/a
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

Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.