Pourquoi la requête SQL DELETE ne fonctionne pas lors de l'utilisation avec LIKE Keyword?

Cela devrait fonctionner correctement, je ne vois rien de mal avec la requête, essayez d'écrire une nouvelle requête dans la nouvelle fenêtre de requête pour vous assurer que la requête est propre

Pour moi, cela fonctionne bien et supprime les données de test de la table p>

DELETE FROM Discipline WHERE Code LIKE '%DHA-DIS%'

(2 row(s) affected)


select * from Discipline where Code like '%DHA-DIS%'

select * from Discipline
select * from Discipline where Code like '%DHA-DIS%'

a>

Vous pouvez voir que la requête ci-dessus fonctionne très bien, elle supprime les 2 lignes qui correspondent à la requête.

1) Vous avez 5000 lignes à supprimer mais ce n'est pas forcément la table entière. Combien de lignes dans le tableau?

2) Votre instruction LIKE nécessitera une analyse de table pour être satisfaite. S'il y a beaucoup de lignes ou de grandes colonnes, cela prendra beaucoup de temps.

3) Publiez le plan de requête et les statistiques sur les tables qu'il utilise.

4) Si vous annulez une suppression , la base de données sera annulée. C'est pourquoi vous le voyez ne rien faire.

Il est possible qu'il y ait un verrou sur la table Discipline par un autre processus
Vous pouvez trouver les verrous sur votre table en exécutant ce script

declare @lock table (spid int, dbid int, objId int, indId int, Type char(4), resource nchar(32), Mode char(8), status char(6))
declare @who table (spid int, ecid int, status char(30), loginname char(128), hostname char(128), blk char(5), dbname char(128), cmd char(16), request_id INT)
declare @LockSummary table (loginname varchar(28), DB varchar(128), object varchar(30), ToLevel varchar(20), How_Many int, Xclusive_lock_for_command char(16), spid int, hostname char(128))

insert into @lock exec sp_lock
insert into @who exec sp_who

insert into @LockSummary
select loginname, 
       db_name(dbid) as DB,
       object_name(objID) as object,
       max(mode) as [ToLevel],
       Count(*) as [How Many],
       Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
       l.spid, 
       hostname
from   @lock l 
  join @who w on l.spid = w.spid
where  dbID != db_id('tempdb') 
and    l.status = 'GRANT'
group by dbID, objID, l.spid, hostname, loginname

select * 
from   @LockSummary 
where  object like '%Discipline%'
order by [ToLevel] Desc, [How_Many] Desc, loginname, DB, object

Avez-vous essayé la même requête en mode édition. Vous pouvez le faire

1. Cliquez avec le bouton droit sur le tableau Pats.Discipline.
2. Choisissez de modifier les 200 premières ... lignes
3. Appuyez sur Ctrl + 3 pour modifier la requête.
4. Remplacez la requête par select * from Discipline where Code like '% DHA-DIS%'
5. Cliquez sur la première cellule du haut comme dans l'image ci-dessous pour sélectionner toutes les lignes
6. Appuyez sur le bouton de suppression

Si l'exécution prend trop de temps, essayez de changer votre requête en quelque chose comme ça

sélectionnez le top 1000 * de la discipline où le code comme '% DHA-DIS%'

répétez l'étape si cela réussit

Deux choses pourraient ralentir votre suppression

1. Vous avez une suppression en cascade sur les clés étrangères référençant le PK dans votre table Pats.Discipline. Vous pouvez vérifier si vous avez des clés pour lesquelles la suppression en cascade est activée en exécutant la requête ci-dessous.

    select * from sys.foreign_keys where delete_referential_action > 0

2. La clé primaire de votre table Pats.Discipline est utilisée comme clé étrangère dans d'autres tables avec un très grand nombre de lignes. La suppression des enregistrements avec ces champs dans la table Discipline déclenchera un contrôle d'intégrité référentielle sur toutes les tables où cette clé est référencée et en fonction de certains facteurs, cela pourrait être un processus long. Si vous savez que l'intégrité référentielle n'est pas violée par cette suppression, vous pouvez supprimer les clés étrangères, puis exécuter votre commande de suppression, puis recréer les clés étrangères.

Remarque: Reprenez UP

Si possible Modifiez la discipline PATS en ceci,

DECLARE @TopSize INT = 10000
DECLARE @BatchSize INT = 10000
DECLARE @MaxLimit INT = 1
DECLARE @RowCount INT = 0

BEGIN TRY
    WHILE (@TopSize <= @MaxLimit)
    BEGIN


    delete TOP (@TopSize) from PATS.Discipline
    where exists(select 1 from #temp where #temp.id=PATS.Discipline.id )


        SET @RowCount = @@RowCount

        --PRINT @TopSize
        IF (
                @RowCount = 0
                OR @RowCount IS NULL
                )
            BREAK;
        ELSE
            SET @TopSize = @TopSize + @BatchSize
    END
END TRY

BEGIN CATCH
    --catch error
END CATCH

Ou modifiez la colonne possible. p >

CreatedBY peut être converti INT en joignant d'abord manuellement avec la table de préoccupation. Puis corriger le code.

Je ne parle pas de ce point, Code LIKE '% DHA-DIS%' est NON SARGable . Ou POURQUOI vous ne devriez pas utiliser. et si la colonne de code doit être NON Clustered Index ou non.

Modifier la conception de la table vous aidera dans tous les aspects que ce soit la requête Select ou Opération DML et pour toujours.

Vous pouvez également le faire

Placez le Select Resultset dans la table Temp, puis Rejoignez et supprimez

create table #temp (id uniqueidentifier not null primary key)

insert into #temp 
select id from PATS.Discipline where Code like '%DHA-DIS%'

delete from PATS.Discipline
where exists(select 1 from #temp where #temp.id=PATS.Discipline.id )

Vous pouvez effectuer les deux étapes

Si Supprimer les données est en millions, vous pouvez utiliser la pagination,

ID int PK
Code varchar(20)
Name varchar(50)
CreatedBY varchar(50) or even better INT
CreatedDate Datetime2(0)
UpdatedBY varchar(50) or even better INT
UpdatedDate Datetime2(0)

p >

Cette réponse est basée sur mon expérience personnelle.

Vous stockez uniqueidentifier en tant qu'ID de votre table et en faites une clé de cluster primaire. La suppression d'un tel nombre de lignes prend du temps en raison de la réindexation de votre table. Alors, je suggérerais -

1. Supprimer votre index de clé primaire
2. Exécutez votre déclaration de suppression
3. Rajoutez votre index de clé primaire

Cela m'a aidé dans un cas similaire. Pourquoi? Parce que dans cette stratégie, l'indexation de cette table n'est manipulée que deux fois. Lors de la suppression, la réindexation de la table ne se produit pas. Pour cette raison, vous constaterez une amélioration significative dans l'exécution de la suppression.

Donc, votre requête devrait être comme ceci-

ALTER TABLE PATS.Discipline DROP CONSTRAINT [PK_Discipline]
GO
DELETE FROM PATS.Discipline WHERE Code LIKE '%DHA-DIS%'
GO
ALTER TABLE PATS.Discipline DROP CONSTRAINT [PK_Discipline] PRIMARY KEY CLUSTERED
GO

Remarque: Cette stratégie de suppression ne doit être appliquée qu'à ce type de cas où la table a de nombreux index ou la clé primaire est uniqueidentifier et que vous essayez de supprimer plus de lignes de cette table. Mais dans un scénario normal, la même ancienne requête de suppression devrait être appliquée.

Ce n'est probablement pas un problème avec le mot clé LIKE. Le SELECT semble renvoyer beaucoup de lignes correspondantes - je présume relativement rapidement (?) - donc la question est "Qu'est-ce qui est lent (ou qu'est-ce qui empêche) DELETE?" Essayez

DELETE TOP 1 FROM PATS.Discipline WHERE Code LIKE '%DHA-DIS%'

et si cela fonctionne, essayez TOP 10/100 ...

Une supposition éclairée dit que c'est lent à cause de la réindexation et / ou des relations croisées (contraintes de clé étrangère) et / ou simplement la construction d'une transaction très importante.

Comment l'accélérer? Il y a beaucoup de bonnes suggestions parmi les réponses déjà publiées et je suggère également de jeter un œil à ceci: Comment supprimer efficacement des lignes sans utiliser Truncate Table dans un tableau de plus de 500 000 lignes

Autre tout Je ne vois aucun problème avec le code que vous utilisiez à l'origine, bien que l'utilisation d'une instruction similaire sur une requête de suppression ait un impact sur les performances. Si je sais que je ne supprime que quelques milliers de lignes, j'adopte l'approche CTE et je la rejoins sur la table source:

;With DeleteCTE as 
(

select 
    [ID],
    Code
from Discipline 
    where Code like '%DHA-DIS%'
)
Delete
from Discipline D 
Inner Join
    DeleteCTE DC 
        on DC.ID = D.ID

Je pense que vous êtes plus préoccupé par le temps d'exécution ici car cela prend beaucoup de temps, vous avez donc annulé l'exécution. J'ai rencontré le même problème dans le passé, ma solution est de redémarrer SQL Management Studio et d'essayer la même requête, cela fonctionnera bien.

Il n'y a aucun problème avec la requête que vous avez utilisée.

Il peut y avoir plusieurs raisons. Le câble réseau a peut-être été déconnecté, la base de données était peut-être hors ligne au milieu de l'exécution, il y a peut-être eu une panne de courant, ou l'accès à la table a été verrouillé à cause d'une autre transaction, etc.

Lorsque votre requête échouait, cela prenait parfois même plus d'une minute, n'est-ce pas? Mais quand cela a réussi, l'exécution a été très rapide. Il doit y avoir eu un autre accès à cette table via le réseau au moment où la requête a échoué.

Lorsque vous traitez avec un grand nombre de données comme 5000, vous pouvez avoir des procédures stockées avec des blocs de transactions ajoutés.