Improve Query Speed

[Groundrush]

Can I get this to run Quicker?

Iv'e been using an append qry to update new entries taken from our odbc server to a table in Access

It seems to work fine early on, but later on in the year when the records from the odbc get bigger, it takes longer to run----can take 5 min+ to run

I did try changing it to a Passthrough qry but it does not work if it's based on an append qry

Is there a better way?

INSERT INTO tblConceptOrders ( TA_DATE, TA_TASK_ID, BG_SITE, BG_ADDRESS, TA_SHORT_DESC, TA_STATUS, TA_LONG_DESC, TA_LOC, BDET_KEY_PERS, BDET_PHONE )
SELECT dbo_F_TASKS.TA_DATE, dbo_F_TASKS.TA_TASK_ID, dbo_FLOCATE.BG_SITE, dbo_FLOCATE.BG_ADDRESS, dbo_F_TASKS.TA_SHORT_DESC, dbo_F_TASKS.TA_STATUS, dbo_F_TASKS.TA_LONG_DESC, dbo_F_TASKS.TA_LOC, dbo_F_BD_DETAILS.BDET_KEY_PERS, dbo_F_BD_DETAILS.BDET_PHONE
FROM dbo_FLOCATE INNER JOIN ((dbo_F_TASKS INNER JOIN dbo_F_CONTRACT ON dbo_F_TASKS.TA_FKEY_CTR_SEQ = dbo_F_CONTRACT.CTR_SEQ) INNER JOIN dbo_F_BD_DETAILS ON dbo_F_TASKS.TA_SEQ = dbo_F_BD_DETAILS.BDET_FKEY_TA_SEQ) ON dbo_FLOCATE.BG_SEQ = dbo_F_TASKS.TA_FKEY_BG_SEQ
GROUP BY dbo_F_TASKS.TA_DATE, dbo_F_TASKS.TA_TASK_ID, dbo_FLOCATE.BG_SITE, dbo_FLOCATE.BG_ADDRESS, dbo_F_TASKS.TA_SHORT_DESC, dbo_F_TASKS.TA_STATUS, dbo_F_TASKS.TA_LONG_DESC, dbo_F_TASKS.TA_LOC, dbo_F_BD_DETAILS.BDET_KEY_PERS, dbo_F_BD_DETAILS.BDET_PHONE
HAVING (((dbo_F_TASKS.TA_TASK_ID) Like "0293*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0294*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0295*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0296*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0297*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0298*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0299*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0300*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0301*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0302*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0303*" Or (dbo_F_TASKS.TA_TASK_ID) Like "0305*"))
ORDER BY dbo_F_TASKS.TA_DATE;


thanks

 

[Mile-O]

That's quite a bit...the only thing I can suggest is changing the HAVING section. Using the Like with wildcards is going to waste a bit of time while it evaluates all possiblities.

It might be better to do use the IN clause with the Left function performed on this field:

i.e.

Left(dbo_F_TASKS.TA_TASK_ID, 4) In ("0293", "0295", "0296", etc)



You'd need to do the Left() also in the SELECT clause.

It might even be better to build a query that does all the criteria for you, thereby eliminating all the unnecessary data. You can then SELECT from that query within the APPEND query above.

 

[Groundrush]

Left(dbo_F_TASKS.TA_TASK_ID, 4) In ("0293", "0295", "0296", etc)

would ("0293") return only "0293" or all records starting with 0293 etc?

 

[Mile-O]

would ("0293") return only "0293" or all records starting with 0293 etc?

It all depends on the SQL; look at the example below. This would return all records starting with 0292 and 0293 - the query would balk, however, if the length of the field was less than 4; that can be remedied too.

SELECT FieldA
FROM tblExample
WHERE Left(FieldA, 4) In ("0292", "0293");

 

[Groundrush]

It all depends on the SQL; look at the example below. This would return all records starting with 0292 and 0293 - the query would balk, however, if the length of the field was less than 4; that can be remedied too.

SELECT FieldA
FROM tblExample
WHERE Left(FieldA, 4) In ("0292", "0293");

I am working with Job no's that start with 0293,0294,0295,0296 etc

so 0293 would be 0293:101, 0293:102. 0293:103 etc

I expect later on in the year some of these job no's to go into the thousands

will your example still work?

 

[Mile-O]

As long as you do it properly, yes.

 

[KyleB]

Don't forget to look ahead though. If you have the chance of exceeding 9999 job numbers within the life of the databse, then you might want to take that into account now with the names you are evaluating. Because as soon as you pass that 9999 point then your query will no longer match with a Left()