SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  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,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Exists other way to make the query more speedy?
    i doubt it very much
    r937.com | rudy.ca | 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
    4,807
    Mentioned
    141 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?
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  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
  •