Performance improvement query sql server

Question

Performance improvement query sql server

riki90

SSC Journeyman

Points: 78
More actions
September 18, 2018 at 1:25 am

#372802

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"

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

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

ryan.mcatee SSC Eights! Points: 860 More actions · Answer 1

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: 18343 More actions · Answer 2

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 More actions · Answer 3

tamil1996raji

Newbie

Points: 1

March 21, 2019 at 7:15 am

#2025098

Thanks for sharing the useful reply.