Slow SQL update could use some help.

Question

An ex-coworker of mine wrote the following UPDATE as part of a data import script and it takes nearly 15 minutes to complete on a table of 92k rows.

UPDATE table
SET name = (
    SELECT TOP 1 old_name FROM (
        SELECT 
            SUM(r) rev,
            number,
            name, 
            intermediate_number,
            intermediate_name,
            old_number,
            old_name
        FROM table
        GROUP BY 
            number,
            name, 
            intermediate_number,
            intermediate_name,
            old_number,
            old_name 
    ) t
    WHERE t.name = table.name
    ORDER BY rev DESC
);

I'm sure it can be re-written to be more efficient, but all my attempts have either failed, or not produced the same results.

Also, no indexes are defined on the table. Suggestions appreciated. I'm on Sybase iAnywhere 11 if that matters.

Answer 1

Put an index on old_Name and see how long it takes as is...

Second, In analyzing your query, it seems there may be a flaw in it. if you look at the reformatted version below,

UPDATE table SET 
    name = (SELECT TOP 1 old_name 
            FROM (SELECT SUM(r) rev, number, name,
                    intermediate_number, intermediate_name,
                    old_number, old_name
                  FROM table
                  GROUP BY number, name, intermediate_number, 
                       intermediate_name, old_number, old_name) t 
            WHERE t.old_name = table.old_name  -- HERE
            ORDER BY rev DESC);

The second to last line *WHERE t.old_name = table.old_name* will cause the inner subquery to only have rows with t.oldname = to the value of the outer query table.old_name. So, it doesn't matter whether you do a Top 1 or not, since all the rows will have the same value for old_name, you will always be setting the value to exactly what it already is, no ??

EDIT: (based on Where clause change) try this:

 UPDATE table SET 
    name =  (SELECT Top 1 old_name
             FROM table it
             Where it.name = table.old_name
             GROUP BY number, intermediate_number, 
                      intermediate_name, old_number, old_name
             Order By SUM(r) Desc);

Answer 2

I'm not a Sybase person, however I would do it in the folowing manner.

Psudo Sql

DECLARE @tempTable TABLE --In memory table if possible

INSERT INTO @tempTable
  SELECT
            SUM(r) rev,
            number,
            name,
            intermediate_number,
            intermediate_name,
            old_number,
            old_name        
  FROM table        
  GROUP BY
            number,
            name,
            intermediate_number,
            intermediate_name,
            old_number,
            old_namet
  ORDER BY rev DESC


UPDATE table
  SET name = SELECT TOP 1 old_name FROM @tempTable t WHERE t.old_name = table.old_name

That should eliminate your recursive SELECT