SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    [SQL Server] Optimize speedy query

    Hi all, I need optimize this query.
    Exists other way to make the query more speedy?
    The fields [DATE-V] -clustered-, [TZZ], [COD1], [COD2], [COD3] and [COD4] are indexes not clustered.

    Thanks in advance.
    Code:
    Affected rows: 0
    Time: 2.015ms
    
    Affected rows: 0
    
    SELECT
    	COALESCE ([MAT], 'Tot') AS [MAT],
    	[NUMBER-R]
    FROM
    	(
    		SELECT
    			LEFT (TZZ, 2) AS MAT,
    			SUM (
    				CASE
    				WHEN (
    					[COD1] IN (
    						'A01',
    						'A02',
    						'A06',
    						'A07',
    						'A08',
    						'A09',
    						'A10',
    						'A12',
    						'A13',
    						'A15',
    						'A16',
    						'A18',
    						'A20',
    						'A21',
    						'A22',
    						'A23',
    						'A24',
    						'A26',
    						'A28',
    						'A29',
    						'A30',
    						'A36',
    						'A37',
    						'A38',
    						'A42',
    						'A43',
    						'A44',
    						'A45',
    						'A47'
    					)
    					OR [COD2] IN (
    						'A01',
    						'A02',
    						'A06',
    						'A07',
    						'A08',
    						'A09',
    						'A10',
    						'A12',
    						'A13',
    						'A15',
    						'A16',
    						'A18',
    						'A20',
    						'A21',
    						'A22',
    						'A23',
    						'A24',
    						'A26',
    						'A28',
    						'A29',
    						'A30',
    						'A36',
    						'A37',
    						'A38',
    						'A42',
    						'A43',
    						'A44',
    						'A45',
    						'A47'
    					)
    					OR [COD3] IN (
    						'A01',
    						'A02',
    						'A06',
    						'A07',
    						'A08',
    						'A09',
    						'A10',
    						'A12',
    						'A13',
    						'A15',
    						'A16',
    						'A18',
    						'A20',
    						'A21',
    						'A22',
    						'A23',
    						'A24',
    						'A26',
    						'A28',
    						'A29',
    						'A30',
    						'A36',
    						'A37',
    						'A38',
    						'A42',
    						'A43',
    						'A44',
    						'A45',
    						'A47'
    					)
    					OR [COD4] IN (
    						'A01',
    						'A02',
    						'A06',
    						'A07',
    						'A08',
    						'A09',
    						'A10',
    						'A12',
    						'A13',
    						'A15',
    						'A16',
    						'A18',
    						'A20',
    						'A21',
    						'A22',
    						'A23',
    						'A24',
    						'A26',
    						'A28',
    						'A29',
    						'A30',
    						'A36',
    						'A37',
    						'A38',
    						'A42',
    						'A43',
    						'A44',
    						'A45',
    						'A47'
    					)
    				)
    				AND [DATE-V] IS NOT NULL THEN
    					1
    				ELSE
    					0
    				END
    			) AS [NUMBER-R]
    		FROM
    			dbo_40
    		WHERE
    			1 = 1
    		AND [TZZ] NOT LIKE 'LG%'
    		GROUP BY
    			LEFT (TZZ, 2) WITH ROLLUP
    	) AS SubQs;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Exists other way to make the query more speedy?
    i doubt it very much
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,235
    Mentioned
    154 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Exists other way to make the query more speedy?
    I agree with @r937 ;, I doubt you can drop this any further. You are at 2.015ms, dropping to 1ms won't be noticeable.

    Why do you think you need to improve this query?

  4. #4
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok,

    I'm interested in expert opinion... thanks a lot!
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

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