Speed up update script

  • Thread starter Thread starter Sorting in a Graph
  • Start date Start date
S

Sorting in a Graph

What is the best way to improve performance on an update script? I have tried
indexing columns but the script is still too slow. How to structure script to
use an index to speed up update? Current script is:

UPDATE Conversion INNER JOIN ANLZ_t ON Conversion.Concate = ANLZ_t.concate
SET Conversion.Cost_Center = [anlz_t].[cost_ctr];
 
Sorting in a Graph said:
What is the best way to improve performance on an update script? I have
tried
indexing columns but the script is still too slow. How to structure script
to
use an index to speed up update? Current script is:

UPDATE Conversion INNER JOIN ANLZ_t ON Conversion.Concate = ANLZ_t.concate
SET Conversion.Cost_Center = [anlz_t].[cost_ctr];
Click to expand...


1. Make sure both tables have an index on the [Concate] field.

2. The update will probably be faster if Conversion.Cost_Center is of the
same data type as [anlz_t].

3. Make sure the table Conversion has a primary key. This may or may not be
important for the update speedl; I don't know.

So long as all those considerations are met, the update query should run
about as fast as it possibly can.
 
Where are your tables?
If they are in a linked network location, maybe your network is slow.
Try moving back end to local drive and compare speed.
Ensure compare columns are indexed and of same data type.
How big are your tables?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Is the Conversion.Cost_Center part of an index? If so that could slow things
down as the index needs to be updated as the data is. When dealing with tens
of thousands - or more - records, it's often faster to drop the index; run
the update; then rebuild the index.
 
Back
Top