Results 1 to 13 of 13

Thread: [RESOLVED] Optimize very slow UPDATE

  1. #1

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Resolved [RESOLVED] Optimize very slow UPDATE

    I have this Update at work and I need to optimize it.

    It's terribly slow because it's done in a subquery at field level with complicated conditions.
    Does anybody know any way to rewrite this to make run faster?
    Code:
    UPDATE #POS SET SaldoDespuesEvento = (
    		  SELECT  SUM(Importe) 
    			FROM  #POS CTACTE2
    		   WHERE  CTACTE2.CodComitente = #POS.CodComitente  
    		     AND  CTACTE2.CodMoneda = #POS.CodMoneda
    		     AND  CTACTE2.EstaAnulado = 0 
    		     AND  (  
    					DATEDIFF(day, CTACTE2.FechaLiquidacion, #POS.FechaLiquidacion) > 0 
    					OR   (
    							DATEDIFF(day, CTACTE2.FechaLiquidacion, #POS.FechaLiquidacion) = 0 
    				 			AND  (	
    				 					#POS.CodCtaCorrienteMon >= CTACTE2.CodCtaCorrienteMon
    				 				  )
    				 		 )
    				   )
    		  ) 
    WHERE #POS.EstaAnulado = 0
      AND #POS.EsSaldoAnterior = 0
    Last edited by jcis; Nov 7th, 2013 at 01:05 PM.

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Optimize very slow UPDATE

    Take your SELECT and put it into another TEMP TABLE

    Code:
    SELECT  CTACTE2.CodComitente ,CTACTE2.CodComitente ,SUM(Importe) "SumImporte" Into #NewTempTable
    			FROM  #POS CTACTE2
    		   WHERE  CTACTE2.EstaAnulado = 0 
    		     AND  (  
    					DATEDIFF(day, CTACTE2.FechaLiquidacion, #POS.FechaLiquidacion) > 0 
    					OR   (
    							DATEDIFF(day, CTACTE2.FechaLiquidacion, #POS.FechaLiquidacion) = 0 
    				 			AND  (	
    				 					#POS.CodCtaCorrienteMon >= CTACTE2.CodCtaCorrienteMon
    				 				  )
    				 		 )
    				   )
    		  )
    Now JOIN to this in the UPDATE instead - does that work better?

    If not then ALTER the TEMP TABLES to put on INDEXES...

    How many rows are in the #POS table?

    How many rows are in the #NewTempTable I had you create??

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Optimize very slow UPDATE

    I'm a little unclear on your FROM statement - is that two tables?

    Is this MS SQL Server?

    Can you please use proper JOINS?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: Optimize very slow UPDATE

    Hi SZ, yes I tried adding indexes, they've been a salvation in other cases but not this one, also changed to joins with no better results, we could even simplify all this to this query:
    Code:
    	SELECT A.CodComitente, SUM(A.Importe) AS ImporteTotal
    	  FROM #POS A 
    INNER JOIN #POS B
    		ON A.CodComitente = B.CodComitente
    	   AND A.CodMoneda = B.CodMoneda
    	 WHERE A.EstaAnulado = 0 AND B.EstaAnulado = 0  AND
    	DATEDIFF(day, B.FechaLiquidacion, A.FechaLiquidacion) > 0 
    	OR   (
    		DATEDIFF(day, B.FechaLiquidacion, A.FechaLiquidacion) = 0 
    		AND  (	
    			A.CodCtaCorrienteMon >= B.CodCtaCorrienteMon  )
    		 )
    	   )						 
    	 GROUP BY A.CodComitente
    And yes it's Sql Server 2005, i need anyway to keep compatibility with Sql Server 2000 version

    EDIT: By mistake removed this part when editing:

    If I remove the date part in the where condition this runs instantly, so the problem is here, using this it takes 3 minutes to run:
    Code:
    	OR   (
    		DATEDIFF(day, B.FechaLiquidacion, A.FechaLiquidacion) = 0 
    		AND  (	
    			A.CodCtaCorrienteMon >= B.CodCtaCorrienteMon  )
    		 )
    	   )
    Last edited by jcis; Nov 7th, 2013 at 01:55 PM.

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Optimize very slow UPDATE

    So how long does that QUERY take to run and how many rows does it return?

    My idea was to put that into a TEMP table - did you try it with Into #NewTempTable and then an UPDATE/FROM statement with this JOIN'd?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: Optimize very slow UPDATE

    Just edited my last post again because last time I edited it I removed some things by mistake, as i wrote there we can simplify all this to that select, the problem is those dates in the where clause, if i remove that the query runs instantly, if not, it takes 3 minutes and i'm just using some sample data, in production that's taking 4 hours to run.

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Optimize very slow UPDATE

    Put all the fields needed to do the DATEDIFF's and such into the NEW temp table - does that run instantly?

    Once in the new temp table you should be able to WHERE clause with the DATEDIFF's from the TEMP TABLE.

    Again - how many rows?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: Optimize very slow UPDATE

    In a test case, the temp table has for example 7500 rows, but: Now i see that there is no 1 to 1 matching, not in the original update and not in the inner join in my example because the join (either explicit or implicit) hasn't all the necessary fields to create a 1 to 1 match between tables, result: 55 million records are checked againt these WHERE conditions, it's like when joinning tables the wrong way you usually get all the rows and: 7500 * 7500 = 56.25 million, but it is ok in this case, the programmer that did this in the past wanted to do it like this and the thing is working fine because that UPDATE in my first post is working like if it were 2 nested Cursors (or 1 + more code inside) making conditional accumulative operations inside, now the question is how to make this faster? Or even better what could be happening in that client that's making this process take 4 or 5 hours (I tested this with many records and it never took more than 3 minutes). These are temp tables so it cannot be a dropped index o something like that.

    Now i think if actually using real cursors won't be faster than this, i don't think it will but i think i'll test anyway.

  9. #9

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: Optimize very slow UPDATE

    If I remove the line in red it runs much faster (it just takes 3 seconds to run):
    Code:
    UPDATE #POS SET SaldoDespuesEvento = (
    		  SELECT  SUM(Importe) 
    			FROM  #POS CTACTE2
    		   WHERE  CTACTE2.CodComitente = #POS.CodComitente  
    		     AND  CTACTE2.CodMoneda = #POS.CodMoneda
    		     AND  CTACTE2.EstaAnulado = 0 
    		     AND  (  
    					DATEDIFF(day, CTACTE2.FechaLiquidacion, #POS.FechaLiquidacion) > 0 
    					OR   (
    							DATEDIFF(day, CTACTE2.FechaLiquidacion, #POS.FechaLiquidacion) = 0 
    				 			AND  (	
    				 					#POS.CodCtaCorrienteMon >= CTACTE2.CodCtaCorrienteMon				 				  )
    				 		 )
    				   )
    		  ) 
    WHERE #POS.EstaAnulado = 0
      AND #POS.EsSaldoAnterior = 0
    But as a client said the whole process (this is the slower part) was taking almost 5 hours i'm starting to think something else is happening in that particular client, like a problem with the server or something like that, i mean i tested with one of the biggest dbs using a time period of 3 years, making it process 50+ million records and the whole thing has taken 3 minutes, sounds almost impossible for this to get to 5 hours so i'll see if I can get more info about this particular case, Thanks SZ

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Optimize very slow UPDATE

    Are they having problems with the TEMPDB - maybe the disk it's on - running out of room?

    Look for error in the SQL logs just to make sure...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Re: Optimize very slow UPDATE

    Run your SELECT separately and take a look at the execution plan. The execution plan will give you a hint where your bottleneck is. Once you find your bottleneck, you will most likely add another index(es) to the table and this should fix your problem.

    UPDATE: I just noticed you are using TEMP table. If your temp table contains thousands or million of records it will be slow. How do you get your data into your temp table? Can you show us your query?

  12. #12

    Thread Starter
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: Optimize very slow UPDATE

    I Found the solution, this is a common problem: Running Totals.

    This is one of the few cases CURSORS perform faster than UPDATES, in my case for example, in the last test i did UPDATE (with subquery) took 5 mins, the CURSOR took 1 second.

    See the Cursor solution and more available solutions here: http://weblogs.sqlteam.com/mladenp/a...ng-Totals.aspx
    or here: http://stackoverflow.com/questions/8...l-in-sqlserver

    Thanks all

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] Optimize very slow UPDATE

    Cursors have a place - I've used them myself. Rarely - but I have used them. Usually due to speed issues...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width