Performance improvement query sql server

  • riki90

    Valued Member

    Points: 58

    Hello then the insert and update queries below must insert the items not present in the **Articolo** table by importing them from **Importazione**, and must update the items in the **Articolo** with those present in the **Importazione**. To summarize in **Articolo** the articles must be updated and the new ones included. The fact is that the two queries used by c # are very slow. How can I speed them up?

    **Sql Execution Plan Insert :** https://www.brentozar.com/pastetheplan/?id=ryifseaOX

    **Sql Execution Plan Update :**: https://www.brentozar.com/pastetheplan/?id=HyJpilaOQ

    **C# Code:**

      String QueryInserimentoNuoviArticoli = "Insert into Articolo(CodArt,Descrizione,CodMarca,CodEAN,Prezzo,PrezzoListino,UM,Fornitore,Importato) SELECT ArticoloMetel as CodArt,DescrizioneArticolo as Descrizione,MarcaMetel as CodMarca,CodiceBarreMetel as CodEAN,PrezzoNetto,PrezzoListino,UM,MarcaMetel as Fornitore,'ELETTROVENETA' as Importato FROM Importazione where ArticoloMetel not in ( select CodArt from Articolo where Importato = 'ELETTROVENETA' ) and MarcaMetel not in ( select CodMarca from Articolo where Importato = 'ELETTROVENETA' ) ";
      SqlCommand command2 = new SqlCommand(QueryInserimentoNuoviArticoli, conn)
      {
      CommandTimeout = 0
      };
      command2.ExecuteNonQuery();
      command2.Parameters.Clear();
      String QueryAggiornamentoArticoliPresenti = " Update Articolo set Stato = 'Disponibile', Descrizione = i.Descrizione, CodEAN = i.CodEAN, Prezzo = i.PrezzoNetto, PrezzoListino = i.PrezzoListino, UM = i.UM, DataAggiornamento = getdate() from( SELECT ArticoloMetel as CodArt, DescrizioneArticolo as Descrizione, MarcaMetel as CodMarca, CodiceBarreMetel as CodEAN, PrezzoNetto, PrezzoListino, UM, MarcaMetel as Fornitore, 'ELETTROVENETA' as Importato FROM Importazione where ArticoloMetel in (select CodArt from Articolo where Importato = 'ELETTROVENETA') and MarcaMetel in (select CodMarca from Articolo where Importato = 'ELETTROVENETA'))i where Articolo.CodArt = i.CodArt and i.CodMarca = Articolo.CodMarca and Articolo.Importato = 'ELETTROVENETA' and Articolo.Fornitore = i.Fornitore";
      SqlCommand command3 = new SqlCommand(QueryAggiornamentoArticoliPresenti, conn){CommandTimeout = 0};

    # Extracted queries

    insert:

      INSERT INTO Articolo(CodArt, Descrizione, CodMarca, CodEAN, Prezzo, PrezzoListino, UM, Fornitore, Importato)
      SELECT ArticoloMetel AS CodArt,
        DescrizioneArticolo AS Descrizione,
        MarcaMetel AS CodMarca,
        CodiceBarreMetel AS CodEAN,
        PrezzoNetto,
        PrezzoListino,
        UM,
        MarcaMetel AS Fornitore,
        'ELETTROVENETA' AS Importato
      FROM Importazione
      WHERE ArticoloMetel NOT IN
       (SELECT CodArt
       FROM Articolo
       WHERE Importato = 'ELETTROVENETA' )
      AND MarcaMetel NOT IN
       (SELECT CodMarca
       FROM Articolo
       WHERE Importato = 'ELETTROVENETA' )

    update:

      UPDATE Articolo
      SET Stato = 'Disponibile',
       Descrizione = i.Descrizione,
       CodEAN = i.CodEAN,
       Prezzo = i.PrezzoNetto,
       PrezzoListino = i.PrezzoListino,
       UM = i.UM,
       DataAggiornamento = getdate()
      FROM
      (SELECT ArticoloMetel AS CodArt, DescrizioneArticolo AS Descrizione, MarcaMetel AS CodMarca, CodiceBarreMetel AS CodEAN, PrezzoNetto, PrezzoListino, UM, MarcaMetel AS Fornitore, 'ELETTROVENETA' AS Importato
       FROM Importazione
       WHERE ArticoloMetel IN
        (SELECT CodArt
        FROM Articolo
        WHERE Importato = 'ELETTROVENETA')
       AND MarcaMetel IN
        (SELECT CodMarca
        FROM Articolo
        WHERE Importato = 'ELETTROVENETA'))i
      WHERE Articolo.CodArt = i.CodArt
      AND i.CodMarca = Articolo.CodMarca
      AND Articolo.Importato = 'ELETTROVENETA'
      AND Articolo.Fornitore = i.Fornitore"

  • ryan.mcatee

    SSC Eights!

    Points: 857

    First, to most directly answer your question:  In nearly every case, poor database performance is the result of poorly-indexed tables.  So review your database indexes.  Also, I think you should look into using a MERGE INTO sql command.  Using MERGE INTO, you can either INSERT or UPDATE based on whether or not the records exist in the database.  Also, I have heard that it is a good practice to refer to all tables using two-part names (schema.tableName).

    Second, the more concerning issue is that you have c# application code with database code intermixed.  This violates the contract between application and database.  To put it simply, your application knows too much about the database.  Imagine the nightmare of trying to refactor your database (change tables, column names, etc) with database code mixed in your application.  A better idea would be to call a stored procedure, then, in your stored procedure, you can do your MERGE INTO.  See, then c# doesn't know or care how the database updates the data, it leaves database responsibilities to the database.

    I will show you a partially-developed merge, but you will have to do the work to complete it:
    MERGE INTO Articolo AS target
    USING
    (
      SELECT ArticoloMetel AS CodArt
       , DescrizioneArticolo AS Descrizione
       , MarcaMetel AS CodMarca
       , CodiceBarreMetel AS CodEAN
       , PrezzoNetto
       , PrezzoListino
       , UM
       , MarcaMetel AS Fornitore
      FROM Importazione
      WHERE Importato='ELETTROVENETA'
    ) source ON target.Importato=source.Importato
    WHEN MATCHED THEN
        UPDATE SET Stato = 'Disponibile'
         , Descrizione = source.Descrizione
         , CodEAN = source.CodEAN
         , Prezzo = source.PrezzoNetto
         , PrezzoListino = source.PrezzoListino
    WHEN NOT MATCHED THEN
        INSERT (CodArt, Descrizione, CodMarca, CodEAN, Prezzo, PrezzoListino, UM, Fornitore, Importato)
        VALUES (source.ArticoloMetel, source.DescrizioneArticolo, source.MarcaMetel, source.CodiceBarreMetel, source.PrezzoNetto, source.PrezzoListino...)
    --WHEN NOT MATCHED BY SOURCE THEN
    --    DELETE ;

  • Jonathan AC Roberts

    SSCoach

    Points: 16793

    ryan.mcatee - Tuesday, November 27, 2018 8:45 AM

    First, to most directly answer your question:  In nearly every case, poor database performance is the result of poorly-indexed tables.  So review your database indexes.  Also, I think you should look into using a MERGE INTO sql command.  Using MERGE INTO, you can either INSERT or UPDATE based on whether or not the records exist in the database.  Also, I have heard that it is a good practice to refer to all tables using two-part names (schema.tableName).

    Second, the more concerning issue is that you have c# application code with database code intermixed.  This violates the contract between application and database.  To put it simply, your application knows too much about the database.  Imagine the nightmare of trying to refactor your database (change tables, column names, etc) with database code mixed in your application.  A better idea would be to call a stored procedure, then, in your stored procedure, you can do your MERGE INTO.  See, then c# doesn't know or care how the database updates the data, it leaves database responsibilities to the database.

    I will show you a partially-developed merge, but you will have to do the work to complete it:
    MERGE INTO Articolo AS target
    USING
    (
      SELECT ArticoloMetel AS CodArt
       , DescrizioneArticolo AS Descrizione
       , MarcaMetel AS CodMarca
       , CodiceBarreMetel AS CodEAN
       , PrezzoNetto
       , PrezzoListino
       , UM
       , MarcaMetel AS Fornitore
      FROM Importazione
      WHERE Importato='ELETTROVENETA'
    ) source ON target.Importato=source.Importato
    WHEN MATCHED THEN
        UPDATE SET Stato = 'Disponibile'
         , Descrizione = source.Descrizione
         , CodEAN = source.CodEAN
         , Prezzo = source.PrezzoNetto
         , PrezzoListino = source.PrezzoListino
    WHEN NOT MATCHED THEN
        INSERT (CodArt, Descrizione, CodMarca, CodEAN, Prezzo, PrezzoListino, UM, Fornitore, Importato)
        VALUES (source.ArticoloMetel, source.DescrizioneArticolo, source.MarcaMetel, source.CodiceBarreMetel, source.PrezzoNetto, source.PrezzoListino...)
    --WHEN NOT MATCHED BY SOURCE THEN
    --    DELETE ;

    An update followed by an insert will normally perform much better than a merge.

  • tamil1996raji

    Newbie

    Points: 1

    Thanks for sharing the useful reply.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply