June 26, 2018 at 9:43 am
UPDATE KilometriCantiereRapportoMobileSET IdRapportoMobile = @IdRapportoWHERE IdKilometri IN (SELECT Kilometri.IdKilometriFROM KilometriINNER JOIN KilometriCantiereRapportoMobile ON Kilometri.IdKilometri = KilometriCantiereRapportoMobile.IdKilometriWHERE KilometriCantiereRapportoMobile.IdRapportoMobile IS NULL AND
CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), Kilometri.Data, 105)AND Kilometri.IdCantiere = @IdCantiere AND
Kilometri.IdUtenteInserimento = @IdUtente); /*Articoli */UPDATE ArticoloCantiereRapportoMobileSET IdRapportoMobile = @IdRapportoWHERE IdArticoloCantiere IN (SELECT ArticoloCantiere.IdArticoloCantiereFROM ArticoloCantiereINNER JOIN
ArticoloCantiereRapportoMobile ON ArticoloCantiere.IdArticoloCantiere = ArticoloCantiereRapportoMobile.IdArticoloCantiereWHERE ArticoloCantiereRapportoMobile.IdRapportoMobile IS NULLAND CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), ArticoloCantiere.Data, 105)AND ArticoloCantiere.IdCantiere = @IdCantiereAND ArticoloCantiere.IdUtente = @IdUtente); /* risorse */UPDATE RisorsaRapportoMobileSET IdRapportoMobile = @IdRapportoWHERE IdRisorseUmane IN (SELECT RisorseUmane.IdRisorseUmaneFROM RisorsaRapportoMobileINNER JOIN RisorseUmane ON RisorseUmane.IdRisorseUmane = RisorsaRapportoMobile.IdRisorseUmaneWHERE RisorsaRapportoMobile.IdRapportoMobile IS NULLAND CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), RisorseUmane.Data, 105)AND RisorseUmane.IdCantiere = @IdCantiereAND RisorseUmane.IdUtenteInserimento = @IdUtente)
June 26, 2018 at 9:55 am
It's almost impossible to read this the way that it's formatted.
The only obvious thing that I see is that you have a function on one of the fields in your where clause, which makes it non-SARGable, that is, slow. Since you haven't provided a table definition or any sample data, we can't tell you how to remove the function.
If removing the formula doesn't help, we'll need the table definition, sample data, and the actual execution plan. The last should be attached as a .sqlplan file. (Pictures WILL NOT HELP.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 26, 2018 at 10:07 am
riccardo 66877 - Tuesday, June 26, 2018 9:43 AMHi then the query I'm running below works correctly but it is very slow to perform the operations as I can to speed it up? Unfortunately it is a very oneorose query and I have to run it on a server sql 2014 some advice?
UPDATE KilometriCantiereRapportoMobileSET IdRapportoMobile = @IdRapportoWHERE IdKilometri IN (SELECT Kilometri.IdKilometriFROM KilometriINNER JOIN KilometriCantiereRapportoMobile ON Kilometri.IdKilometri = KilometriCantiereRapportoMobile.IdKilometriWHERE KilometriCantiereRapportoMobile.IdRapportoMobile IS NULL AND
CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), Kilometri.Data, 105)AND Kilometri.IdCantiere = @IdCantiere AND
Kilometri.IdUtenteInserimento = @IdUtente); /*Articoli */UPDATE ArticoloCantiereRapportoMobileSET IdRapportoMobile = @IdRapportoWHERE IdArticoloCantiere IN (SELECT ArticoloCantiere.IdArticoloCantiereFROM ArticoloCantiereINNER JOIN
ArticoloCantiereRapportoMobile ON ArticoloCantiere.IdArticoloCantiere = ArticoloCantiereRapportoMobile.IdArticoloCantiereWHERE ArticoloCantiereRapportoMobile.IdRapportoMobile IS NULLAND CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), ArticoloCantiere.Data, 105)AND ArticoloCantiere.IdCantiere = @IdCantiereAND ArticoloCantiere.IdUtente = @IdUtente); /* risorse */UPDATE RisorsaRapportoMobileSET IdRapportoMobile = @IdRapportoWHERE IdRisorseUmane IN (SELECT RisorseUmane.IdRisorseUmaneFROM RisorsaRapportoMobileINNER JOIN RisorseUmane ON RisorseUmane.IdRisorseUmane = RisorsaRapportoMobile.IdRisorseUmaneWHERE RisorsaRapportoMobile.IdRapportoMobile IS NULLAND CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), RisorseUmane.Data, 105)AND RisorseUmane.IdCantiere = @IdCantiereAND RisorseUmane.IdUtenteInserimento = @IdUtente)
After cut & paste in SSMS, this query does not parse successfully, please reformat.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 27, 2018 at 4:44 am
I agree with everyone else, if you really want help, make it easier for us to read this.
I took the time to scroll through it a little bit. While there may be any number of problems, this immediately jumps out:
CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), ArticoloCantiere.Data, 105)
By putting functions on the variable/parameter (can't tell which it is), and on the column, you're destroying any ability of SQL Server to use indexes and statistics in an efficient way. To satisfy this query, it must scan the tables, no choice. That is a performance killer. Whatever the actual data type of ArticoloCantiere.Data, make the @data variable/parameter, the same. Don't do these conversions and functions on columns.
Also, for more complete help, post the execution plan (preferable the actual plan, but the estimated plan works too).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 27, 2018 at 7:32 am
I agree on what has been said, but there might be something else that can be done. You have 3 updates. Formatting the first, it would look like this:
UPDATE KilometriCantiereRapportoMobile SET
IdRapportoMobile = @IdRapporto
WHERE IdKilometri IN (
SELECT k.IdKilometri
FROM Kilometri k
INNER JOIN KilometriCantiereRapportoMobile kcr ON k.IdKilometri = kcr.IdKilometri
WHERE kcr.IdRapportoMobile IS NULL
AND CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), k.Data, 105)
AND k.IdCantiere = @IdCantiere
AND k.IdUtenteInserimento = @IdUtente);
It could be changed like this which might prevent one additional read on the table.:
UPDATE kcr SET
IdRapportoMobile = @IdRapporto
FROM KilometriCantiereRapportoMobile kcr
JOIN Kilometri k ON k.IdKilometri = kcr.IdKilometri
WHERE kcr.IdRapportoMobile IS NULL
AND CONVERT(varchar(10), k.Data, 105) = CONVERT(varchar(10), @data, 105) /*This still needs to be fixed*/
AND k.IdCantiere = @IdCantiere
AND k.IdUtenteInserimento = @IdUtente;
You could try to do this with the other 2 UPDATEs in your script.
For more help, read this: How to Post Performance Problems - SQLServerCentral
June 27, 2018 at 1:57 pm
I'll go ahead and reformat them all:UPDATE KCRM
SET KCRM.IdRapportoMobile = @IdRapporto
FROM KilometriCantiereRapportoMobile AS KCRM
WHERE KCRM.IdKilometri IN (
SELECT K.IdKilometri
FROM Kilometri AS K
INNER JOIN KilometriCantiereRapportoMobile AS KCRM2
ON K.IdKilometri = KCRM2.IdKilometri
WHERE KCRM2.IdRapportoMobile IS NULL
AND CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), K.[Data], 105)
AND K.IdCantiere = @IdCantiere
AND K.IdUtenteInserimento = @IdUtente
);
/*Articoli */
UPDATE ACRM
SET ACRM.IdRapportoMobile = @IdRapporto
FROM ArticoloCantiereRapportoMobile AS ACRM
WHERE ACRM.IdArticoloCantiere IN (
SELECT AC.IdArticoloCantiere
FROM ArticoloCantiere AS AC
INNER JOIN ArticoloCantiereRapportoMobile AS ACRM2
ON AC.IdArticoloCantiere = ACRM2.IdArticoloCantiere
WHERE ACRM2.IdRapportoMobile IS NULL
AND CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), AC.[Data], 105)
AND AC.IdCantiere = @IdCantiere
AND AC.IdUtente = @IdUtente
);
/* risorse */
UPDATE RRM
SET RRM.IdRapportoMobile = @IdRapporto
FROM RisorsaRapportoMobile AS RRM
WHERE RRM.IdRisorseUmane IN (
SELECT RU.IdRisorseUmane
FROM RisorsaRapportoMobile AS RRM2
INNER JOIN RisorseUmane AS RU
ON RU.IdRisorseUmane = RRM2.IdRisorseUmane
WHERE RRM2.IdRapportoMobile IS NULL
AND CONVERT(varchar(10), @data, 105) = CONVERT(varchar(10), RU.[Data], 105)
AND RU.IdCantiere = @IdCantiere
AND RU.IdUtenteInserimento = @IdUtente
);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 27, 2018 at 2:03 pm
sgmunson - Wednesday, June 27, 2018 1:57 PMI'll go ahead and reformat them all:
And suddenly, it becomes human-readable!
But it looks like we're all trying to no avail ... the OP has gone dormant.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 27, 2018 at 2:13 pm
Phil Parkin - Wednesday, June 27, 2018 2:03 PMsgmunson - Wednesday, June 27, 2018 1:57 PMI'll go ahead and reformat them all:And suddenly, it becomes human-readable!
But it looks like we're all trying to no avail ... the OP has gone dormant.
But it's fun to see when the OP has tried: https://www.codeproject.com/Questions/1250029/Queries-with-multiple-updates-very-slow
June 27, 2018 at 2:30 pm
Luis Cazares - Wednesday, June 27, 2018 2:13 PMPhil Parkin - Wednesday, June 27, 2018 2:03 PMsgmunson - Wednesday, June 27, 2018 1:57 PMI'll go ahead and reformat them all:And suddenly, it becomes human-readable!
But it looks like we're all trying to no avail ... the OP has gone dormant.But it's fun to see when the OP has tried: https://www.codeproject.com/Questions/1250029/Queries-with-multiple-updates-very-slow
Hah! I had some success with 'go home for lunch, come back and try again' earlier today.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply