SQL Performance Tuning

[bala]

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

[Larry Linson]

Use the Query Builder, join on AGENCY and set the criteria for Agency in
tblRows to Is Null. Indexing AGENCY in both Tables is likely to increase the
speed, as well. If you need the SQL to use in code, go to SQL view in the
Query Builder and copy it.

Larry Linson
Microsoft Access MVP

"bala" <ba*****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.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

[Justin Hoffman]

"bala" <ba*****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.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

When you explain your query, you mention rows 1 and 2 having the same AGENCY
and BATCH. When you run your query, you do not seem to care about BATCH
being the same. In other words if the BATCH column for row 2 was "BANANA",
row 2 would still be excluded because its AGENCY column is 1000 (and
AGENCY_1000 appears elsewhere in the table with a LOC of "B").
If this is OK and the query returns the right results, then why is the
subquery so complicated? Surely the following shows the rows we want to
exclude:
SELECT DISTINCT B.AGENCY FROM tblRows AS B WHERE B.LOC = "B"

and if this is being used as a sub-query, you do not need to worry about the
DISTINCT, so the whole query would be:

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

Generally speaking, these sort of subqueries should not be slow. So if
performance is still poor, look at the indexing of the table before you
throw out the sub-query idea. You could let us know how many rows the table
holds, how many rows the query returns and how long it took to run. This
will give us an idea of whether you could expect better results.
PS you also do not mention the location or type of back-end tables.

[bala]

hi justin

thanx for the response, it is appreciated.

well just to answer your question i want the combination of BATCH and
AGENCY to be unique but since just checking AGENCY would solved the
purpose i am using BATCH alone.

also regarding indexing - i already have ID column and AGENCY column
are both indexed.

it is taking more than 30 mins and most of the time the application
hangs when i use the 'NOT IN' keyword or even '<>' symbol, whereas if
remove the NOT IN or '<>' and just run it (just for checking the speed)
the resultset comes in seconds eventhough it retrieves 16,000 records.
in the actual resultset i want it the number of records wont be more
than 500.

hope this information helps.

since the number of rows are less and the query needn't be optimized
much i used subquery as it would solve the purpose but 'NOT IN' is
giving me weird problems. :)

bala

[bala]

hi larry

thanx for the response, it is appreciated.

well i always build my SQL statements manually.

let me give the query builder a shot and see whether i can the
statement right. will let u know if i am successful or not

regards
bala