SQL脚本重写，旧脚本耗时太长 [英] SQL Script re-write, old script taking too long

问题描述

大家好！



大家好日子。我只是快速询问为什么下面的查询花了太长时间才能完成。实际上，有时候它运行得很顺利，而且它从来都没有完成。



它让我想到问题可能是查询本身，但数据库微调也可以帮助。每当它没有完成时，我只需先通过执行内部查询手动运行查询（获取重复项并排除MAX obj ID），因此只删除带有MIN OBJ ID的重复项。



我正在考虑分成多个脚本，但总是欢迎提出建议。



以下是查询：

  DELETE   FROM  U_TABLE 
  WHERE  OBJ_ID  IN （
  SELECT  OBJ_ID 
  FROM  U_TABLE 
  WHERE （S_FIELD，P_FIELD） IN （
  SELECT  S_FIELD，P_FIELD 
  FROM  U_TABLE 
  GROUP   BY  S_FIELD，P_FIELD 
  HAVING  COUNT（*）>  1 ）
）
  AND  OBJ_ID  NOT   IN （
  SELECT  MAX（OBJ_ID）
  FROM  U_TABLE 
  GROUP   BY  S_FIELD，P_FIELD 
  HAVING  COUNT（*）>  1  
）

我想感谢所有提前帮助的人。

解决方案

所以你要删除除最年轻之外的所有副本吗？

我会这样尝试：

  CREATE   TABLE  U_TABLE（OBJ_ID  INT   IDENTITY （ 1 ， 1 ），S_FIELD  INT ，P_FIELD  INT ）
 
  INSERT   INTO  U_TABLE（ S_FIELD，P_FIELD） VALUES （ 1 ， 1 ）
  INSERT   INTO  U_TABLE（S _FIELD，P_FIELD） VALUES （ 1 ， 1 ）
  INSERT   INTO  U_TABLE（S_FIELD，P_FIELD） VALUES （ 1 ， 1 ）
  INSERT   INTO  U_TABLE（S_FIELD，P_FIELD） VALUES （ 1 ， 2 ）
  INSERT   INTO  U_TABLE（S_FIELD，P_FIELD） VALUES （ 1 ，  2 ）
  INSERT   INTO  U_TABLE（S_FIELD，P_FIELD） VALUES （ 1 ， 2 ）
 
  SELECT  *  FROM  U_TABLE 
 
  WITH  cte  AS （
  SELECT  ROW_NUMBER（） OVER （ PARTITION   BY  S_FIELD，P_FIELD 
  ORDER   BY  OBJ_ID  DESC ）RN 
  FROM  U_TABLE）
  DELETE   FROM  cte  WHERE  RN> 1 

这适用于SQL-Server 2008（你没有提到你的版本）所以如果你喜欢它，你需要检查是否常见表格表达式适用于你

FranzBe的解决方案很好，但Oracle的语法错误。

尝试：

  DELETE   FROM  U_TABLE t1 
  WHERE   EXISTS （
  SELECT   1  
  FROM （
  SELECT  OBJ_ID 
，ROW_NUMBER（） OVER  （ PARTITION   BY  S_FIELD，P_FIELD  ORDER  < span class =code-keyword> BY  OBJ_ID  DESC ）RN 
  FROM  U_TABLE 
）t2 
  WHERE  t2.RN>  1  
  AND  t1.OBJ_ID = t2.OBJ_ID 
）
 ; 

要获得高性能，您需要两个索引。

一个打开（S_FIELD，P_FIELD，OBJ_ID），另一个打开（OBJ_ID）

Hi Everyone!

Good day to all of you. I just have a quick inquiry as to why below query is taking too long to complete. Actually, there are times it runs smoothly and times it never finishes at all.

It had me thinking maybe the problem is the query itself but database fine tuning could also help. Whenever it doesn't complete, I just manually run the query by performing the inner query first (getting the duplicates and excluding the MAX obj IDs) therefore deleting only duplicates with the MIN OBJ ID.

I am thinking of splitting into multiple scripts but suggestions are always welcome.

Here is the query:

DELETE FROM U_TABLE
WHERE   OBJ_ID IN (
    SELECT  OBJ_ID
    FROM    U_TABLE
    WHERE   (S_FIELD, P_FIELD) IN (
        SELECT  S_FIELD, P_FIELD
        FROM    U_TABLE
        GROUP BY S_FIELD, P_FIELD
        HAVING COUNT (*) > 1)
        )
        AND OBJ_ID NOT IN (
            SELECT MAX (OBJ_ID)
            FROM    U_TABLE
            GROUP BY S_FIELD, P_FIELD
            HAVING COUNT (*) > 1
            )

I'd like to thank you all who will help, in advance.

解决方案

so you want to delete all the duplicates except the "youngest" one?
I would try it this way:

CREATE TABLE U_TABLE (OBJ_ID INT IDENTITY(1,1), S_FIELD INT, P_FIELD INT )

INSERT INTO U_TABLE (S_FIELD, P_FIELD) VALUES (1, 1)
INSERT INTO U_TABLE (S_FIELD, P_FIELD) VALUES (1, 1)
INSERT INTO U_TABLE (S_FIELD, P_FIELD) VALUES (1, 1)
INSERT INTO U_TABLE (S_FIELD, P_FIELD) VALUES (1, 2)
INSERT INTO U_TABLE (S_FIELD, P_FIELD) VALUES (1, 2)
INSERT INTO U_TABLE (S_FIELD, P_FIELD) VALUES (1, 2)

SELECT * FROM U_TABLE

WITH cte AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY S_FIELD, P_FIELD
                            ORDER BY  OBJ_ID DESC ) RN
  FROM   U_TABLE)
DELETE FROM cte WHERE RN>1

this works with SQL-Server 2008 (you didn't mention your version) so if you like it, you need to check whether common table expressions will work for you

The solution from FranzBe is a good one, but the syntax is wrong for Oracle.
Try:

DELETE FROM U_TABLE t1
WHERE EXISTS (
    SELECT  1
    FROM    (
        SELECT  OBJ_ID
               ,ROW_NUMBER() OVER (PARTITION BY S_FIELD, P_FIELD ORDER BY  OBJ_ID DESC ) RN
        FROM    U_TABLE
        ) t2
    WHERE   t2.RN > 1
        AND t1.OBJ_ID = t2.OBJ_ID
    )
;

To get high performance you need two indices.
One on (S_FIELD, P_FIELD, OBJ_ID) and the other one on (OBJ_ID)

这篇关于SQL脚本重写，旧脚本耗时太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！