Increase Query Speed

[Bev]

I am attempting to run a query and it runs forever and never appends records.

The CLMPRDFIL files are linked through an ODBC driver to a AS400 database.

The RCTCDP and RCTCDP files are very large.

I need help in minimizing the time it takes to run. I have ran it in the
past with better success.

I have all criteria and linked fields indexed.

SQL follows.


INSERT INTO [T002a MTM - Member claims] ( MEM_ID, DQP2NB, DQOINB, DQUJCD,
DQTCCD, S4COT3, DQIONB, DQIMNB, Filled, [Index], [Clm Cost], [Fill Date],
Status, [formulary status], S4CNT3, DQRIC4 )
SELECT [MTM Log of eligible patients 1-1-06 - -].MEM_ID,
CLMPRDFIL_RCTCDP.DQP2NB, CLMPRDFIL_RCTCDP.DQOINB, CLMPRDFIL_RCTCDP.DQUJCD,
CLMPRDFIL_RCTCDP.DQTCCD, CLMPRDFIL_RCPIDP.S4COT3, CLMPRDFIL_RCTCDP.DQIONB,
CLMPRDFIL_RCTCDP.DQIMNB, Mid([DQBBDA],5,2) & "/" & Mid([DQBBDA],7,2) & "/" &
Mid([DQBBDA],1,4) AS Filled, [DQS5CD] & [DQP2NB] & [DQOINB] AS [Index],
([E0ERPR]+[E0ESPR]) AS [Clm Cost], CLMPRDFIL_RCTCDP.DQBBDA AS [Fill Date],
CLMPRDFIL_RCTCDP.DQS6ST AS Status, CLMPRDFIL_RCTCDP.DQLNST AS [formulary
status], CLMPRDFIL_RCPIDP.S4CNT3, CLMPRDFIL_RCTCDP.DQRIC4
FROM (([MTM Log of eligible patients 1-1-06 - -] LEFT JOIN (CLMPRDFIL_RCTCDP
LEFT JOIN CLMPRDFIL_RCRXSP ON (CLMPRDFIL_RCTCDP.DQOINB =
CLMPRDFIL_RCRXSP.DNPSNB) AND (CLMPRDFIL_RCTCDP.DQP2NB =
CLMPRDFIL_RCRXSP.DNP2NB)) ON [MTM Log of eligible patients 1-1-06 - -].MEM_ID
= CLMPRDFIL_RCTCDP.DQS5CD) LEFT JOIN CLMPRDFIL_RCPIDP ON
(CLMPRDFIL_RCTCDP.DQRIC4 = CLMPRDFIL_RCPIDP.S4E6C4) AND
(CLMPRDFIL_RCTCDP.DQHTCJ = CLMPRDFIL_RCPIDP.S4NQS3)) LEFT JOIN
CLMPRDFIL_RCPDTP ON (CLMPRDFIL_RCTCDP.DQP2NB = CLMPRDFIL_RCPDTP.E0P2NB) AND
(CLMPRDFIL_RCTCDP.DQOINB = CLMPRDFIL_RCPDTP.E0OINB)
WHERE (((CLMPRDFIL_RCTCDP.DQBBDA)>=20060101) AND
((CLMPRDFIL_RCTCDP.DQS6ST)="P"));

 

[OfficeDev18 via AccessMonster.com]

Bev,

In my experience, ODBC provides a s-l-o-w connection, and that's with no
linked fields. With linked fields I find it impossible to both run the
program and get some work done. I use Microsoft Query in conjunction with
Excel to do that work. It's much faster. If you really need the fields
indexed, you can sort the various fields right in this query.

Hope this helps,

Sam

I am attempting to run a query and it runs forever and never appends records.

The CLMPRDFIL files are linked through an ODBC driver to a AS400 database.

The RCTCDP and RCTCDP files are very large.

I need help in minimizing the time it takes to run. I have ran it in the
past with better success.

I have all criteria and linked fields indexed.

SQL follows.

INSERT INTO [T002a MTM - Member claims] ( MEM_ID, DQP2NB, DQOINB, DQUJCD,
DQTCCD, S4COT3, DQIONB, DQIMNB, Filled, [Index], [Clm Cost], [Fill Date],
Status, [formulary status], S4CNT3, DQRIC4 )
SELECT [MTM Log of eligible patients 1-1-06 - -].MEM_ID,
CLMPRDFIL_RCTCDP.DQP2NB, CLMPRDFIL_RCTCDP.DQOINB, CLMPRDFIL_RCTCDP.DQUJCD,
CLMPRDFIL_RCTCDP.DQTCCD, CLMPRDFIL_RCPIDP.S4COT3, CLMPRDFIL_RCTCDP.DQIONB,
CLMPRDFIL_RCTCDP.DQIMNB, Mid([DQBBDA],5,2) & "/" & Mid([DQBBDA],7,2) & "/" &
Mid([DQBBDA],1,4) AS Filled, [DQS5CD] & [DQP2NB] & [DQOINB] AS [Index],
([E0ERPR]+[E0ESPR]) AS [Clm Cost], CLMPRDFIL_RCTCDP.DQBBDA AS [Fill Date],
CLMPRDFIL_RCTCDP.DQS6ST AS Status, CLMPRDFIL_RCTCDP.DQLNST AS [formulary
status], CLMPRDFIL_RCPIDP.S4CNT3, CLMPRDFIL_RCTCDP.DQRIC4
FROM (([MTM Log of eligible patients 1-1-06 - -] LEFT JOIN (CLMPRDFIL_RCTCDP
LEFT JOIN CLMPRDFIL_RCRXSP ON (CLMPRDFIL_RCTCDP.DQOINB =
CLMPRDFIL_RCRXSP.DNPSNB) AND (CLMPRDFIL_RCTCDP.DQP2NB =
CLMPRDFIL_RCRXSP.DNP2NB)) ON [MTM Log of eligible patients 1-1-06 - -].MEM_ID
= CLMPRDFIL_RCTCDP.DQS5CD) LEFT JOIN CLMPRDFIL_RCPIDP ON
(CLMPRDFIL_RCTCDP.DQRIC4 = CLMPRDFIL_RCPIDP.S4E6C4) AND
(CLMPRDFIL_RCTCDP.DQHTCJ = CLMPRDFIL_RCPIDP.S4NQS3)) LEFT JOIN
CLMPRDFIL_RCPDTP ON (CLMPRDFIL_RCTCDP.DQP2NB = CLMPRDFIL_RCPDTP.E0P2NB) AND
(CLMPRDFIL_RCTCDP.DQOINB = CLMPRDFIL_RCPDTP.E0OINB)
WHERE (((CLMPRDFIL_RCTCDP.DQBBDA)>=20060101) AND
((CLMPRDFIL_RCTCDP.DQS6ST)="P"));