Speed up a VERY slow append query

[Grunners]

All,

I'm not sure how well I've managed to search on this as I'm not too sure where to start!

I have an append query as follows:

INSERT INTO tbl_Employee ( Company_No )
SELECT tbl_Co_Data.Company_No
FROM tbl_Co_Data
WHERE (((tbl_Co_Data.Company_No) Not In (select Company_No from tbl_Employee)))
ORDER BY tbl_Co_Data.Company_No;

Basically this query is run a number of times a day and appends new company numbers in to a table - 'tbl_Employee'. It's badly named - it's not got much to do with employees. Any way it takes a good 3 minutes to run with about 20k records in tbl_Co_Data and probably 18k records in tbl_Employee.

It looks to me like it's looping through each record in one table for each record in the other - which is plain daft.

I'm currently experimenting with a DTS package that puts tbl_Co_Data in to SQL server first before the query would run (tbl_Employee is already there) with a view to running a SP and ditching the query.

Does anybody have any other ideas as I'm having problems with the DTS in that it appears to be pretty slow in itself!

Many thanks in advance for any response.

 

[Grunners]

Dimwit

Apparently it takes 6 minutes.

Obviously this generated extra pressure to fix the problem and I've managed it using a simple unmatched query and then a quick append. None of the IN(select rubbish.

I may need to think about a change of career! OR A HOLIDAY...

 

[Fifty2One]

The selection of the unique Company_No by the Where statement and the sort statement is probably adding 5 minutes and 15 seconds to the 6 minute query.

If tbl_Employee only collects new Company_No then if Company_No was indexed with no duplicates permitted you could get rid of the WHERE (((tbl_Co_Data.Company_No) Not In (select Company_No from tbl_Employee))) and the ORDER BY tbl_Co_Data.Company_No.

 

[MSAccessRookie]

All,

I'm not sure how well I've managed to search on this as I'm not too sure where to start!

I have an append query as follows:

INSERT INTO tbl_Employee ( Company_No )
SELECT tbl_Co_Data.Company_No
FROM tbl_Co_Data
WHERE (((tbl_Co_Data.Company_No) Not In (select Company_No from tbl_Employee)))
ORDER BY tbl_Co_Data.Company_No;

Basically this query is run a number of times a day and appends new company numbers in to a table - 'tbl_Employee'. It's badly named - it's not got much to do with employees. Any way it takes a good 3 minutes to run with about 20k records in tbl_Co_Data and probably 18k records in tbl_Employee.

It looks to me like it's looping through each record in one table for each record in the other - which is plain daft.

I'm currently experimenting with a DTS package that puts tbl_Co_Data in to SQL server first before the query would run (tbl_Employee is already there) with a view to running a SP and ditching the query.

Does anybody have any other ideas as I'm having problems with the DTS in that it appears to be pretty slow in itself!

Many thanks in advance for any response.

Have you considered combining the two queries into a single Query with a Left Join? One of the reasons that this is slow could be that there are two levels of Queries. If you Left Join the two Queries, then you should reduce the number of records processed, and therefore the amount of time. Something along this line:

[FONT=Times New Roman][SIZE=3]INSERT INTO tbl_Employee ( Company_No )[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] SELECT tbl_Co_Data.Company_No[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] FROM tbl_Co_Data [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]     LEFT JOIN tbl_Employee[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]     ON tbl_Co_Data.Company_No = tbl_Employee.Company_No[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3] WHERE tbl_Employee.Company_No Is Null[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]ORDER BY tbl_Co_Data.Company_No;[/SIZE][/FONT]

 

[antonsachs]

Try saving your SQL statement as actual query - queries are precompiled, so while you lose time in saving - depending on you machine and hard drive (UGH!) - you should actually gain speed.
On my top of the line machine, running db from RAMDrive, I get 25% to 35% speed increase by using actual query.

 

[isladogs]

Anton

Whilst I agree with your comment that queries are often faster than the equivalent SQL, the last post in this thread dates back to 2009. I imagine by now they've either sorted it out o2 a slight improvement in speed is no longer relevant!