Very complex query

[Bill R via AccessMonster.com]

Here it is:

SELECT DMSID INTO tblFilteredDMSIDs
FROM qryRealDwgs
WHERE

Retire=0 AND [Current Location]<>21
AND DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "ND"

OR
Retire=0 AND [Current Location]<>21
AND DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "DDC"

The IN(SELECT queries look in tables that are represented by subforms in the
mainform, and the data is not in the recordsource of the mainform. This query
is the result of selections on a filter form. Depending on the other criteria
selected in the filter form, the rows of criteria (including the IN(SELECT
queries) can grow to 16 or more in number. The query parks the resultant
DMSIDs in a temp table so that once the job is done, the local temp table is
used as the filter in the mainform's recordsource from then on, until the
user decides to refilter the records.

It can be a painfully long wait while the query runs. Any suggestions?

Thanks,

BIll

 

[Rob Oldfield]

Will it always be the case that the select dmsid in(...) and dmsid not
in(...) will be the same for each of the OR sections?

 

[Bill R via AccessMonster.com]

Yes, but the IN(SELECT statements will be different depending on the
selections in the filter. The object of the IN(SELECT statements is to filter
the drawings list for those drawings that are open in a specific phase of the
drawing cycle.

Will it always be the case that the select dmsid in(...) and dmsid not
in(...) will be the same for each of the OR sections?

Here it is:

[quoted text clipped - 45 lines]

BIll

 

[Rob Oldfield]

Your problem is that those select commands in the subqueries are going to be
run multiple times: twice in the example you give, but up to sixteen times
in your extreme cases. Two ways around it:

First - rewrite the SQL so that it only needs to be run once. It will be
something like this (although you'd have to work the exact bracketing out
for yourself)

SELECT DMSID INTO tblFilteredDMSIDs
FROM qryRealDwgs
WHERE

(Retire=0 AND [Current Location]<>21
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "ND"

OR
Retire=0 AND [Current Location]<>21
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "DDC")

AND DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)

Way number 2 would again be to use a temp table. This way is slightly less
elegant but might be easier to put together. This would mean putting the
results of

DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)

into a temp table, and then replacing references to it in the main query
with references to that table.

One other thing - your tables are fully indexed? DMSID, PHID, Finish, and
the joining fields on the other tables are all indexed?

Yes, but the IN(SELECT statements will be different depending on the
selections in the filter. The object of the IN(SELECT statements is to filter
the drawings list for those drawings that are open in a specific phase of the
drawing cycle.

Will it always be the case that the select dmsid in(...) and dmsid not
in(...) will be the same for each of the OR sections?

Here it is:

[quoted text clipped - 45 lines]

BIll

 

[Bill R via AccessMonster.com]

Yes they are fully indexed and normalized. Your method #1 looks like the way
to go. I will implent it. Thanks a million.

Bill

Your problem is that those select commands in the subqueries are going to be
run multiple times: twice in the example you give, but up to sixteen times
in your extreme cases. Two ways around it:

First - rewrite the SQL so that it only needs to be run once. It will be
something like this (although you'd have to work the exact bracketing out
for yourself)

SELECT DMSID INTO tblFilteredDMSIDs
FROM qryRealDwgs
WHERE

(Retire=0 AND [Current Location]<>21
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "ND"

OR
Retire=0 AND [Current Location]<>21
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "DDC")

AND DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)

Way number 2 would again be to use a temp table. This way is slightly less
elegant but might be easier to put together. This would mean putting the
results of

DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)

into a temp table, and then replacing references to it in the main query
with references to that table.

One other thing - your tables are fully indexed? DMSID, PHID, Finish, and
the joining fields on the other tables are all indexed?

Yes, but the IN(SELECT statements will be different depending on the
selections in the filter. The object of the IN(SELECT statements is to filter

[quoted text clipped - 9 lines]