2
\$\begingroup\$

The insert query 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

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();

Extracted queries

Insert Query:

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' )
\$\endgroup\$

closed as off-topic by Malachi Sep 18 '18 at 16:58

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Lacks concrete context: Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site." – Malachi
If this question can be reworded to fit the rules in the help center, please edit the question.

  • \$\begingroup\$ I'm no expert in SQL so I'll keep my thoughts as a comment. Perhaps joining on a temp table with the results of the subqueries in the WHERE clause would be more performant? The missing indexes in the execution plan you posted would probably help quite a bit too since it's doing a scan on the Articolo table instead of a seek. \$\endgroup\$ – Shelby115 Sep 18 '18 at 12:42
  • \$\begingroup\$ I'm afraid this question does not match what this site is about. Code Review is about improving existing, working code. The example code that you have posted is not reviewable in this form because it leaves us guessing at your intentions. Unlike Stack Overflow, Code Review needs to look at concrete code in a real context. Please see Why is hypothetical example code off-topic for CR? \$\endgroup\$ – Malachi Sep 18 '18 at 16:56
  • \$\begingroup\$ This does not feel like real code that you have in a production like atmosphere, we could review this, but you likely would not get the kind of review that you are looking for, please post the actual code. \$\endgroup\$ – Malachi Sep 18 '18 at 16:57
  • \$\begingroup\$ I am also not a SQL expert, and I can't be sure this is what you need, but you might look at the MERGE statement for this scenario. \$\endgroup\$ – benj2240 Sep 18 '18 at 22:07