Well, here goes....
The SQL statement is:
SELECT o.IOCardPinID, o.IOCardOccurrenceID, (SELECT
ConnectorLabel FROM tblIOCardConnector WHERE
IOCardConnectorID = (SELECT IOCardConnector FROM
tblIOCardPin WHERE IOCardPinID = [o].[IOCardPinID]
& " - " & (SELECT IOCardPinLabel FROM tblIOCardPin WHERE
IOCardPinID = [o].[IOCardPinID]
& " < " & (SELECT
IOCardPinLabel FROM tblIOCardPin WHERE IOCardPinID =
(SELECT PinConnectsTo FROM tblIOCardPinConnectsTo WHERE
IOCardPinID = [o].[IOCardPinID]
& " >" AS P2Info FROM
tblIOCardPinOccurrence AS o INNER JOIN tblIOChassisSlot
AS s ON o.IOCardOccurrenceID = s.IOCardOccurrenceID WHERE
s.IOChassisSlotID = Forms!popPanelAttach!
lstVMICCardChoice And s.IOChassisID = Forms!
popPanelAttach!txtintChassis;
Now some background:
This database is managing circuit connections between
several circuit cards. This particular SQL is for a list
box to show the circuits associated with a particular
card. The particular card is selected in another listbox
(lstVMICCardChoice).
The card chosen has a connector with several pins
(terminals). These pins are connected to various channels
defined by the card (CH01HI, CH01LO, +5VDC, GND, etc).
To define a card:
tblIOCard had a primary Key of IOCardID (Autonumber).
This table has a record for each type of circuit card.
tblIOCardConnector has a primary key of IOCardConnectorID
(Autonumber). This table has a record for each connector
on each card. It has a second field to link this table
back to the tblIOCard by the IOCardID.
tblIOCardPin has a primary key of IOCardPinID
(AutoNumber). This table has a record for each pin in
each connector. It is linked back to the
tblIOCardConnector by a second field for
IOCardConnectorID.
tblIOCardPinConnectsTo has a primary key of IOCardPinID.
This is linked back to the IOCardPinID in tblIOCardPin.
It has a second field called PinConnectsTo. This is also
linked back to the IOCardPinID in tblIOCardPin.
With the first three tables, I can map every pin on every
card. The fourth table allows me to capture how the pins
are connected to one another on a card. This way I can
trace circuit paths.
These cards are housed in a 21-slot rack-mounted chassis.
There can be more than one instance (occurrence) of each
type of card.
I have a table called tblIOCardOccurrence with a primary
key of IOCardOccurrenceID (AutoNumber). This table
contains a record for every card in the chassis. It has a
second field that links this table back to the
[tblIOCard].[IOCardID]. So for every occurrence/instance,
I can determine what type of card and then all the
circuit paths for that card.
I have a table called tblIOChassisSlot with a primary key
of IOChassisSlotID (integers 1 through 21). This table
has a second field that links this table to
tblIOCardOccurrence with IOCardOccurrenceID. With this I
can see what type of card is in each slot.
Finally, there are interconnections between cards. This
is captured in tblIOCardPinOccurrence. This table has a
primary key of IOCardPinID and IOCardOccurrence. If you
have two or more of the same type of card in the chassis,
each will have the same IOCardPinIDs, so the combination
of the pin ID and Occurrence ID differentiates. This
table also has two more fields called PinMatesTo and
PinMatesToOccurrence. These capture card-to-card circuit
connections.
With all that, the first list box shows the list of
available cards and when one of those is chosen, the
second listbox processes the SQL above. The second list
box is to show all of the Pin Labels on the I/O connector
as well as the channel label associated with that
particular pin. This will allow the user to pick a
channel and make a connection to another pin on another
card (that coding and logic not part of this discusssion).
So the SQL grabs all of the Pin IDs from the
tblIOCardPinOccurrence where the Occurrence ID matches
that of the card picked in the first listbox. But the pin
ID is meaningless to the user, so the SQL also grabs the
label information associated with each Pin ID and that is
what gets displayed. It grabs the Connector Label and the
Pin Label and then it must look at what these pins are
connected to on the card (from the
tblIOCardPinConnectsTo). The connected to pin IDs are
determined, and then the label for those pin IDs are
pulled up (these labels contain the channel names).
The end result would look something like:
...
P2 - A12 < CH11HI >
P2 - A13 < CH12HI >
...
With all the subqueries, this take a while to run. I
currenly have the list box locked down along with a
progress meter based on the record count in the list box.
It keeps the user interface problems cured, but it take
approximately 10-15 seconds to run. The final record
count is 96. Watching the progress meter shows that the
meter is initiated, then stays at 0%, then jumps to 100%
and closes.
The individual bits are pretty straight forward, but the
total is a bit complex. Any suggestions or strategies for
breaking it up or improving the efficiency are
appreciated.
-dch
-----Original Message-----
Surely a better solution (rather than stopping the user clicking too soon),
would be to speed up the query?
Common causes of a query being slow are, insufficient indexes, and/or,
inefficiently written SQL.
Maybe show us the SQL? (Also state the primary key of each table involved.)
HTH,
TC
.
