Click here to Skip to main content
15,567,415 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi 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? 

SQL
UPDATE KilometriCantiereRapportoMobile
    SET IdRapportoMobile = @IdRapporto
    WHERE IdKilometri IN (SELECT
      Kilometri.IdKilometri
    FROM Kilometri
    INNER JOIN KilometriCantiereRapportoMobile
      ON Kilometri.IdKilometri = KilometriCantiereRapportoMobile.IdKilometri
    WHERE 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 ArticoloCantiereRapportoMobile
    SET IdRapportoMobile = @IdRapporto
    WHERE IdArticoloCantiere IN (SELECT
      ArticoloCantiere.IdArticoloCantiere
    FROM ArticoloCantiere
    INNER JOIN ArticoloCantiereRapportoMobile
      ON ArticoloCantiere.IdArticoloCantiere = ArticoloCantiereRapportoMobile.IdArticoloCantiere
    WHERE ArticoloCantiereRapportoMobile.IdRapportoMobile IS NULL
    AND CONVERT(varchar(10), @Data, 105) = CONVERT(varchar(10), ArticoloCantiere.Data, 105)
    AND ArticoloCantiere.IdCantiere = @IdCantiere
    AND ArticoloCantiere.IdUtente = @IdUtente);  /* risorse */
    UPDATE RisorsaRapportoMobile
    SET IdRapportoMobile = @IdRapporto
    WHERE IdRisorseUmane IN (SELECT
      RisorseUmane.IdRisorseUmane
    FROM RisorsaRapportoMobile
    INNER JOIN RisorseUmane
      ON RisorseUmane.IdRisorseUmane = RisorsaRapportoMobile.IdRisorseUmane
    WHERE RisorsaRapportoMobile.IdRapportoMobile IS NULL
    AND CONVERT(varchar(10), @Data, 105) = CONVERT(varchar(10), RisorseUmane.Data, 105)
    AND RisorseUmane.IdCantiere = @IdCantiere
    AND RisorseUmane.IdUtenteInserimento = @IdUtente)


What I have tried:

restart sql server
Posted
Updated 4-Jul-18 3:28am
v2
Comments
Clifford Nelson 26-Jun-18 11:21am    
Might be a good idea to look at your keys and make sure those keys needed for the query have indexes. Also might figure out how to avoid all those converts, they cannot be helping
#realJSOP 26-Jun-18 11:37am    
The joins are also killing your performance.
rikidev 26-Jun-18 11:38am    
how can i do within join?
MadMyche 26-Jun-18 11:48am    
Adding onto Clifford's comment, I would chime in to use the "Execution Plan" tool in Sql Server to pinpoint what is slowing you down. The CONVERT functions in there look to be a simple date; I would make sure the input is in that format to begin with and skip it, or at least have a varchar(10) variable assigned to it and skip half the conversions on every single line compared
Gerry Schmitz 26-Jun-18 12:34pm    
Ever heard of "temporary tables"? "Partitioning" the problem?

1 solution

Adding to the comments above, there is no reason to have those sub-queries. You can do something like the following (although it is only marginally better)
SQL
UPDATE RRM SET IdRapportoMobile = @IdRapporto
FROM RisorsaRapportoMobile RRM
INNER JOIN RisorseUmane RU ON RU.IdRisorseUmane = RRM.IdRisorseUmane
WHERE RRM.IdRapportoMobile IS NULL
    AND CONVERT(varchar(10), @Data, 105) = CONVERT(varchar(10), RU.Data, 105)
    AND RU.IdCantiere = @IdCantiere
    AND RU.IdUtenteInserimento = @IdUtente
See the comments about the use of CONVERT - get rid of those. Ensure @Data is a date (not a datetime. Date has been around since SQL2008 so it will work with 2014). If RisorseUmane.Data is a datetime on the database then try using
SQL
AND @Data = CAST(RU.Data AS date)
It should be faster than converting to strings.

You're joining tables on strings which isn't always the best - if you have scope to change that then it might help a little.

Temp tables will almost certainly help, as you will be limiting the data examined both in depth and width and you can also add indexes to the temp tables. Don't be tempted to use CTEs however.

There are some CodeProject articles that might also help you when it comes to tuning performance:
SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[^]
SQL Server Profiler Step by Step[^]
How to Analyze SQL Server Performance[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900